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.
- 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.
- Select the merged cell.
- 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).
- Go to the Home Tab > Center Align & Middle Align the text.
- 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.
- Select around six rows (or as many needed) below the header.
- Go to the Home Tab > Wrap Text.
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.
- Add in different details that you’d want to be filled in the timesheet.
- 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 👀
- Select a cell or two against each of the above information fields.
- Go to the Home Tab > Merge & Center 🤝
This makes a defined space for the users to write in what’s required.
- To define these information fields better, select the merged cell.
- 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.
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 🍭
- Divide the grid into a few columns. Like we have made columns for the Day, Date, Time In, etc.
- 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.
- 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.
- Select the column and the row headers.
- Go to the Home Tab > Fill Color.
- From the drop-down menu of colors, choose any color of your liking. We are going with a lighter tone of grey for now 🙈
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 🏆
- Select the Grid.
- 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:
- Go to the Home Tab > Borders > Thick Outside Borders.
- 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.
- Go to the View Tab > Uncheck the option for Gridlines.
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.
- Select the cells under the header Total hours.
- Go to the Home Tab > Format > More Number Formats.
- Under the Format Cells dialog box, select Time from the pane on the left.
- Select the Time format highlighted below.
This will represent the total hours in 00:00 format like 12:30 (12 hours and 30 minutes).
- 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