Payroll Template in Excel: Build a Gross-to-Net Payroll Register
How to build a payroll template in Excel: an employee sheet, a settings tab for tax rates, and a pay-period register that turns hours into gross pay, withholdings, net pay and employer cost.
A payroll template in Excel is a workbook that turns each employee’s hours and pay rate into gross pay, subtracts taxes and deductions, and records net pay and the employer’s total cost for every pay period. A reliable one has three parts: an employee sheet, a settings sheet that holds every rate in one place, and a payroll register with one row per employee per pay date. This guide shows how to build each part, which formulas to use, and where a spreadsheet stops being enough. The examples follow US federal rules; the same structure works in other countries once you replace the rates.
What a payroll template has to calculate
Every payroll run answers the same five questions. Laying them out as columns keeps the logic easy to check.
| Step | Question | Typical columns |
|---|---|---|
| 1. Time | How many regular and overtime hours were worked? | Total hours, regular hours, overtime hours |
| 2. Gross pay | What did the employee earn before anything is taken out? | Regular pay, overtime pay, bonus, commission, gross pay |
| 3. Deductions | What comes out before and after tax? | Retirement deferral, health premium, garnishment, other |
| 4. Withholding | Which taxes must be withheld? | Federal income tax, Social Security, Medicare, state and local tax |
| 5. Result | What is paid to the employee and what does it cost the business? | Net pay, employer taxes, total employer cost |
Sheet 1: the employee list
Keep one row per employee with the details that rarely change. Every other sheet looks these up by employee ID, so a pay rate is only ever typed once.
- Employee ID and name: the ID is the lookup key; never reuse an ID after someone leaves.
- Pay type: hourly or salaried, chosen from a drop-down list.
- Pay rate: the hourly rate, or the annual salary for salaried staff.
- Overtime status: non-exempt or exempt under the Fair Labor Standards Act.
- Withholding information: filing status and the adjustments from the employee’s Form W-4.
- Recurring deductions: retirement deferral percentage, health premium per pay period.
- Work state and start date: state determines state tax and some overtime rules.
Store personal data such as Social Security numbers and bank details outside the calculation workbook, or at least on a protected sheet that only the person running payroll can open.
Sheet 2: settings for rates and limits
Put every rate in a settings sheet and give each cell a name (for example SS_Rate or Medicare_Rate). Formulas then point to names instead of numbers, and when a rate or limit changes in January you update one cell.
| Item | Federal rule (US) | How to store it |
|---|---|---|
| Social Security | 6.2% from the employee and 6.2% from the employer, up to an annual wage base | Rate cell plus a wage-base cell updated each year from the Social Security Administration |
| Medicare | 1.45% from the employee and 1.45% from the employer, no wage limit | Rate cell |
| Additional Medicare | Extra 0.9% withheld from the employee on wages above $200,000 in a calendar year; no employer match | Rate cell and threshold cell |
| FUTA | 6.0% on the first $7,000 paid to each employee in a year; most employers receive a credit that brings the rate to 0.6% | Rate, wage limit, and a note if your state has a credit reduction |
| Federal income tax | Calculated from the employee’s W-4 using the methods in IRS Publication 15-T | A lookup table or the amount entered from your withholding calculation |
| State and local tax, SUTA | Set by each state; rates and wage bases differ | One row per state you pay in |
Do not hard-code income tax brackets into long nested IF formulas. Publication 15-T changes every year, and a lookup table you can replace is far easier to audit.
Sheet 3: the payroll register
The register is a table with one row per employee per pay date. Format it as an Excel Table so formulas fill down automatically and totals can be built with SUMIFS by pay date.
Hours and gross pay
For a non-exempt employee paid weekly, federal law requires overtime at 1.5 times the regular rate for hours above 40 in a workweek. Split the hours first, then price them:
- Regular hours:
=MIN([@Hours],40) - Overtime hours:
=MAX([@Hours]-40,0) - Gross pay:
=[@RegHours]*[@Rate]+[@OTHours]*[@Rate]*1.5+[@Bonus]
Some states, such as California, also require daily overtime. If you have staff there, add a daily hours sheet rather than relying on the weekly total. For salaried employees, gross pay per period is the annual salary divided by the number of pay periods: 52 for weekly, 26 for biweekly, 24 for semimonthly and 12 for monthly payroll.
Taxable wages
Pre-tax deductions do not all reduce the same wages, so keep two taxable-wage columns:
- Income-tax wages: gross pay minus deductions that are exempt from income tax, such as traditional 401(k) deferrals and qualifying Section 125 health premiums.
- FICA wages: gross pay minus deductions exempt from Social Security and Medicare. Section 125 premiums usually reduce FICA wages; traditional 401(k) deferrals do not.
Withholding formulas
- Social Security, respecting the wage base:
=ROUND(MIN([@FICAWages],MAX(SS_WageBase-[@YTD_SSWages],0))*SS_Rate,2) - Medicare:
=ROUND([@FICAWages]*Medicare_Rate,2) - Additional Medicare, only once year-to-date wages pass the threshold:
=ROUND((MAX([@YTD_Wages]+[@FICAWages]-AddMed_Threshold,0)-MAX([@YTD_Wages]-AddMed_Threshold,0))*AddMed_Rate,2). The second MAX removes wages that were already above the threshold in earlier periods, so only this period’s excess is taxed. - Federal income tax: the result of your Publication 15-T calculation or lookup for that employee.
- Net pay:
=[@Gross]-[@PreTaxDed]-[@FIT]-[@SS]-[@Medicare]-[@AddMed]-[@StateTax]-[@PostTaxDed]
The year-to-date columns come from a SUMIFS over earlier rows for the same employee in the same calendar year, which is why the register should never be split into separate files per month.
Employer cost
Add a column that shows what each paycheck really costs the business: gross pay plus the employer’s Social Security and Medicare match, FUTA and state unemployment tax, and any employer-paid benefits. This is the figure to use in your 13-week cash flow forecast and when pricing a new hire.
A worked example
An hourly employee earns $20 an hour, worked 44 hours this week and defers 5% of pay to a traditional 401(k). There are no health premiums.
| Line | Calculation | Amount |
|---|---|---|
| Regular pay | 40 × $20 | $800.00 |
| Overtime pay | 4 × $20 × 1.5 | $120.00 |
| Gross pay | $800 + $120 | $920.00 |
| 401(k) deferral | 5% × $920 | $46.00 |
| Social Security (employee) | 6.2% × $920 (FICA wages are not reduced by the 401(k)) | $57.04 |
| Medicare (employee) | 1.45% × $920 | $13.34 |
| Pay before income taxes | $920 − $46 − $57.04 − $13.34 | $803.62 |
Federal and state income tax are then withheld from the $803.62, using $874 of income-tax wages ($920 minus the deferral) and the employee’s W-4. On the employer side, the business pays another $57.04 of Social Security and $13.34 of Medicare, plus unemployment taxes.
Checks before you pay anyone
- Totals match the timesheets. Total hours in the register should equal total approved hours. A timesheet template that feeds the register avoids retyping.
- Net pay is plausible. Flag any row where net pay changed by more than, say, 15% from the employee’s last paycheck and explain it.
- Year-to-date limits behave. Test the Social Security formula with an employee near the wage base to make sure it stops at the limit.
- Rounding is consistent. Round each tax line to the cent with ROUND, not with cell formatting, so the register totals agree with the paychecks.
- Deposits and filings are scheduled. Withheld taxes belong to the government; record the deposit due dates in your calendar the same day you run payroll.
Protecting and keeping payroll records
Lock the formula columns and the settings sheet so that only the input cells (hours, bonus, one-off deductions) can be edited; how to protect formulas in Excel walks through the steps. Save each finished pay period as a read-only copy or PDF. The IRS asks employers to keep employment tax records for at least four years, and state rules can require longer, so archive the register rather than overwriting it.
When payroll software is the better choice
A spreadsheet works well for a handful of employees in one state with simple pay. Consider dedicated payroll software when you:
- have employees in several states or localities with their own taxes;
- want direct deposit, pay stubs and tax deposits handled automatically;
- need Forms 941, 940, W-2 and state returns prepared from the same data;
- pay tips, garnishments or benefits with complicated tax treatment.
Even then, the register structure above is useful: it is how you check what the software produced.
Important limits
This template organises payroll calculations; it does not file returns, make tax deposits or decide how a payment should be taxed. Rates, wage bases and withholding tables change every year, and state rules vary. Confirm the current figures with the IRS, the Social Security Administration and your state agency, or with a payroll professional, before you pay employees.
Related guides
Payroll sits next to scheduling, time and leave tracking. See the employee schedule template, the leave tracker and the employee onboarding checklist, or browse every guide in Operations & HR.
Frequently asked questions
Can Excel calculate federal income tax withholding?
Yes, if you build the percentage-method tables from IRS Publication 15-T into a lookup sheet and update them every year. Many small employers instead enter the withholding amount from the IRS tables or an online calculator and let Excel handle everything else.
Does this payroll template work in Google Sheets?
The formulas (MIN, MAX, ROUND, SUMIFS) work the same way in Google Sheets. Use named ranges for the rates and protected ranges for the formula columns, and limit sharing to the people who run payroll.
How do I handle a salaried employee?
Divide the annual salary by the number of pay periods and enter that as gross pay. If the salaried employee is non-exempt, they are still owed overtime for hours above 40 in a workweek, so keep their hours too.
How many employees can a spreadsheet payroll handle?
There is no fixed number, but the risk of error grows with every state, pay type and benefit you add. Most businesses move to software well before they reach a few dozen employees.
Can I use the same template outside the United States?
The three-sheet structure works anywhere. Replace the settings sheet with your country’s social contributions and income tax rules, and rename the withholding columns to match.