How to Make a Timesheet in Excel Step-by-Step (2024)

If you look into the market stats, you’d know having time-tracking software installed for your business will burn a hole in your pocket 💰

And you are certainly not advised to get one for your business if it’s yet only a startup. This is because you can get the same job done by creating a timesheet in Excel yourself.

Yes, you can create it yourself. And I will take you through the entire process of creating a timesheet in Excel.

So let’s jump right into the guide below. To tag along with the guide, do not forget to download our sample workbook here 📩

Why make a timesheet in Excel?

Almost every business (be it a startup or a well-developed one) would need a timesheet to track the work hours of their employees ⌛

However, the needs of each business will vary greatly. And although the market has an unending variety of ready-made Excel timesheet templates, hard to say you’d find the one that fits all your needs.

So, why don’t you set up a timesheet for your business yourself? Here are some other reasons why or why not creating a timesheet in Excel may be a good idea 🤔

Pros

Keep better track of your employees

Each business would have a different work cycle – with some departments clocking in extra hours. And some departments underdoing their normal working hours.

And that’s just fine. By designing timesheets yourself, you can create timesheet formats that fit the needs of your business. It also helps you to keep better track of the time spent by your employees at work ⏰

Save big on costs

Time-tracking software solutions can prove very damaging to your budget. These solutions often come with periodic renewal requirements which means you’re into an unending expense cycle 🚴‍♂️

There’s little point in spending away that much money when you can achieve the same goal with almost zero costs. You can create a timesheet in Excel for free.

Cons

Dedication of Resources

To create Excel timesheets, you’d have to spare away some time (probably a few hours) and maybe some of your human resources too.

Subject to Manipulation

Excel timesheets can be interactive. Or if you’re going with a paper-based system, they’ll be manually filled in by employees ✍

Both cases attract the possibility of human error or even, manipulation. Without efficient controls, employees may find it easy to punch in more hours than they’ve worked.

Now, while “Steve Jones” appears several times on the list, there’s only one “Steve Jones from the sales division”.

This is the kind of magic you can do with INDEX MATCH with multiple criteria.

How to make a timesheet in Excel?

Coming straight to the point – how can you make a timesheet in Excel? Super simple. See it below 👇

Format your Timesheet

We will begin with formatting our spreadsheet. Before we structure our timesheet in Excel, we must bring it in good shape.

1. Select the first three rows and around twelve columns (Columns A to L) and merge them.

This will make the header of your timesheet. It is best to add the name and logo of your business here to personalize it.

2. Select the merged cell.
3. Go to the Home Tab > Fill Color and choose a color of your liking. We are going with grey for now 🎓

Go generous with colors here (preferably the theme color of your business).

4. Go to the Home Tab > Center Align & Middle Align the text.

5. Bolden the text to add more volume to it by going to the Home Tab > Bold Button 🅱

Structure your Timesheet

Believe me, after you’ve set the headers in place, you are halfway across the job 🚀

So we are done with the header, and now it’s time that we structure our timesheet.

1. Select around six rows (or as many needed) below the header.
2. Go to the Home Tab > Wrap Text.

3. Add in different details that you’d want to be filled in the timesheet.
4. Bold them once written by pressing the Control key + B.

We have mentioned Name, Employee ID, Department, Manager Name, etc. Simply select a cell and type in what you want. And then leave some space ahead of it for the user to write in 👀

5. Select a cell or two against each of the above information fields.

6. Go to the Home Tab > Merge & Center 🤝

This makes a defined space for the users to write in what’s required.

7. To define these information fields better, select the merged cell.
8. Go to the Home Tab > Borders > Bottom Border.

Here’s what the final shape looks like 😍

Make a grid for the timesheet

Now comes the time to make the main grid where the employees will fill in the time details.

1. Divide the grid into a few columns. Like we have made columns for the Day, Date, Time In, etc.

2. Add rows to this grid as needed. We have added 5 rows for the five working days of a week.

We have also added an exemplary row to guide users on how each field must be populated.

3. Bolden the text in the headers (for both rows and columns) by selecting them and pressing the Control Key + B.

Ah! Don’t let it go that plain. To make the grid more appealing, fill in the color in the headers.

4. Select the column and the row headers.
5. Go to the Home Tab > Fill Color.
6. From the drop-down menu of colors, choose any color of your liking. We are going with a lighter tone of grey for now 🙈

Looks like the below.

How do you like it?

Borders and some final touches

We are not done already!

Though the timesheet seems already done, there are still some final touches to go. Like adding borders 🏆

1. Select the Grid.
2. Go to the Home Tab > Borders > All Borders.

Here’s what it looks like.

Good work with the borders! To add more detail to the grid, you may outline it with thick borders. To do so:

3. Go to the Home Tab > Borders > Thick Outside Borders.

Looks better now?

4. Finish off the timesheet by adding some space for the signatures of the employees and authorized signatories 📝

Now when we are all done, make the timesheet easier for the eyes by removing the gridlines from your spreadsheet.

5. Go to the View Tab > Uncheck the option for Gridlines.

Looks cleaner now 🤍

With this, know that even the sky is not the limit for you. You may add several more information fields, rows, columns, grids, and whatnot.

Automate your timesheet (use formulas)

Excel without formulas doesn’t even sound like Excel 🔔

Let’s make timesheets easy for employees by automating some calculations in them. Our grid shows a precise summary of the time spent by each employee at work.

So if you want to incentivize employees based on the number of hours spent by them at work, you need to subtotal the hours for each day. And the total up the hours for the whole week ➕

We will now set up a formula in Excel to automate this calculation.

1. Select the cells under the header Total hours.

2. Go to the Home Tab > Format > More Number Formats.

3. Under the Format Cells dialog box, select Time from the pane on the left.
4. Select the Time format highlighted below.

This will represent the total hours in 00:00 format like 12:30 (12 hours and 30 minutes).

5. In the column for Total Hours against the exemplary row, write the formula below:

= E14 – D14 – (G14 – F14) + (K14 – J14) – H14 – I14

Let’s break down the formula here for you 💡

• E14: The Time Out (when the employee’s official duty hours end)
• D14: The Time In (when the employee’s official duty hours start)
• G14 – F14: Total Break hours (End of break hours – Start of break hours)
• K14 – J14: Total overtime hours (End of overtime hours – Start of overtime hours)
• H14: Any absence hours
• I14: Sick leave hours

So through the formula above, we are telling Excel to calculate the total work hours for each day as:

Duty hours – Break hours + Overtime hours – Absence hours – Sick leave hours

Pro Tip!

Must note that for this formula to work as intended, make sure all of the referenced cells are formatted as Time ⌚

To change the format of a cell to Time:

1. Select that cell.
2. Go to the Home Tab > Format > Time.

6. Press Okay.

Excel calculates the total hours for the exemplary row to be 10. And yeah, the math makes sense.

7. Drag and drop the same formula to the whole list.

Now as employees enter the relevant hours for each day, Excel will automatically calculate the total hours for that day based on the formula above 🚴‍♂️

Done with totaling the hours for each day. Now let’s set up the formula to add up the hours for the whole week.

8. For that write the SUM function below the Total Hours column below.

= SUM (L14 : L18)

L14:L18 covers the cell range where the total hours for each day are populated.

And that’s it. Your timesheet is now all automatic 🤖

Printing your Timesheet

There are two ways how you can use your timesheet in Excel. You may use it as it is (as an Excel file) or, you may want to have it printed and filled in by employees in paper form📃

To print your timesheet after it’s all ready:

1. Press the Control key + P (the shortcut key for Print Command).

This takes you to the Print Window, as shown below 🖨

2. Under Settings, choose to Print only Active Sheets.

3. Set the orientation to Landscape.

4. Select the paper dimensions that you’d use to print it out. We have set ours to A4.

You have to ensure that your timesheet fits one page. And if it doesn’t, you may scale it down until you can preview it one page entirely 🤙

5. Under the option for Scaling, select Shrink All Columns on one page.

Pro Tip!

Printing timesheets might require you to make some final formatting touches to achieve the desired prints.

If you decide on using your timesheet by printing it, here’s a tip for you to enjoy seamless printing 💁‍♀️

Make your timesheet print ready by setting everything in place. Once done, save it as a PDF file. This way you’ll have the final print look locked. Then all you have to do is launch the PDF file and run the print command by pressing the Control key + P.

6. See if the print preview looks good and Print.

The print preview above looks all set so guess we’re good to go 👍

Frequently asked questions