Key Takeaways: : How to Make an Excel Time Sheet

• ✔ Create a weekly Excel time sheet by setting up a table with merged headers, dates, and time columns for clock-ins, breaks, and pay calculations.
• ✔ Use formulas to automate calculations, including regular hours, overtime, and gross pay, based on standard U.S. overtime rules.
• ✔ Include identifying info fields, like employee name, ID, department, and manager, to make the time sheet complete and HR-friendly.
• ✔ Apply formatting for clarity, such as bold headers, column sizing, borders, and color fills to improve readability and professional appearance.
• ✔ Consider Excel's limitations, such as a lack of automation and security, and evaluate software alternatives, like OnTheClock, for better scalability.

If you're looking for a reliable way to track hours worked, this guide will show you how to make a time sheet in Excel, step by step. Even if you’re not confident with spreadsheets, Excel makes it possible to build a simple, effective time sheet without special software or coding.

We’ll walk you through each part of the process, from setting up your table and adding formulas to formatting, printing, and calculating pay. You’ll also get free downloadable templates, quick answers to common questions, and guidance on when it might be time to upgrade to a more automated system.

But first, let’s cover the basics so you know exactly what a timesheet is and why it matters.

What’s a Time Sheet?

A time sheet is a simple tool that helps track how much time someone spends working. Whether you're managing a team or running your own business, a time sheet provides a clear record of hours worked on specific tasks, projects, or shifts.

What Is a Time Sheet Used For?

Time sheets are used for more than just tracking hours; they help businesses stay organized, accurate, and compliant. Here are the most common ways to use them:

• Payroll: Calculate pay based on actual hours worked, including overtime and breaks.
• Client billing: Track billable hours for service-based work, like consulting, design, or freelance projects.
• Project tracking: Monitor how much time is spent on specific jobs or tasks to stay on budget.
• Compliance: Keep a record of employee hours to meet labor laws and audit requirements.
• Performance insights: Review time data to identify bottlenecks, improve efficiency, or plan schedules better.

If you’re using Excel, it’s easy to build a time sheet that serves all of these purposes without the need for expensive software.

Can You Use Excel as a Time Clock?

Yes, you can use Excel as a basic time clock, but it has limits. Employees can enter their clock-in and -out times manually, and, with the right formulas, Excel can calculate total hours worked. This setup might work for very small teams or solo users.

However, for most businesses, Excel isn’t the best long-term solution. It doesn’t have built-in automation, reminders, or security features. Time entries can be accidentally changed or, worse, intentionally edited, leading to inaccurate records and possible time theft.

How to Create a Timesheet in Excel

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 two start and end times, allowing you to 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 out 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; and
• 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 bespoken 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.

OnTheClock: A Better Alternative to Excel Time Sheets

Excel is a great place to start, but it’s not built for long-term time tracking. If you're tired of manual entry, chasing time sheets, or fixing formula errors, switching to an automated solution can save you hours every week.

OnTheClock offers a simpler, more secure way to track time:

• Automatic clock-ins and outs with GPS or device tracking;
• Real-time reports on hours, breaks, and overtime;
• Easy payroll exports to providers like QuickBooks and ADP;
• User-friendly mobile app for teams on the go; and
• PTO tracking and approval workflows built in.

You get accurate data without the daily spreadsheet hassle. And with OnTheClock, employees can’t change their own times, so you stay compliant and in control.

See how OnTheClock works in action:

If Excel is starting to slow you down, it might be time to try something built for the job.

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.