How to Create a Timesheet in Excel | Smartsheet (2024)

Download the Weekly Timesheet Template for Excel

This Excel timesheet template includes space to log your hours for each day of the week, including overtime, sick, vacation, and holiday hours. The template includes formulas to calculate the number of hours worked and how much you are owed based on your hourly rate. This template is free to download, easy to use, and completely customizable. Follow along, and we’ll show you how.

1. After you download the template above, open it. It will automatically open in Excel.
2. If the Protected View banner appears at the top of the spreadsheet, click Enable Editing. This will allow you to make changes and add information to the template.
   
3. Ensure the template includes all of the sections that you will need. This template is fully editable, so you can easily change any of the text fields. We’ll cover how to add break columns to customize your template in the tutorial below.

1. On the left side of the spreadsheet, click on the Week of field and add the date that starts your workweek. The template will automatically populate the dates for the rest of the week in the date column to the right.
   
2. Next, click on the Employee Name field and enter the name of the employee whose hours you are tracking.
   
3. Then click on the Employee ID field and enter the employee’s ID number, if applicable.
   

1. In the bottom section of the template, fill in the employee’s rates of pay by clicking on each field in the row and entering the hourly pay rate for each of their regular, overtime, sick, vacation, holiday, and other hours.
   

1. Click on the Start Time field for the first day of the week, and enter the employee’s start time in the format HH:MM AM or PM.
   
2. Click on the Finish Time field for the first day of the week, and enter the employee’s finish time in the same format.
   
3. Repeat this process for each day of the week the employee worked.
   

1. For each day of the week, calculate the number of hours of each type worked, and enter it in the appropriate field. The template will automatically add up the total number of hours per row for each day in the Total Hours column.
   
   Pro Tip: If you are having trouble calculating the total number of hours and minutes worked each day, use an online hours calculator.
2. The template will automatically calculate the total number of hours for rate type and display the total pay per day, as well as the weekly total pay.
   

1. When all of your timesheet information has been added, verify that it is correct.
2. Click the File tab.
   
3. Click Save As to save your timesheet.
   

Right-click the desired cell, then click Format Cells… in the menu. Cycle through the menu options Number, Alignment, Font, Border, Fill, and Protection to change number styles, fonts, and colors; add or change borders; change the cell’s alignment; and protect cells.

Pro Tip: To format a group of cells, hold the Shift key on your keyboard and click the first and last cells within the desired range. Release the Shift key. Right-click anywhere inside the highlighted selection. Click Format Cells… in the drop-down menu.

1. Select your desired cell or cells, and right-click. Select Format Cells… from the drop-down menu.
2. Click on the Font tab. Select your new font, then click the OK button.
   
3. Repeat these steps to change the font in all desired cells.
   

1. Select your desired cell or cells and right-click. Select Format Cells… from the drop-down menu.
2. Click the Fill tab. Select your desired fill color, then click the OK button.
   
3. Repeat these steps to add or change color in all desired cells.

1. Click the Insert tab, then click Pictures. Choose Stock Images or Online Pictures to browse categories of images you can add to your timesheet. To add an image saved on your computer, click This Device… in the Insert Picture From menu.
   
2. Navigate to the location of the image on your computer, select the image you would like to insert, then click the Insert button.
   
3. Resize your image by clicking and dragging the circles surrounding the image, and click and drag the center of the image to place it where you would like it to go.
   

1. Right-click on the F column and click Insert from the drop-down menu to add a column to the left.
   
2. Repeat this process to add a second column.
3. Hold the Shift key on your keyboard, and click cells G2 and G9 to select the range. Release the Shift key.
   
4. Right-click within the selected range and click Format Cells… in the drop-down menu.
   
5. In the Format Cells… window, click the Border tab, click the options to create left and right borders, then click the OK button.
   
6. Click in cell F2, press the Caps Lock key on your keyboard, and enter “BREAK START.”
   
7. Click in cell G2, press the Caps Lock key on your keyboard, and enter “BREAK END.”
   
8. Use these new columns to track the time the employee clocked out for their break and when they clocked back in.

Pro Tip: Don’t forget to subtract this time when calculating the employee’s total hours if the break is unpaid.

The instructions below are for Excel found in Microsoft Enterprise 365. Some Excel versions of Microsoft 365 don’t support the Allow Edit Ranges button. If your version of Excel doesn’t have this button, you’ll find steps for protecting a range of cells below.

1. Click the Review tab, then click Allow Edit Ranges.
   
2. Click the New button in the Allow Users to Edit Ranges window.
   
3. Click the Up Arrow button in the New Range window.
   
4. Select the range of cells you would like to allow users to have edit access to. To select a large range of cells, hold the Shift key on your keyboard and click the top-leftmost cell and the bottom-rightmost cells. Release the Shift key. To select additional cells, hold the Ctrl key on your keyboard and click on the additional cells you would like to add to your selection.
   
   Pro Tip: Select the cells that you would like users to be able to fill in. Your selection may not match the image, depending on what you would like to allow edit access to for all users.
5. When all your cells have been selected, click the Down Arrow button in the New Range window.
   
6. Change the Title of your new range, if desired, and create and enter a password in the Range Password field. Then click the OK button.
   
7. Reenter the password you created in the previous dialog box to confirm, then click the OK button.
   
   Pro Tip: Choose a password you are sure you will not forget, as it cannot be recovered if lost.
8. Click Apply, then click Protect Sheet.
   
9. Create a password to unprotect the sheet and enter it in the Password to unprotect sheet field. Then click the OK button.
   
10. Reenter the password you created in the previous dialog box to confirm, then click the OK button.
    
    Pro Tip: Use the same password that you created to unprotect your range of cells above to avoid greater confusion.
11. Click on one of the cells within the range you selected in step 4. Enter the password you created, then click the OK button.
    
12. Your sheet is now protected, and users can only make changes to the cells within the range that you created.

These instructions are for how to protect cells in Excel for Microsoft 365.

1. Click and select the cells you want to protect on the timesheet.
   
2. On the Home tab, click Format and click Format Cells.
   
3. In the pop-up window on the Protection tab, click Locked. Click the OK button.
   
4. On the Review tab, click Protect Sheet. Enter a password and enter it again in the Verify box. Click the OK button. The cells are now locked and can’t be changed unless someone knows the password.