Payroll reconciliation template
By the Analistable team · Updated · 3 min read
A free workbook with a Payroll register sheet (gross, deductions and net pay per employee, with a gross-to-net check), a Bank payments sheet and a Reconciliation sheet that matches net pay to amounts paid by employee ID. It flags underpayments, overpayments, missing payments, duplicate payments and payees who aren't on the payroll.
Part of our guide: How to reconcile data in Excel
What the template does
A payroll reconciliation answers two questions: does each payslip add up, and did the bank pay exactly what the payroll said? This template checks both, per employee, so a single wrong payment can't hide inside a total that happens to look right. It is part of the reconciling data in Excel guide; if you also need to check hours before pay is run, start with reconciling payroll with timesheets.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Payroll register | Employee ID, Name, Gross pay, Income tax, Employee NI, Pension, Net pay, Net check | First seven columns |
| Bank payments | Employee ID, Payee, Amount paid, Payment date, On payroll? | First four columns |
| Reconciliation | Employee ID, Name, Net pay, Paid, Difference, Payments, Status | No — calculated |
How to fill it in
- Export the payroll register for the pay run and paste it in. Rename deduction columns to match your country (e.g. PRSI or Social Security instead of NI).
- Export the payment batch or bank statement lines for the run. Add the employee ID to each line — most payment files carry it in the reference.
- Any Net check other than 0 means a payslip doesn't add up.
- Work through every Reconciliation row that isn't 'Matched', plus any bank line marked 'Not on payroll'.
How the formulas work
Gross-to-net check on the register; it should always be zero:
=ROUND(C2-D2-E2-F2-G2,2)Paid and number of payments per employee:
=SUMIFS('Bank payments'!$C$2:$C$300,'Bank payments'!$A$2:$A$300,A2)Status checks for missing and duplicate payments before comparing amounts:
=IF(F2=0,"Not paid",IF(F2>1,"Paid more than once",IF(E2=0,"Matched",IF(E2>0,"Overpaid","Underpaid"))))Worked example
| Employee | Net pay | Paid | Difference | Payments | Status |
|---|---|---|---|---|---|
| E01 Alice Grant | 2660 | 2660 | 0 | 1 | Matched |
| E02 Ben Hale | 2191 | 2191 | 0 | 1 | Matched |
| E03 Cara Imes | 3129 | 3129 | 0 | 1 | Matched |
| E04 Dev Joshi | 2065 | 2065 | 0 | 1 | Matched |
| E05 Ella King | 2392 | 2292 | -100 | 1 | Underpaid |
| E06 Femi Lowe | 1830.5 | 0 | -1830.5 | 0 | Not paid |
| E07 Gus Moss | 2932 | 5864 | 2932 | 2 | Paid more than once |
| Total | 17199.5 | 18201 | 1001.5 |
Below the table, the template shows 1,500 paid to a payee who isn't on the register, so the bank paid 19,701 in total against 17,199.50 of net pay. Four problems — a short payment, a missed employee, a duplicate and an unknown payee — would all be invisible if you only compared the two totals.
Limits and when to move on
The template covers net pay. Tax and pension liabilities need their own check against the amounts paid to the tax authority and pension provider, the same way a bank reconciliation against the ledger works. For many pay runs or entities, load the register and bank exports into Analistable and ask 'employees paid a different amount from their net pay this quarter' — payroll data stays in your browser, which matters for files this sensitive.
Frequently asked questions
- What if the bank file has names but no employee IDs?
- Add the ID by hand or with an INDEX/MATCH on name from the register. Names are a weak key, so check any row the lookup can't find.
- Should I reconcile gross pay to the ledger too?
- Yes, at month end: total gross pay and employer costs on the register should match the payroll expense journals in your accounts.
- Is it safe to keep payroll in a spreadsheet?
- Only with restricted access. Store the workbook where only payroll staff can open it, and remove bank details you don't need for the check.