Build a Simple Payroll Calculator in Excel

Simple payroll in Excel takes hours and rates and turns them into gross pay, deductions and net pay for each person. One table holds the employees, a second table holds the rates and bands, and VLOOKUP and ROUND connect them. This guide builds a weekly payroll sheet for a small team. The tax bands are made-up examples to show the method: always use the official rates for your country.

How the payroll sheet is laid out#

Keep employee data on the left and the rules on the right. The finished sheet looks like this:

And the withholding table in H1:I5:

Only hours, rates and the band table are typed in. Gross, tax and net are formulas.

Step 1: Calculate gross pay with overtime#

Gross pay is regular hours at the normal rate plus overtime hours at a higher rate. This example pays 1.5 times the rate for hours over 40 in a week. Your rules may differ.

Split the hours first. Regular hours are the smaller of the hours worked and 40, and overtime is anything above 40:

=MIN(B2,40)
=MAX(0,B2-40)

Then combine them in D2:

=ROUND(MIN(B2,40)*C2+MAX(0,B2-40)*C2*1.5,2)

For Ben, that's 40 × 18 = 720 plus 4 × 27 = 108, so 828.00. For Ana, who worked 38 hours, the overtime part is 0 and gross is 855.00. Fill the formula down with Ctrl+D (Mac: ⌘D). The guide to calculating hours worked and overtime covers turning start and end times into hours.

Step 2: Look up the tax rate with VLOOKUP approximate match#

A band table lists the lower limit of each band in the first column, sorted from smallest to largest. VLOOKUP with TRUE as its last argument finds the largest value that is not bigger than the pay, which is exactly the band the pay falls in.

In E2:

=ROUND(D2*VLOOKUP(D2,$H$2:$I$5,2,TRUE),2)

How it works for Chen's 1,240.00:

1. VLOOKUP looks down H2:H5 for 1,240. There's no exact match.
2. The largest value not above 1,240 is 1,000, so it uses that row.
3. It returns the rate from column 2 of the table: 15%.
4. 1,240 × 15% = 186.00, and ROUND keeps it to cents.

The $ signs lock the band table so it stays put when you fill down. The first column must be sorted ascending, or VLOOKUP returns wrong rates without any warning. Practise VLOOKUP approximate match to see how it walks down a sorted table, and read the VLOOKUP function guide for every argument.

Step 3: Calculate net pay#

Net pay is gross pay minus every deduction. With only tax, F2 is:

=D2-E2

Ana takes home 769.50. If you have other deductions, such as a pension or benefit contribution, give each its own column and subtract them all: =D2-SUM(E2:G2). Keep each deduction's rate in a cell next to the band table rather than typing it into the formula.

Step 4: Round every money result#

Round each money result to two decimals at the step where you calculate it. Payroll deals in cents, and unrounded values can hide fractions that add up to a visible difference in the totals.

The rule of thumb:

• Round gross, tax and each deduction with ROUND(..., 2).
• Don't round net pay separately. It's the difference of rounded numbers, so it's already in cents.
• Don't rely on cell formatting. Formatting only changes what you see; the full value stays in the cell and flows into totals.

The ROUND function guide and the rounding lesson explain ROUND, ROUNDUP and ROUNDDOWN. Some rules round tax down rather than to the nearest cent, so check yours.

Step 5: Handle progressive tax bands#

Many real tax systems are progressive: each rate applies only to the part of pay inside its band, not to the whole amount. The flat-band lookup above is simpler, but it can make a small pay rise reduce take-home pay. For a progressive system, add a Base column with the tax due at the start of each band.

Tax is the base for the band plus the rate times the amount above the band's start. If this table is in H2:J5:

=ROUND(VLOOKUP(D2,$H$2:$J$5,2,TRUE)+(D2-VLOOKUP(D2,$H$2:$J$5,1,TRUE))*VLOOKUP(D2,$H$2:$J$5,3,TRUE),2)

For 1,240.00, that's 50 + (1,240 − 1,000) × 20% = 98.00. Each VLOOKUP finds the same row and returns a different column.

Step 6: Add totals and checks#

Totals help you check the payroll before anyone is paid. Under the table, total each money column:

=SUM(D2:D5)

Gross is 4,216.60, tax 548.34 and net 3,668.26. A quick check is =SUM(D2:D5)-SUM(E2:E5)-SUM(F2:F5), which must be 0. If it isn't, a formula wasn't filled down.

To show decimal hours as hours and minutes on payslips, INT and MOD split 7.75 into 7 hours and 45 minutes; see the guide to MOD and INT in Excel. For sales staff paid partly on results, the commission calculator in Excel uses the same VLOOKUP band method. And for another money model with ROUND on every row, see the loan amortization schedule.

Practise it#

Try the exercise on this page: look up a band rate with VLOOKUP and TRUE, then round the tax. Next, practise VLOOKUP approximate match in a live spreadsheet with band tables like this one, and practise ROUND, ROUNDUP and ROUNDDOWN so every payroll line comes out to the cent.