If you need a way to track hours worked, we’re here to help. Even if you’re not naturally talented at spreadsheets, time tracking with Excel isn’t too complicated. However, it does take some time to set up, as well as to log hours each week.

Here’s how to make a time spreadsheet in Excel, step by step.

Step 1: Create your table.

First, the basics. Take note of the “Merge and Center” function, which we’ll use often throughout this tutorial.

1. Launch Microsoft Excel and create a blank workbook
2. Select columns A1-K1. Navigate to “Home,” then select “Merge and Center.”
3. Select columns A2-K2, then “Merge and Center.”
4. Type a time Sheet title into the merged cells. (Your company name works.)

If you’re creating an Excel time sheet for your staff, your title may be your company name. If you’re invoicing for hours worked, your time sheet title may include the title of your LLC.

Step 2: Add dates and times.

Every time sheet needs time data. Let’s add ours. For this tutorial, we’ll make a weekly time sheet.

1. Highlight cells A3-B3, then Merge & Center. Type “Dates” into this field
2. In column A4, type the start day of your workweek (e.g., Monday).
3. Hover your cursor over the lower right corner of cell A4. It should transform into a plus sign.
4. Click and drag to highlight cells A4-A10, then release. This will auto-fill the rest of the days of your workweek.
5. Highlight cells C4-F4.
6. Right-click on the highlighted area, and choose “Format Cells.”
7. Select “Time,” then choose your preferred time format.

Step 3: Add hour types.

Next, you’ll want to add days and times to your Excel sheet. Each date will have 2 start and end times. That way, you can account for a break.

1. Type “Start Time” into cells C3 and E3.
2. Type “End Time” into cells D3 and F3.
3. Type “Regular Hours” into cell G3.
4. Type “Vacation” into cell H3.
5. Type “Sick” into cell I2.
6. Type “Holiday” into cell J2.
7. Type “Regular Hours” into cell F11.
8. Type “Overtime” into cell F12.
9. Type “Total Hours” into cell F13.
10. Type “Hourly Rate” into cell F14.
11. Type “Overtime Rate” into cell F15.
12. Type “Gross Pay” into cell F16.
13. Select cells G14-16.
14. Right-click and select “Format Cells.”
15. Under “Number,” select “Currency.”
16. Enter the hourly pay rate into cell G14.
17. Enter the overtime rate into cell G15.

After you finish these formatting steps, you need to enter pay rates.

Step 4: Add identifying information.

To make your time sheet a functional record, you need to create a section for identifying information.

1. Type “Employee” into cell H11. Select cells I11-J11, then “Merge & Center.”
2. Type “Employee ID” into cell H12. Select cells I12-J12, then “Merge & Center.”
3. Type “Manager” into cell H13. Select cells I13-J13, then “Merge & Center.”
4. Type “Department” into cell H14. Select cells I14-J14, then “Merge & Center.”
5. Type “Pay Period” into cell H15. Select cells I15-J15, then “Merge & Center.”
6. Type “Signature” into cell H16. Select cells I16-J16, then “Merge & Center.”

If you plan on filling your time sheet by hand, you can skip to step 6 to finish formatting. Alternatively, you can make a fully functional digital time sheet with Excel formulas.

Step 5: Adding formulas.

Now onto the fun part: Excel formulas. While adding sums in Excel is relatively easy, it gets complicated when weekly overtime comes into play. We’ve provided a spreadsheet formula that automatically calculates overtime after 40 hours per week.

In addition to calculating weekly overtime hours, we’ll need to factor vacation, sick, and holiday hours into our total hours. This will allow gross pay to be accurately calculated.

Note: This Excel spreadsheet formula calculates overtime after 40 hours are worked within a week. This is the most common overtime law in the United States. If you calculate overtime at a daily rate, you can use the sum function instead.

Calculating Daily Total Hours

In cell G4, copy and paste the following formula:

Then, hover your cursor in the bottom right corner of G4. It should transform into a plus sign. Click and drag to highlight cells G4-G10, then release. This will autofill the formula to the remaining cells for the workweek.

Calculating Weekly Total Hours

To calculate regular hours, copy and paste the following formula into cell G11:

To calculate weekly overtime, copy and paste the following formula into cell G12:

To calculate total weekly hours, copy and paste the following formula into cell G13:

Finally, to calculate weekly gross pay, enter the following formula into cell G16:

Now, you’ve got a functional weekly time sheet in Excel. Congratulations!

Step 6: Make it pretty.

While our Excel time sheet is fully functional, it could look nicer. There are many fun ways to spruce up your spreadsheets and add a personal touch. We’ll cover some formatting basics:

• Formatting Fonts to help you choose an easy-to-read font.
• Sizing Columns to give a uniform appearance.
• Adding Borders to organize data into sections.
• Color Fills to make it easy to visually understand data.

For now, let’s do some basic formatting to make your time sheet look professional and easy to read.

Formatting Fonts

1. Select all. You can do this with a keyboard shortcut: Ctrl + A on Windows or Cmnd + A on Mac.
2. Right-click to bring up font format options.
3. Click on the font name and select one that’s easy to read. (We chose Arial.)
4. Click on the number next to the font name to change the font size. (We chose 12.)
5. To make it easier to read, bold your column and row headers (found in row 3, column F, and column H).
6. Finally, right-click on your spreadsheet title (i.e., your company name).
7. Format your title to stand out. (We changed the font to Arial Black, size 22.)

Sizing Columns

1. Select all.
2. Right-click on “A” at the top of the screen.”
3. From the menu, select “Column Width.”
4. Enter a value to make columns equal width; we choose “16” based on our font size.

Adding Borders

1. Select A3-J10. Right-click to bring up the format menu.
2. Click on the arrow next to the Border icon.
3. Choose “Thick Outside Border.”
4. Repeat on cells F11-G17 and H11-J17.
5. Now, we’ll add a vertical line. Select G3-G17.
6. Right-click, then click on the arrow next to the Border icon.
7. Select “Right Border.”

Adding Color Fills

1. Select headers (A3-J3, F11-F17, and H11-16).
2. To select multiple headers, hold down the Ctrl (Windows) or Cmnd (Mac) keys while dragging your cursor.
3. Right-click, then click on the arrow next to the fill button (which looks like a paint bucket).
4. Select your preferred color to fill the headers. (We chose light orange).
5. Select A5-J5, A7-J7, A9-J9, then navigate back to fill colors.
6. Fill these areas in with light gray.
7. Select G11, G13, and G15, then fill with light gray.
8. Select J11, J13, and J15, then fill with light gray.

Step 7: Adding hours worked.

We’re finally ready to fill out the time sheet. If you prefer to fill your time sheet by hand, skip to the next step. Otherwise, here’s how to add data electronically.

1. Type in applicable identifying data for rows I11-I15.
2. In cell B4, enter the start date for the pay period as MM/DD/YYYY (e.g., Jul 3, 2023, would be 07/03/2023).
3. Hover your cursor over the lower left corner of cell B4. It should transform into a plus sign.
4. Click and drag to highlight cells B4-B10, then release. This will autofill the dates for the rest of the week.
5. Enter the start and end times for each date.
6. Enter any applicable vacation, sick, or holiday hours for each date.

You now have a digital file ready for payroll processing. If you’re using payroll provider software, you can export your file as a CSV for easy importing.

Step 8: Printing.

Printing a hard copy of your Excel time sheet requires a little setup.

1. Navigate to “File,” then “Print.”
2. Set the page orientation to “Landscape.”
3. Set the scaling to “Fit Sheet on Page.”
4. Navigate to “Page Setup,” then “Margins.”
5. Check the boxes to center horizontally and vertically on the page.

Now, you can print a clean copy of your spreadsheet to be signed and stored as a hard record.

Excel Time Sheet Templates

Looking for a time sheet template? We’ve got you covered.

• Daily Time Sheet Template
• Weekly Time Sheet Template
• Biweekly Time Sheet Template
• Monthly Time Sheet Template

Go ahead and download — our templates are free!

Can you use Excel as a time clock?

Thanks to the “Now” function, setting up an Excel spreadsheet like a time clock is possible. Employees could simply type =NOW() and press enter, imitating a punch into a time clock. You can even add buttons to perform this function, so that workers can click to clock in or out.

But let’s not oversimplify. It is pretty time-consuming to configure this type of time clock. And once you get it working, keeping employee data stored and organized is another complicated issue to resolve. Finally, because Excel isn’t time clock software, using it in this way makes it prone to human error, and puts you at risk of time theft.

Creating an Excel time clock could be fun if you have a passion for spreadsheets, coding, or design. However, we don’t recommend it for the average user. If you’ve outgrown manual time tracking with spreadsheets, investing in a proper time clock system is worth it.

Time Tracking in Excel: Pros and Cons

Using time sheets in Excel is a workable solution for contractors, freelancers, small business owners, and anyone who needs to invoice clients on an hourly basis. That said? There are a few drawbacks to manually tracking time in this way.

Pros:

• Cost-effective. If you already have Excel, you don’t need to purchase additional software.
• Instant calculations. Excel can crunch numbers for you with formulas and functions.
• Customizable. Excel formulas and functions allow you to create bespoke time sheets.

Cons:

• Learning curve. If you’re new to Excel, it can be challenging to learn how to use it.
• Error-prone. Excel time sheets may be unreliable, as data can be easily manipulated or entered incorrectly.
• Inconvenient. Excel is powerful, but lacks automation, reporting, and alerts.

For many, manually tracking hours worked in a spreadsheet is a place to start. But it may be cumbersome if you’re managing payroll for a large staff, or a portfolio of billable clients. If that’s the case, automating time tracking might be a worthy investment.

Streamline Your Business Operations with OnTheClock

A good time tracking software can be configured to meet your unique business needs. Employees clock in and out with a single tap. When it’s time to process payroll, you can send timecard data to your provider in just a few clicks.

If you’re ready to evolve past manual time tracking, OnTheClock offers 30 days for $0 to new users. Explore our features, schedule a demo, or start your free trial now.