Looking for the best way to track employee time? If you have a small team, using an Excel spreadsheet can be an option for you. In this step-by-step guide, you’ll learn how to make a timesheet in Excel that includes formulas and space for vacation, sick days, and more.
How to make a timesheet in Excel
Step 1: Format your spreadsheet
The first step for how to make a timesheet in Excel is to format it to create a spreadsheet that’s easy to read. Start by making all your cells wider:
- Widen cell A (we set ours to 14.83)
- Right-click on column A, and select Copy
- Highlight columns B through L
- Right-click again, and select Paste Special, then Formatting
This will make all columns wider and the same size to keep formatting consistent.
Step 2: Title your timesheet
Now, type the name of your business—or whatever you want to name your timesheet—into cell A1. Then, center the text by width and height, format the text by font, size (we used size 38), and color, then bold it.
After that, merge the cells to create your title by highlighting cells A1 through L1, then select Merge & Center on the Home toolbar.
Step 3: Add labels to your timesheet
Next, we’ll add labels at the top of the document so employees can fill out the information, such as name, date, and pay rate.
Here are the labels you should add with the corresponding cells we used:
- Employee (A2)
- Manager (A4)
- Employee’s signature (F2)
- Manager’s signature (F4)
- Date (K2 and K4)
- This week’s start date (E6)
- Standard pay rate (H6)
- Overtime pay rate (K6)
If any of the text takes up more than one cell, select Wrap Text so the words fit all in one cell. You can do that for “This week’s start date,” “Standard pay rate,” and “Overtime pay rate,” for example.
Then, in the cells following each label, make a line for employees and managers to write or type over by merging the cells and then applying a Bottom Border (from the borders tool, located on the Home toolbar). After that, merge cells A7 through L7 to create a small barrier between the set of labels in our next step.
Step 4: Add the timesheet
It’s time to create the timesheet table. In row 8, add these labels for each column: Day, Date, Job/Shift, Time in, Time out, Time in, Time out, Total (Hours), Overtime (Hours), Sick (Hours), Holiday (Hours), and Vacation (Hours). You’ll list “Time in” and “Time out” twice to account for lunch breaks.
Then, fill the row with any color of your choice, and use the borders tool to put boxes around the column labels. After that, center and bold the text.
For this weekly timesheet, we’ve labeled the cells below “Day” with the days of the week, starting with Monday in cell A9 and ending with Sunday in cell A15. Center the labels, then widen rows 8 through 19 for better readability (our rows are all about 46.00 high). Apply borders to all cells beneath the row of labels. Then add Thick Box Borders to better divide the table.
After this, set up labels for totals:
- Hours this week (G16)
- Rate (G17)
- Subtotal (G18)
- Total (K19)
Make these cells whatever color you like, then center and bold the text. Use the borders tool to make these sections stand out.
Step 5: Add formulas for automatic calculations
This is where we start making spreadsheet magic—adding the formulas to total the hours and pay. For the following formulas, consider that “Hours this week” is on cell G16 and “Total” is on cell K19. If your cells aren’t the same, you can still use the formulas but adjust to the corrected cells.
To make your calculations stand out from the rest of the employee’s timesheet entries, you can fill the cells in the “Hours this week,” “Rate,” “Subtotal,” and “Total” rows with gray.
Here’s what are the formulas you’ll use:
For these formulas to work, you’ll have to input the hourly rates, such as the standard hourly rate and overtime rate, for the employee in each of the cells in the “Rate” row. If your business provides paid time off (PTO) for sick time, vacations, and holidays, fill the cells with the employee’s standard hourly rate.
As an example, if your employee makes $10 per hour, the bottom of your spreadsheet will look like this:
Remember, these are basic calculations and do not include things like taxes, workman’s comp, or other paycheck deductions.
Step 6: Print or keep it online
If you plan to print this template and have employees turn in handwritten timesheet data, remove any unnecessary gridlines. Select View and uncheck the box that says Gridlines.
Now you can print copies of this Excel timesheet for employee use.
- Highlight everything you want printed (A1 across to L1 and down to row 19).
- Click on File > Print > Show Details
- Select landscape orientation
- Under Print, choose Selection
- Under Margins, choose Wide Margins
- Check the box that says Scale to fit and click Print
Otherwise, you can share this timesheet with your employees so they can fill out, sign, and submit them at the end of each week.
Excel timesheet challenges
Once you get the hang of it, creating Excel timesheets will be as easy as clocking in. However, time tracking in Excel does come with some challenges.
Here are some issues you may face:
- Manual inputs: For some areas of your timesheet, you’ll have to manually add the hours and rates and calculate the hours worked daily.
- Can lead to errors: Timesheets in Excel can also lead to a few human errors, such as adding the incorrect rate, hours, or calculation.
- More difficult to keep track: You’ll have to keep track of your employees’ timesheets and ensure everything is correct before signing them.
- Different rate types: It might be difficult to track employees’ pay rates if they differ.
- Can’t automate it: It will be difficult to cover a longer period and integrate it with other tools.
However, if you have a small team and don’t mind doing the manual work, using Excel timesheets might still work for you.
Skip the manual work with time tracking software
Now that you learned how to make a timesheet in Excel, you might wonder if there are ways to automate it—after all, it’s still manual work.
An automated time tracking tool like QuickBooks Time could save your business time and money every time you run payroll—and grant you more peace of mind. Check out our digital timesheet solution with easy integrations to QuickBooks and other payroll providers.
Excel timesheets FAQ
Still have questions about Excel timesheets? We’ve got the answers.