For small businesses with a handful of employees, dedicated payroll software can feel like overkill. You are paying $50-100 per month for a system that processes four paychecks. Excel handles the core tracking and calculation needs for very small teams without any subscription fee, and once it is set up correctly it takes about ten minutes per pay period to run.
This guide covers building a weekly timesheet, calculating regular and overtime hours automatically, and generating a simple payroll summary that gives you everything you need to process payroll accurately every week.
Start with a simple employee reference sheet. One row per employee with the details you reference every pay period. Your timesheet and payroll sheets will pull from this automatically using VLOOKUP so you never have to retype rates or names.
| Column | What to Enter |
|---|---|
| Employee ID | Short unique code, EMP001 or initials like JDS |
| Full Name | Employee's full name |
| Job Title | Their role |
| Pay Type | Hourly or Salary |
| Hourly Rate | For hourly employees, their regular hourly rate |
| Weekly Salary | For salaried employees, their weekly gross pay |
| OT Eligible | Yes or No, salaried employees are often exempt |
| Start Date | When they joined the company |
| Status | Active or Inactive |
Your weekly timesheet logs hours worked for each employee each day of the week. Keep it simple, one row per employee per week, with daily hours across the columns. Excel calculates the totals, overtime, and gross pay automatically.
Here is what a completed weekly timesheet looks like:
Set up columns for each day of the week plus total hours, overtime hours, and gross pay. The formulas that do the work:
Total hours worked for the week:
Regular hours — capped at 40:
Overtime hours — anything above 40:
Gross pay for hourly employees including overtime at 1.5x:
Pull hourly rate from your employee master list automatically:
Your pay period summary pulls together all employee totals for the period and gives you a clean record of what was paid and to whom. This is the sheet you reference when processing payroll and the one that provides the audit trail if any hours or payments are ever questioned.
| Column | What to Enter or Formula |
|---|---|
| Pay Period | The date range this pay period covers |
| Employee ID | Reference to your employee list |
| Employee Name | =VLOOKUP from employee master list |
| Regular Hours | Pulled from timesheet via SUMIFS |
| Overtime Hours | Pulled from timesheet via SUMIFS |
| Hourly Rate | =VLOOKUP from employee master list |
| Regular Pay | =Regular Hours x Hourly Rate |
| Overtime Pay | =Overtime Hours x Hourly Rate x 1.5 |
| Gross Pay | =Regular Pay plus Overtime Pay |
| Notes | Any adjustments, bonuses, or deductions to flag |
Beyond week-to-week tracking, a year-to-date payroll summary gives you a running total of what you have paid each employee across the year. This is valuable for budgeting, W-2 preparation, and understanding your total labor cost at any point during the year.
Set it up with one row per employee and columns for each pay period. Use SUMIF to pull each employee's gross pay from your weekly summaries:
Add a total row at the bottom showing your total payroll cost year to date. Comparing this to your labor budget each month tells you whether payroll is tracking in line with your plan or running over, early enough to make adjustments.
Excel works well for hours tracking and gross pay calculation with a small team. The point at which it becomes insufficient is usually when you need to handle payroll taxes automatically. Calculating federal withholding, Social Security, Medicare, and state taxes correctly for multiple employees with different filing statuses is genuinely complex and the consequences of errors are significant.
A practical approach that many small businesses use: track hours and calculate gross pay in Excel, then enter those gross pay amounts into a low-cost payroll service like Gusto or Wave Payroll that handles all the tax withholding and filings automatically. You get the simplicity of Excel for the hours side and the compliance protection of dedicated software for the tax side.
If you would like a custom employee hours and payroll tracking workbook built for your team, with your pay structure, your overtime rules, and your reporting needs, that is exactly what we do at HelpMyData.
Tell us about your team and pay structure and we'll build a complete Excel timesheet and payroll tracker tailored to your needs. Starting at just $75.
📧 Get Started Today