Excel guide · 5 min read
Payroll formulas in Excel: a gross-to-net payroll register
=MIN(threshold,hours), overtime hours =MAX(0,hours-threshold), gross pay =ROUND(regular*rate+overtime*rate*multiplier,2), deductions as rate cells times gross (each rounded to cents) plus fixed amounts, and net pay =gross-deductions. Keep every rate and threshold in input cells, and take the actual deduction rules from your payroll provider or accountant; Excel has none built in.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Period end | Employee | Hours | Rate | Regular hrs | OT hrs | Gross | Deductions | Net | OT after (h/week) | 40 | |
| 2 | Nov 6, 2026 | Ana Lopez | 46 | 24 | 40 | 6 | 1176 | 108.46 | 1067.54 | OT multiplier | 1.5 | |
| 3 | Nov 6, 2026 | Ben Okafor | 38.5 | 21.5 | 38.5 | 0 | 827.75 | 81.82 | 745.93 | Deduction 1 (% of gross) | 0.062 | |
| 4 | Nov 6, 2026 | Cara Singh | 44.25 | 26 | 40 | 4.25 | 1205.75 | 110.74 | 1095.01 | Deduction 2 (% of gross) | 0.0145 | |
| 5 | Nov 6, 2026 | Dev Patel | 40 | 19.75 | 40 | 0 | 790 | 78.94 | 711.06 | Fixed deduction ($) | 18.5 | |
| 6 | Nov 13, 2026 | Ana Lopez | 41.5 | 24 | 40 | 1.5 | 1014 | 96.07 | 917.93 | |||
| 7 | Nov 13, 2026 | Ben Okafor | 40 | 21.5 | 40 | 0 | 860 | 84.29 | 775.71 | |||
| 8 | Nov 13, 2026 | Cara Singh | 36 | 26 | 36 | 0 | 936 | 90.1 | 845.9 | |||
| 9 | Nov 13, 2026 | Dev Patel | 43.25 | 19.75 | 40 | 3.25 | 886.28 | 86.3 | 799.98 |
Download this sheet as an Excel practice file (.xlsx)
The register layout
Each row is one employee in one pay period: period end date, name, hours, hourly rate, then the calculated columns. The inputs sit to the side in L1:L5: the overtime threshold, the overtime multiplier, two percentage deductions and one fixed deduction.
The deduction values in L3:L5 are placeholders to show the arithmetic, not real tax or benefit rates. Replace them with the figures your payroll provider, benefits plan or accountant gives you. Many real deductions are not a flat percentage: they have brackets, annual caps, thresholds or depend on filing details. A single rate cell cannot represent those, and that is a job for payroll software or your provider, with Excel used to check and summarize.
One row per person per period (a long list, not one column per week) keeps every total a simple SUMIFS and lets you add a new period by pasting rows underneath.
Regular and overtime hours
Overtime hours are whatever is above the threshold in L1, never below zero: =MAX(0,C2-$L$1). Regular hours are the rest, capped at the threshold: =MIN($L$1,C2). Ana worked 46 hours, so 40 regular and 6 overtime; Ben's 38.5 hours are all regular.
The Hours column must be decimal hours (46, 38.5), not a time like 46:00. If your timesheet holds clock times, convert with x24 and MOD for overnight shifts first: calculate overtime in Excel covers daily limits, double time and combined daily and weekly rules, and the payroll and timesheet formulas hub goes from clock times to weekly hours for a team.
Result: 6
6 overtime hours for Ana in the week ending November 6 (46 - 40).
Gross pay
Regular hours at the rate, plus overtime hours at the rate times the multiplier in L2, rounded to cents because that is what the payslip shows. Ana's week is 40 x $24 + 6 x $36 = $1,176.
Round at the line, not later. Dev's second week (43.25 hours at $19.75) works out to $886.28125 before rounding; the payslip says $886.28, so the register should hold $886.28 and every deduction should be calculated from that figure.
Result: 1176
$1,176.00 gross for Ana: 40 x $24 plus 6 x $36.
Deductions from input cells
Each percentage deduction is gross x its rate cell, rounded to cents on its own, then the fixed amount is added. For Ana that is $72.91 + $17.05 + $18.50 = $108.46. Rounding each deduction separately matches how they appear as separate lines on a payslip.
If you have several deductions, give each its own column rather than one long formula: it is easier to check, and you can total each one by period with SUMIFS for the amounts you pay over to each provider. Some deductions are worked out on gross pay and others on pay after another deduction; the base for each one comes from your provider, so build the formula to match what they tell you.
Result: 108.46
$108.46 total deductions for Ana with the placeholder rates in L3:L5.
Net pay
Net pay is gross minus deductions. Because both were already rounded to cents, the subtraction needs no extra rounding. Ana's net for the week is $1,067.54.
Add a check that the register balances: =SUM(G2:G9)-SUM(H2:H9)-SUM(I2:I9) should be exactly 0. Here gross is $7,695.78, deductions $736.72 and net $6,959.06. If the check is not 0, someone typed over a formula with a number.
Result: 1067.54
$1,067.54 net pay for Ana: $1,176.00 gross less $108.46 deductions.
Totals by employee and by pay period
SUMIFS adds up any column for one person or one period. =SUMIFS(I2:I9,B2:B9,"Ana Lopez") gives Ana's net pay across both weeks, $1,985.47. For one period, compare the date column with a real date: =SUMIFS(G2:G9,A2:A9,DATE(2026,11,13)) returns $3,696.28 gross for the week ending November 13. Put the date or name in a cell and refer to it, so the summary changes with one edit.
For a summary table of every employee, list the names with =UNIQUE(B2:B9) in Excel 365 or 2021 and put the SUMIFS beside it. A PivotTable with Employee in Rows and Gross, Deductions and Net in Values does the same with no formulas. Before you total, check no one was entered twice for a period: =SUMPRODUCT(--(COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)>1)) counts duplicate rows and should be 0.
Result: 1985.47
$1,985.47: Ana's net pay for the two weeks ($1,067.54 + $917.93).
Why the totals can be a cent off
Deduction 2 rounded on each line adds up to $111.58 across the eight rows. Applying the same rate to the total gross, =ROUND(SUM(G2:G9)*L4,2), gives $111.59. Neither is a mistake: rounding eight times and rounding once land on different cents.
Pick one method and use it everywhere. The usual one is to round per employee per period, because that is what each payslip shows, and to make every total a SUM of the rounded lines. If an amount you pay over is calculated on total wages instead, keep that figure in its own cell and expect it to differ from the sum of the lines by a few cents. Excel sum not adding up explains the same effect with displayed decimals.
Result: 111.58
$111.58 from rounding each line, one cent less than the $111.59 you get by rounding the total.
Common mistakes
Typing tax or deduction rates into formulas
Rates change and differ by location and employee. Keep them in input cells, filled from your payroll provider or accountant, and refer to them with $.
Hours as times in the Hours column
46:00 is stored as 1.917 days, so the pay is far too small. Convert clock times to decimal hours with x24 first.
Rounding only the net pay
Round gross and each deduction to cents as you go, as the payslip does. Rounding only at the end gives a net that does not equal gross minus the deduction lines.
One column per pay period
A wide sheet needs new formulas every period. A long list (one row per person per period) keeps totals as SUMIFS and works with PivotTables.
FAQ
What are the basic payroll formulas in Excel?
Regular hours =MIN(threshold,hours), overtime =MAX(0,hours-threshold), gross =ROUND(regular*rate+overtime*rate*multiplier,2), deductions =ROUND(gross*rate,2) for each percentage plus fixed amounts, and net =gross-deductions.
How do I calculate net pay in Excel?
Subtract the total deductions from gross pay: =G2-H2. Calculate each deduction from its rate cell and round it to cents first, so the net matches the payslip.
Can Excel calculate payroll taxes?
Excel can do the arithmetic, but it has no tax tables or rules built in, and real withholding often uses brackets and caps a single rate cannot capture. Get the figures from your payroll provider or an accountant, or use payroll software and use Excel to check and summarize.
How do I total payroll by employee in Excel?
=SUMIFS(net_column,name_column,"Ana Lopez"), or point the criteria at a cell holding the name. Use the same pattern with the period end date to total one pay period.
Why is my payroll total one cent different?
Rounding each line and then adding gives a different cent from rounding the total once. Choose one method, usually per line because payslips show it, and make every total a SUM of the rounded lines.
Functions used
MINSmallest numberMAXLargest numberROUNDRounds to digitsSUMAdds numbersSUMIFSAdds with several conditionsSUMPRODUCTMultiply then addCOUNTIFSCounts with several conditionsDATEBuilds a dateUNIQUERemoves duplicates, spills
Related guides
- Payroll and timesheet formulas in Excel
- How to calculate overtime in Excel
- How to calculate hours worked in Excel
- How to calculate hourly rate from salary in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate schedule adherence in Excel
- How to calculate product return rate in Excel
Updated