Tracking employee time in an Excel spreadsheet isn’t the easiest way to keep track of hours for payroll. For that, you might check out automated time tracking that integrates seamlessly with your payroll solution. But if you’re still shopping around and wondering if spreadsheets are right for you, download one of our free timesheet templates or learn how to make a timesheet in Excel. What follows is a step-by-step guide to creating a timesheet in Excel.
How to create an Excel timesheet
Every business has different needs. They depend on the type of work you do, how many people you employ, and about a hundred other variables. That’s why the first step to making a timesheet in Excel is to identify the categories you’ll need to create to run payroll—and your business—successfully. This timesheet includes space for employees to write down the following for each week.
The job or shift
The times they clock in (before and after lunch)
The times they clock out (before and after lunch)
Their total number of hours
They total number of overtime hours
Their total number of sick hours
Their total number of holiday hours
Their total number of vacation hours
Your timesheet might also include columns for answers to yes or no questions (e.g., Were you injured on the job? Is the project complete?) or space for client signatures.
Once you know which columns or labels you want in your timesheet, it’s time to create the working product. Here’s how to create a timesheet any employee can use.
Step 1: Format your spreadsheet
To create a spreadsheet that’s easy to read, first you need to format. Make all your cells wider, by first widening cell A (ours is set to 14.83). Then right-click on column A, select Copy, highlight columns B through L, right-click again, and select Paste Special, then Formatting.
Step 2: Title your timesheet
Type the name of your business into cell A1. Center the text by width and height, and format the text by font, size (we used size 38), and color, then bold it. Highlight cells A1 through L1. Select Merge & Center on the Home toolbar.
Step 3: Add labels to your timesheet
Add labels like EMPLOYEE and MANAGER (with signature and date lines for each), THIS WEEK’S START DATE, STANDARD PAY RATE, and OVERTIME PAY RATE. We used cells A2, A4, F2, F4, K2, K4, E6, H6, and K6.
For THIS WEEK’S START DATE, STANDARD PAY RATE, and OVERTIME PAY RATE, select Wrap Text, so the words don’t take up more than one cell.
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). Merge cells A7 through L7 to create a small barrier between the next set of labels.
Step 4: Add timesheet labels
In row 8, create 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). TIME IN and TIME OUT are listed twice to account for lunch breaks.
Fill the row with any color of your choice, and use the borders tool to put boxes around the column labels. Center and bold the text.
For this weekly timesheet, we’ve labelled 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 up and down and side to side. 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.
Step 5: Print the timesheet
If your plan is to print this template and have employees turn in handwritten timesheet data, you’re almost done!
The last thing you need to do is set up labels for totals. In cells G16, G17, and G18, write HOURS THIS WEEK, RATE, and SUB-TOTAL. In cell K19, write TOTAL. Make these cells whatever color you like, then center and bold the text. Use the borders tool to make these sections stand out.
Put some final spit and polish on your document by removing 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
That’s it—you’re done! Don’t want to print? Keep reading.
How to add formulas for automatic calculations
This is where we start making spreadsheet magic. Assuming you haven’t removed or added any days to your spreadsheet, HOURS THIS WEEK should be in cell G16, while TOTAL should be in cell K19.
Step 1: Prepare to add formulas for digital use
Follow instructions from step 5, then highlight cells H16 over L16 and down to row 18. Turn these and cell L19 gray. Now the boxes with your calculations will stand out from the rest of the employee’s timesheet entrees.
Step 2: Click on cell H16 or whatever cell is directly beside the box that says HOURS THIS WEEK. In the formula bar, type in =SUM(H9:H15) and press Enter. This box will now add any numbers entered in the column labeled TOTAL (HOURS).
Step 3: Click on cell I16. In the formula bar, type in =SUM(I9:I15) and press Enter. This box will now add any numbers entered in the column labeled OVERTIME (HOURS).
Step 4: Click on cell J16. In the formula bar, type in =SUM(J9:J15) and press Enter. This box will now add any numbers entered in the column labeled SICK (HOURS).
Step 5: Click on cell K16. In the formula bar, type in =SUM(K9:K15) and press Enter. This box will now add any numbers entered in the column labeled HOLIDAY (HOURS).
Step 6: Click on cell L16. In the formula bar, type in =SUM(L9:L15) and press Enter. This box will now add any numbers entered in the column labeled VACATION (HOURS).
Step 7: In cell H17 (or whatever cell is directly beside RATE), type in the employee’s hourly rate. In the cell beside that, type in the employee’s overtime rate.
If your business provides paid time off (PTO) for sick time, vacations, and holidays, fill J17, K17, and L17 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:Step 8: Now to calculate sub-totals. Click on cell H18 (or whatever cell is directly beside SUB-TOTAL). In the formula bar, type in =SUM(H16*H17) and press Enter. This box will now multiply the employees total hours by their standard pay rate to show how much money they’re owed.*
Step 9: Click on cell I18. In the formula bar, type in =SUM(I16*I17) and press Enter. This box will now multiply the employee’s overtime hours by their overtime pay rate to show how much they’re owed.*
Step 10: Click on cell J18. In the formula bar, type in =SUM(J16*J17) and press Enter. This box will now multiply the employee’s paid sick hours by their standard pay rate to show how much money they’re owed.*
Step 11: Click on cell K18. In the formula bar, type in =SUM(K16*K17) and press Enter. This box will now multiply the employee’s paid holiday hours by their standard pay rate to show how much money they’re owed.*
Step 12: Click on cell L18. In the formula bar, type in =SUM(L16*L17) and press Enter. This box will multiply the employee’s paid vacation hours by their standard pay rate to show how much money they’re owed.*
For example, say an employee worked 42 hours this week. Cell H16 should now say 40, while cell I16 should say 2. If your formulas are correct. H18 will say 400, while I18 will say 30.
*These are basic calculations and do not include things like taxes, workman’s comp, or other paycheck deductions.
Step 13: At last, we get to calculate total money owed. Select cell L19. In the formula bar, type in =SUM(H18:L18) and press Enter. This box will now add the five sub-totals to show how much money your employee has made total (before bonuses, taxes, or other deductions).
Think that’s simple? Check out our digital timesheet solution with easy integrations to QuickBooks, ADP, and other payroll providers. And keep in mind, paper timesheets are the leading cause of timesheet errors. 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.
FAQs about Excel timesheets
What does this Excel timesheet include?
This Excel timesheet has space for workers to record their daily clock in and clock out times. It also has a place for them to record hours for vacation, illness, or holidays. If your company does not provide paid time off, you’ll want employees to still write down when they’re out on vacation or sick, so your records are complete. In the row titled RATE, however, you’ll want to put 0 in cells J17, K17, and L17, so that the employee does not show earnings for those hours.
What doesn’t this Excel timesheet include?
This Excel timesheet template is a basic template for calculating an employee’s pay, prior to deductions like taxes, Social Security, or Medicare. It also doesn’t have space for any additional income, such as bonuses or commissions.
Additionally, if you bill hours to client projects, you’ll need more robust features than this timesheet offers that will allow you to track hours for specific projects.
Other time tracking solutions may offer more sophisticated features, including mobile apps, geofencing, scheduling, and more. Such features can help employers save time and money on payroll, cutting down common issues like time theft, time card errors, or compliance errors.
Should my salaried employees track time?
Yes, salaried employees should track time. This is more than an issue of good record-keeping. Employers should have two years’ worth of time cards for each employee. Should a dispute over compliance or pay arise, the company is protected from legal recourse. Additionally, salaried employees can use their time cards to their own benefit, be it for making a case for a raise or proving their own attendance.
Tracking time on the job holds everyone accountable so that employers and employees each give and take what is expected for all to benefit.
How do I add my employees to QuickBooks Time from Excel?
It’s easy to upload employee information into QuickBooks Time, should you decide to track time digitally. If your spreadsheet is a CSV file, follow these instructions to get started, then send your employees an invitation, straight to their mobile device, to start tracking time.