Back
Hero background image of many pineapples

How to Create A Simple Excel Timesheet

See all resources

How to Create A Simple Excel Timesheet

Two basic Excel formulas

This is a short visual guide on how to build a simple Excel timesheet with automatically calculated hours that can be easily shared with your employees.

The example we are showing here only uses two basic Excel formulas, meaning that this timesheet template can be copied, changed and customized with ease, depending on your organization's needs. Creating a simple timesheet in MS Excel

Illustration of people standing on a line chart

Defining categories for timesheets

Defining categories

First of all, you need to figure out what kind of information you actually need to have on your timesheets. Typically you would need a field for employee name, the time period the timesheet covers (week, month), but also the name of department & manager.

Of course, you can also add fields for social security numbers, employee IDs if needed, but simpler is better.


Use columns to break up the days

Organizing timesheet into columns

In this example, the fields for morning working hours and afternoon hours are separated, to account for the longer lunch break in the middle of the day. Also, by entering hours in the timesheet twice a day, the employee won't have to remember all their day's hours at the end of the day.

Breaking up the day, however, means that now we have to add up the hours. The next step shows you how to calculate the hours automatically.

Calculating daily working hours

Calculating daily work hours

In this example timesheet, we will be using two basic formulas - one for adding up the total hours for a single day, and one for adding up all working hours for the week.

To add up a single day's hours, insert a simple =SUM(B7+D7) formula into the "Total hours" cell at the end of the first work day (E7 in this example). Once an employee has typed in their hours in the "morning" and "afternoon" cells, they will be automatically added up in the "total" field.

To use the same formula for the other days, simply right click and copy the cell containing the formula and choose "paste formula" for the remaining total hour cells - Excel will automatically change the formula for the other rows, so no need to do this manually!


Calculating total weekly hours

Calculating weekly total working hours

To get the total hours your employee worked in a week, you will need to add up all the daily total hours for that week. To do that, pick a cell where you want the weekly total count to be displayed and insert another SUM formula.

For example in this example, the total daily working hour fields are all in column D, starting at row number 3 and ending with row number 8. To calculate the weekly total hours, the formula would be =SUM(E7:E11). This formula adds up all values in column D that fall between rows 3 and 8.


Locking cells to prevent editing

Locking timesheet cells for editing

Once you've added these formulas and formatted the sheet to your liking, you will have a perfectly functional, easy-to-use timesheet. Now it's time to protect your design from unwanted changes.

To avoid someone accidentally deleting a formula or breaking your formatting, you can limit the cells your employees can edit by locking them. Typically, your employees would only need to manually insert the "morning" and "afternoon" working hours, but you can also choose to let them add their own names and edit the date ranges.

By default, all cells in Excel are set as "locked" - to designate the cells you want your employees to be able to edit, drag and select these cells, select "format cells" and under the "Protection" tab, untick the checkbox that says "locked".

At this point, you have only defined which cells should be "unlocked" - for these changes to have an actual effect, you need to enable the "protect sheet" settings.


Locking the timesheet

Protecting excel timesheets from editing

Once you have unlocked the cells you need your employees to edit, head to the "Format" menu and select "protect sheet">. You will see a long list of options, but you only need to tick the box where it says "select unlocked cells".

When you've ticked the box, choose a password for unlocking the timesheet, hit "OK" and you're done. But make sure you don't forget the password!

Simply copy the timesheet file and pass the copies on to your employees so they can start logging their time.

3D illustration of stopwatch with a Toggl Track icon
Toggl Track as a free timesheet alternative

Use Toggl Track to skip all this work

Ultimately, Excel timesheets are tricky to manage and not very accurate. This is why we created Toggl Track for tracking work time.

It's very simple to use and easy to set up. With options for tracking in real time and filling in work hours later, it's basically a much more accurate and versatile version of a timesheet. On top of that, Toggl Track has a powerful reporting feature that automatically sorts your timesheet data into customizable reports that are easy to share - and all of this is stored safely in the cloud.

Feel free to check it out by signing up below - Toggl Track is free for small teams!

Sign up for Toggl Track

All plans come with a free, 30-day trial of Toggl Track Premium—no credit card required. You can choose to upgrade at the end of the trial or continue using Track for free.

Prefer a product demo instead?

Teams of 10+ are eligible for a personalized demo to see how Toggl Track can meet your time tracking goals

3D model of a Toggl pinball machine

Get Your Know-How Here

Supercharge your productivity and project management with these resources

Illustration of man surfing on spreadsheets

Difference Between CSV and XLS

A closer look at each format

Illustration of woman in a clock and her arms out as clock hands

9/80 Work Schedule

Learn more about the 9/80 work schedule and see if it would work for your team

Illustration of a businessman and money symbols

How to Calculate Your Billable Hours with Toggl Track

Automate calculating billable rates, even in different currencies

Toggl Global

Discover other Toggl tools:

© 2021 Toggl. All rights reserved.