A well-built payroll spreadsheet can give a small business a repeatable way to collect hours, calculate gross pay, record deductions, estimate employer costs, and prepare approvals. This guide explains how to set up an Excel or Google Sheets payroll template, choose the right inputs, handle hourly and salaried employees, separate contractor payments, and review each payroll before money is sent.
Overview
A payroll spreadsheet template is most useful when it separates permanent employee information from each pay run. Instead of overwriting the same worksheet every week or month, create a workbook with dedicated tabs and retain a copy of each completed period.
A practical small business payroll spreadsheet can include these tabs:
- Instructions: Explain the purpose of each tab, the person responsible for approval, and the date the formulas or payroll assumptions were last reviewed.
- Employee setup: Store employee ID, name, employment type, pay rate, pay frequency, regular schedule, tax setup references, benefit elections, and start or end dates. Restrict access to this tab because it contains sensitive information.
- Time and PTO: Enter regular hours, overtime hours, unpaid time, paid time off, holidays, and approval status for the current period.
- Payroll calculation: Calculate regular pay, overtime pay, gross pay, deductions, estimated taxes, and net pay.
- Contractors: Track approved invoices separately from employee wages. A contractor invoice is not automatically an employee payroll record.
- Pay-stub summary: Present pay-period dates, earnings, deductions, net pay, and year-to-date totals in a readable format. Review the pay stub template guide for a separate checklist of pay-statement fields.
- Checks and approvals: Record who reviewed hours, who approved the calculation, the payment date, and any correction made after review.
Use Excel or Google Sheets for calculation and record organization, but do not treat a spreadsheet as a substitute for checking the payroll rules that apply to the business. Tax withholding, employer contributions, wage requirements, recordkeeping, pay-statement rules, and filing deadlines can vary by location and may change. Keep rate tables and policy notes in a separate, clearly dated area rather than hiding them inside formulas.
How to estimate payroll in a spreadsheet
Start each pay run by identifying the pay period and locking the relevant dates. A simple header might include:
- Pay period start date
- Pay period end date
- Scheduled pay date
- Pay frequency: weekly, biweekly, semimonthly, or monthly
- Payroll preparer and approver
For an hourly employee, the basic calculation is:
Regular pay = regular hours × hourly rate
Overtime pay = overtime hours × overtime rate
Gross pay = regular pay + overtime pay + other taxable or non-taxable earnings, as applicable
Do not assume that every extra hour is treated identically. Use the overtime rules applicable to the employee and work location, and document which hours were included in the overtime calculation. If the business uses an overtime multiplier, place that multiplier in a visible assumptions cell instead of typing it into multiple formulas.
For a salaried employee, convert the annual salary to the selected pay frequency only when that method matches the employer's payroll practice. A basic estimate is:
Periodic salary = annual salary ÷ number of pay periods in the year
For example, a biweekly payroll uses the number of biweekly pay periods established by the business's calendar. A monthly payroll uses the number of monthly periods. If an employee starts, leaves, changes salary, or takes unpaid time during a period, document the proration method before applying it.
For deductions, create separate columns for each recurring item rather than one combined deduction column. This makes it easier to reconcile benefits, garnishments, retirement contributions, and voluntary withholdings. The payroll deduction worksheet can help organize these categories.
A simplified net-pay structure is:
Net pay = gross pay − employee deductions − employee tax withholdings
Employer payroll costs should be tracked separately:
Total employer cost = gross pay + employer taxes and contributions + employer-paid benefits
Keep employer costs out of the employee's net-pay calculation. They are useful for budgeting and job-cost analysis, but they are not amounts paid to the employee.
Inputs and assumptions
A spreadsheet is only as reliable as its inputs. Add a data-validation rule where practical so users select a pay frequency, employment type, or approval status from a controlled list. Use one row per employee per pay run, or one row per earning type when more detail is needed.
Recommended input fields include:
- Employee or contractor ID
- Worker classification and department
- Pay rate and rate effective date
- Regular, overtime, PTO, holiday, and unpaid hours
- Commission, bonus, reimbursement, or other earning categories
- Benefit, garnishment, retirement, and voluntary deduction amounts
- Applicable withholding assumptions, identified by jurisdiction and effective period
- Payment method and approval status
Use a separate rate-and-assumptions table with columns for item, value, jurisdiction, effective date, source or verification note, and reviewer. This is where an editor or payroll administrator can refresh withholding tables, contribution limits, minimum-wage assumptions, and other values at the start of a tax year or whenever rules change. Avoid presenting an unverified rate as current.
PTO accrual also needs a defined assumption. If the policy accrues a fixed amount each pay period, a simple estimate is accrual per period = annual accrual allowance ÷ annual pay periods. If accrual depends on hours worked, use the policy's stated accrual rate and maintain a running balance. The spreadsheet should show beginning balance, additions, usage, adjustments, and ending balance.
Contractor payments belong in a separate workflow. Record the contractor's legal name, invoice number, service period, approval date, amount, payment date, and required tax documentation status. Do not combine contractor invoices with employee wage calculations simply because both are paid on the same day. See the contractor payment workflow template for a more complete process.
Worked examples
Assume a biweekly pay run contains one hourly employee who works 76 regular hours at an hourly rate of 20.00 and 4 overtime hours at an overtime rate of 30.00. Before deductions and withholding estimates:
- Regular pay: 76 × 20.00 = 1,520.00
- Overtime pay: 4 × 30.00 = 120.00
- Gross pay: 1,520.00 + 120.00 = 1,640.00
The spreadsheet should then calculate or enter each applicable employee deduction separately. If the row includes 75.00 for a benefit and 25.00 for another voluntary deduction, those amounts should remain identifiable even if the final net-pay formula subtracts their combined total. Withholding estimates should come from the business's reviewed tables or payroll process, not from an unverified fixed percentage.
For a salaried employee with an annual salary of 62,400 and 26 biweekly pay periods, the unadjusted periodic salary estimate is:
- 62,400 ÷ 26 = 2,400 per biweekly period
If the employee has a 100 employee deduction and 250 in employer-paid benefits, the employee's net-pay calculation and the employer-cost calculation remain separate. The 100 may reduce net pay; the 250 increases employer cost but does not reduce the employee's net pay.
Before approval, compare the spreadsheet with source records: the approved timesheet, PTO request, salary-change notice, benefit report, deduction instructions, and payment register. A useful final check is to compare total net pay to the bank payment file or payment instructions. The payroll reconciliation checklist provides a repeatable review sequence.
When to recalculate and update the template
Review the payroll spreadsheet before every pay run, not only when a formula appears broken. Recalculate whenever an employee is hired, leaves, changes pay, changes work location, changes benefits, or moves from hourly to salaried status. Recheck the workbook when a pay frequency, payroll calendar, deduction instruction, or PTO policy changes.
At the start of each tax year, review the rate-and-assumptions tab, formulas, jurisdiction labels, contribution limits, wage assumptions, and year-to-date reset rules. Also review the workbook when tax or labor guidance changes during the year. Keep the previous version unchanged and save a dated copy of the revised template so the audit trail is clear.
Before each payroll approval, use this short checklist:
- Confirm the pay period and scheduled pay date.
- Import or enter approved time records and compare unusual hours with the source timesheet.
- Check new hires, terminations, rate changes, PTO, bonuses, and reimbursements.
- Review employee and employer deductions separately.
- Verify formulas copied through every active row and inspect any override.
- Compare current-period and year-to-date totals with the prior run for unexplained changes.
- Reconcile net pay, employer cost, and payment instructions before approval.
- Save the final workbook as a read-only period record and restrict access to sensitive data.
Use the payroll calendar template to connect pay periods with processing dates and deadlines, and consult the payroll compliance checklist for recurring tasks. A spreadsheet works best as a controlled calculation and review tool: keep the inputs documented, update assumptions deliberately, and obtain qualified advice when the business's payroll situation is complex or uncertain.