Written with AI assistance.
If you pay a handful of people, you don't need payroll software to stay organised. You need one register with a row per employee per month, and formulas that work out gross pay, deductions and net pay. This guide builds that register in Excel, so you type each number once and can print a payslip for anyone.
Note: deductions, contributions and reporting duties depend on where you operate. This article shows spreadsheet mechanics only, with rates you enter yourself. It is not tax, payroll, legal or accounting advice; check the rules with a qualified adviser.
Sheet 1: Employees
| Col | Field |
|---|---|
| A | Emp ID |
| B | Name |
| C | Job title |
| D | Start date |
| E | Monthly base pay |
| F | Fixed allowances |
| G | Deduction 1 % (for example tax withholding) |
| H | Deduction 2 % (for example pension) |
| I | Fixed deduction per month |
| J | Employer contribution % (optional) |
You enter the rates that apply to you; nothing is preset. Keep only what you need. Full bank and ID numbers don't belong in a spreadsheet you email around.
Sheet 2: Register (one row per employee per month)
| Col | Field |
|---|---|
| A | Month (first day, e.g. 2026-10-01) |
| B | Emp ID |
| C | Days in month |
| D | Days paid |
| E | Base pay |
| F | Allowances |
| G | Other earnings |
| H | Gross |
| I | Deduction 1 |
| J | Deduction 2 |
| K | Fixed deduction |
| L | Other deductions |
| M | Total deductions |
| N | Net pay |
Days in month (C2): =DAY(EOMONTH(A2,0)). If you pay on working days (Monday to Friday), use =NETWORKDAYS(A2,EOMONTH(A2,0)) instead.
Pro-rated base pay (E2), for someone who started mid-month or took unpaid leave:
=IF($B2="","",ROUND(INDEX(Employees!$E:$E,MATCH($B2,Employees!$A:$A,0))*D2/C2,2))
Copy the same pattern for allowances (column F of Employees).
Gross, deductions, net:
H2: =IF($B2="","",E2+F2+N(G2))
I2: =IF($B2="","",ROUND(H2*INDEX(Employees!$G:$G,MATCH($B2,Employees!$A:$A,0)),2))
J2: =IF($B2="","",ROUND(H2*INDEX(Employees!$H:$H,MATCH($B2,Employees!$A:$A,0)),2))
K2: =IF($B2="","",INDEX(Employees!$I:$I,MATCH($B2,Employees!$A:$A,0)))
M2: =IF($B2="","",I2+J2+K2+N(L2))
N2: =IF($B2="","",H2-M2)
The percentage deductions here are taken from gross pay. If a rule you follow works on a different base, change the formula to match it. Keeping the rates in the Employees sheet means that when one changes, you update one cell, not a formula.
Quick check: base pay 4,200 plus allowances 300 for a full month is a gross of 4,500. At a 15% deduction 1 that is 675. Half a month (15 of 30 days) gives a gross of 2,250 and a deduction of 337.50.
Add checks
-
Days paid can't exceed days in month. Select column D, Data > Data Validation, whole number between 0 and
=C2. - Duplicates. A check column catches the same person and month entered twice:
=IF(COUNTIFS($A:$A,A2,$B:$B,B2)>1,"Duplicate","OK")
-
Unknown IDs. Wrap the lookups in
IFERROR(...,0)and add a Name column that shows"Check ID"when the ID isn't in the Employees list.
A monthly summary without a pivot table
On a Summary sheet, list months down column A and use SUMIFS:
Gross: =SUMIFS(Register!$H:$H,Register!$A:$A,A2)
Deductions: =SUMIFS(Register!$M:$M,Register!$A:$A,A2)
Net pay: =SUMIFS(Register!$N:$N,Register!$A:$A,A2)
For a year-to-date total, add a start and end date:
=SUMIFS(Register!$N:$N,Register!$A:$A,">="&DATE(2026,1,1),Register!$A:$A,"<="&DATE(2026,12,1))
Overtime and one-off payments
Bonuses, overtime and reimbursements don't belong in the Employees sheet because they change each month. Put them in the Register's Other earnings column and include them in gross, as above. Label them clearly on the payslip so the employee can see where the extra amount came from.
Employer cost (optional)
If you track an employer contribution, add one more column: =ROUND(H2*INDEX(Employees!$J:$J,MATCH($B2,Employees!$A:$A,0)),2). Total payroll cost for the month is then gross plus that column.
Common mistakes
- Overwriting last month's rows. Add new rows each month; the register is your history.
- Rates hard-coded in formulas. Keep them in the Employees sheet.
- Duplicate rows for the same person and month. Use the check column.
- Sharing the whole file. Send each person a PDF of their own slip, not the register.
Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.
Practical spreadsheet templates for small businesses, made with AI assistance.
Top comments (0)