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 🤔


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.


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.
Merging the first few cells of excel spreadsheet

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

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

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

  1. Go to the Home Tab > Center Align & Middle Align the text.
Center aligning the header
  1. Bolden the text to add more volume to it by going to the Home Tab > Bold Button 🅱
Adding the name and logo of the company

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.
Option to Wrap text in Excel sheet

The Wrap text option restricts the contents of a cell to the boundaries of that cell. And if the content doesn’t fit in the existing cell size, it automatically resizes the subject row to fit it in.

Kasper Langmann, Microsoft Office Specialist
  1. Add in different details that you’d want to be filled in the timesheet.
  2. 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 👀

Adding labels to the timesheet
  1. Select a cell or two against each of the above information fields.
Selecting of cells
  1. Go to the Home Tab > Merge & Center 🤝
Merging Cells

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

  1. To define these information fields better, select the merged cell.
  2. Go to the Home Tab > Borders > Bottom Border.
Applying bottom borders

Here’s what the final shape looks like 😍

Bordering the information fields

Make a grid for the timesheet

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

There is no hard and fast rule for how the grid should be. You can create it just as you like. Add as many details as you want to add and style it in any way you like 🍭

Kasper Langmann, Microsoft Office Specialist
  1. Divide the grid into a few columns. Like we have made columns for the Day, Date, Time In, etc.
Making a grid
  1. Add rows to this grid as needed. We have added 5 rows for the five working days of a week.
Adding rows

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

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

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

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

Looks like the below.

Headers with grey color

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.
Adding simple borders

Here’s what it looks like.

Grid with borders for multiple employees

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

  1. Go to the Home Tab > Borders > Thick Outside Borders.
Adding thick outside borders.

Looks better now?

Grid with thick outside borders.
  1. 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.

  1. Go to the View Tab > Uncheck the option for Gridlines.
Removing gridlines

Looks cleaner now 🤍

Weekly timesheet template

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.
Selection of Cells
  1. Go to the Home Tab > Format > More Number Formats.
Accessing more number formats
  1. Under the Format Cells dialog box, select Time from the pane on the left.
  2. Select the Time format highlighted below.
Applying the right time format

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

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

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

Writing the formula to calculate regular and overtime hours

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.
  1. Press Okay.
Excel timesheet formula to track hours

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

  1. 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.

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

= SUM (L14 : L18)

Excel timesheet calculator

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

Be careful not to include the exemplary hours in the SUM range 🧐

Kasper Langmann, Microsoft Office Specialist

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 🖨

The Print Command
  1. Under Settings, choose to Print only Active Sheets.
Command to print active sheets only
  1. Set the orientation to Landscape.
Setting the print orientation to landscape
  1. Select the paper dimensions that you’d use to print it out. We have set ours to A4.
Printing dimensions set 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 🤙

  1. Under the option for Scaling, select Shrink All Columns on one page.
Scaling set to shrink all columns

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.

  1. See if the print preview looks good and Print.
Final Print Preview of excel timesheet template

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

Frequently asked questions

Format time in Excel to calculate hours worked by following these steps:

  1. Select the cells containing the time.
  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. Choose any Time format as desired.

To calculate total hours and minutes in Excel:

  1. Ensure the cells containing these hours and minutes are formatted as Time.
  2. The cell where the total hours/minutes are to be calculated must be formatted as Time in the format (00:00).

After this is done, write a simple SUM function (or formula) to total hours/minutes.