To get started, you’ll need to create the basic layout of the worksheet. You’re going to want to create a table, a grouping of rows and columns. The rows will represent employees – each employee will receive his or her own row. The columns will represent days of the week, with each column as a day. In any given cell, you’re entering how many hours a given employee worked on a given day.
To start with, create a table that covers one full week. It should be eight columns wide: The first column should have the names of all your employees, and columns two through eight should have the days of the week. If your business/company only works Monday through Friday, make only make six columns instead of eight. The top row should have the days of the week and each row below that should have an employee’s name in the first column.
Finally, put the date of the first day of the week (the day in column B) into cell A1. You have created a table to store all your employees’ hours for a week.
Building Out the Timesheet
Now that you’ve created a template for a week’s worth of employee data, you’re going to want to copy over some additional weeks. To accomplish this, copy the entire table. Move over one additional column (there should be one empty column between the start of the new table and the end of the old one) and paste the table.
You should now have two identical tables. There are two modifications you should make before copying more weeks.
In the first cell of the newly pasted table, replace the date with the following formula:
=a1 + 7
This will cause the new table to have a date of seven days later than the first table. In effect, each time you copy and paste a new table, you will be making a new week’s timesheet.
Summing Up Weekly Hours
In the empty column between the two tables, you are going to create a cell that sums the weekly hours for each employee. To do that, in the empty cell next to the first employee, enter the following formula:
This will sum up all hours entered for that employee for the week. Now, copy that cell, and paste down so that each employee row has a copy of that formula. Finally, copy the entire column with the formulas and paste it next to the second table.
Once you’ve done these two things, you can create as many tables as you want by copying and pasting the second table in sequence, side by side. Make sure not to copy the first table, as it won’t have the date formula.
Once you’ve created as many weeks as you need, you’re ready to start entering employee hours and tracking their weeks.
The employee timesheet you’ve created will get the job done, but there are some further things you can do to enhance it.
- Format the table so that it looks nicer. Some suggestions would be bolding the total rows, centering the text within all columns and using borders to create neat boxes.
- Create a yearly (or quarterly, or monthly) total hours for each employee. This would be done by summing the weekly totals for each table in a separate table for each employee.
- Use conditional formatting to alert yourself to situations where employees are getting too many or too few hours. You can set thresholds to light up if weekly totals go over or under a certain value.
Those extras are only the tip of the iceberg. You can do quite a bit to enhance the timesheet using built-in Excel functionality.
By creating an employee timesheet in Excel, you’ll be able to better organize and plan your employees’ hours and track them over time.