This wikiHow teaches you how to create a Microsoft Excel timesheet for payroll. You can do this on a Windows or Mac computer by using a pre-existing template or creating your own timesheet.
Step
Method 1 of 2: Using Templates
Step 1. Open Microsoft Excel
Microsoft Excel is a dark green application with a white "X".
Step 2. Click the search bar
It's at the top of the Excel window.
On Mac computers, first click File in the top left corner, then click New from Templates… in the drop-down menu.
Step 3. Type time sheet in the search bar and press Enter
Those keywords will look up the timesheet template in the template database.
Step 4. Choose a template
Click the template to use. The template page will open so you can see its format and appearance.
If you don't like the newly selected template, click X on the template window to close it.
Step 5. Click Create
It's to the right of the template preview. With that command, you will create a new template in Excel.
Step 6. Wait for the template to finish loading
It may take a few seconds. Once the template has finished loading, you can move on to creating the timesheet.
Step 7. Enter all the required information
Each template is slightly different from the others. However, there is usually an option to enter the following information:
- Hourly rate - The amount paid to a specified employee per hour worked.
- Employee identification - Employee name, ID number, and so on.
Step 8. Enter the amount of working time in the appropriate column
Most timesheets have a column containing the day of the week on the far left. This means that the number of hours worked is entered into the "Time" (or similar) column to the right of the "Day" column.
Example: if an employee works 8 hours on Mondays in the first week of a given month, look for the cell "Monday" in the "Week 1" column and type 8.0
Step 9. Review the results
Timesheet templates will always count the total number of hours entered. If you have entered the hourly rate, the timesheet will show the amount of the employee's income.
Step 10. Save your timesheet
How to do it:
- Windows - Click File, click Save As, double click This PC, click the location to save on the left side of the window, type the name of the document (e.g. "January Timesheet") into the "File name" text box, and click Save.
- Mac - Click File, click Save As…, enter the document name (eg "January Timesheet") in the "Save As" field, select a location to save by clicking the "Where" box and clicking a folder, then click Save.
Method 2 of 2: Creating a Manual Timesheet
Step 1. Open Microsoft Excel
The Microsoft Excel application icon resembles a white "X" on a dark green background.
Step 2. Click Blank workbook
This white icon is located at the top left of the new Excel page.
Skip this step on a Mac
Step 3. Enter the text header
Type the following text headers into these cells:
- A1 - Type Day
- B1 - Type Week 1
- C1 - Type Week 2
- You can enter Week [number] in the cell D1, E1, and F1 (if needed).
- If you are also calculating overtime, add the Overtime text header in the cell C1 for Week 1, tues E1 for Week 2, and so on.
Step 4. Enter the days of the week
in cell A2 until A8, type Sunday to Saturday in order.
Step 5. Enter the rate
Type Rates into the cell A9, then enter the hourly rate in cell B9. For example, if the rate is $15.25 per hour, type 15.25 in cell B9.
Step 6. Add a "Total" row
Type Total in the cell A10. Total hours worked are entered here.
If overtime is also taken into account, type Overtime to A11 and enter the overtime rate to B11.
Step 7. Enter the formula for Week 1
This formula will add up the hours worked from Sunday to Saturday and then multiply the number by the hourly rate. Do it this way:
- Click the Week 1 "Total" cell, i.e. B10.
-
Type
=sum(B2:B8)*B9
- then press Enter.
Step 8. Enter the formula for the following weeks
Copy the entered formula for Week 1, then paste it into the "Total" row under the week you selected and replace the formula part B2:B8 with the column alphabet of the week (eg C2:C8).
-
For overtime, the above formula can be used to calculate overtime by substituting B9 with B11. For example, if the Week 1 "Overtime" column is in the column C, insert
=sum(C2:C8)*B11
annoyed C10.
-
If there is overtime, create a "Final Total" section by typing Final Total into the cell A12, type
=sum(B10, C10)
annoyed B12, and repeat for each column "Week [number]" with the correct column alphabet.
Step 9. Fill in the time sheet
Enter the number of hours worked each day in the "Week 1" column. You'll see the number of hours and total revenue earned at the bottom of the worksheet, under the "Totals" section.
If overtime is also counted, fill in the column as well. The "Final Total" section will change to reflect the combination of regular pay and overtime pay
Step 10. Save your timesheet
To save:
- Windows - Click File, click Save As, double click This PC, click the location to save the file on the left side of the window, then type a document name (eg "January Timesheet") into the "File name" box, and click Save.
- Mac - Click File, click Save As…, enter the document name (eg "January Timesheet") in the "Save As" field, select a location to save by clicking the "Where" box and clicking a folder, then click Save.
