T4 Payroll Register Excel: Balance Year-End Boxes From Your Pay Runs
Use a t4 payroll register excel method: map register columns to T4 boxes, cross-foot YTD, and reconcile remittances before you file slips.
Year-end balancing assumes you already keep a Canadian payroll register during the year. February is a terrible time to invent history. A t4 payroll register excel workflow means your period register was complete enough that Box totals are a SUM — not a scavenger hunt through banking apps.
Key takeaway: Before you fill T4 slips, cross-foot register YTD totals to remittance history and to draft Box 14/16/18/22 figures. Fix the register first; then file.
Why the register is the T4 source of truth
The CRA T4 slip page defines what employers report. HR Insider’s Excel balancing guide spells out the operational method: keep a Register sheet of each pay run, map columns into a T4 sheet, and reconcile to PD7A-style remittance totals and GL wage expense. Custom CPA’s year-end checklist likewise tells teams to compare payroll register totals to ledger and CRA remittances monthly — not only in February.
Avalon’s free payroll template includes a T4 Summary tab for owner-managers for the same reason: if the year was entered cleanly, filing is transcription plus review.
Practical box mapping from register columns
Register column → T4 box (typical)
| Register total (per employee YTD) | Typical T4 box | Notes |
|---|---|---|
| Gross / employment income | Box 14 | Watch taxable benefits not in gross |
| CPP employee | Box 16 | Employer CPP is separate |
| EI employee | Box 18 | Employer EI is separate |
| Income tax deducted | Box 22 | Combined federal/provincial withheld |
| Other codes | Other Information | Depends on benefits / RPP / etc. |
Always verify against current CRA instructions — boxes and “other information” codes change over time.
Year-end balance in Excel
- Freeze the year’s register (archive a copy).
- SUMIFS each money column by EmployeeID for the calendar year.
- Build a T4 working sheet with one row per employee and formulas pointing at those sums (HR Insider example:
=SUM(Register!B2:H2)style mapping). - Compare employee CPP/EI/tax YTD to remittance records.
- Compare wage expense in the GL to gross + employer CPP/EI as applicable.
- Only then enter slips in CRA Web Forms or your filing method and distribute employee copies.
Warning: Do not “fix” T4 boxes to match a guess. If numbers disagree, find the missing register row, void, or remittance — adjustments without audit trails create the next year’s problem.
Off-cycle runs and voids
Off-cycle bonuses, vacation payouts, and voided deposits are where T4s break. Keep them on the register with Notes. If YTDs were adjusted outside a register row, add a Voided/Adjustment sheet so T4 formulas still see them (HR Insider).
Cluster links and product CTA
- Deduction workflow: canadian payroll cpp ei excel
- Format: payroll register format in excel
- Structured sheets: Canadian payroll register template (one-time purchase; see product page for pricing)
Field notes from Canadian payroll register cycles
After every pay run, lock a dated copy of the register totals before you change rates or claim codes. Mid-year TD1 updates and CPP/EI exemption flags are common — if you overwrite the live row without an archive, February T4 reconciling becomes guesswork.
Keep employer CPP and EI in their own columns. Remittance math and general-ledger wage expense need those amounts; stuffing them into a single “taxes” cell forces you to re-split later. When finance asks “what did we remit last month?”, you should filter the register by pay dates in that remittance period and sum — not rebuild from memory.
Keep pensionable and insurable earnings honest
Gross pay is not always the same as CPP pensionable earnings or EI insurable earnings. Record both when your pay codes include non-pensionable or non-insurable amounts. The CRA Payroll Deductions Online Calculator and T4032 tables expect the right bases — your register should too.
If you only store net pay, you cannot explain a CRA notice or a T4 mismatch. Earnings bases are part of the audit story, not optional metadata.
When to reopen the Start Here sheet
Reopen onboarding notes when you add a province of employment, change pay frequency (weekly vs biweekly vs semi-monthly), or hire someone with CPP or EI exemption status. Those three inputs change almost every deduction line. A five-minute Start Here review beats a month of wrong remittances.
Also reopen Start Here after CRA annual rate updates take effect, and after anyone on the team “helps” by typing over a gray formula cell. Document which cells are inputs in one place so the next person does not invent a second process.
Remittance and bank proof habits
After you pay employees, keep the register row IDs (or pay date + employee ID) in your bank memo or payment export when possible. Matching bank outflows to register net pay is how you catch a duplicate EFT or a missed cheque before month close.
Build a simple remittance checklist: filter register → sum employee deductions + employer CPP/EI → compare to the amount you will send CRA → file the confirmation PDF beside that month’s register archive. This habit is boring — and it is what makes T4 season short.
What this workbook is not
A Canadian payroll register is not a personal expense tracker, not a US Form W-2 worksheet, and not a full HRIS. It will not replace advice from a CPA or payroll professional when you have multi-province complexity, union dues, or unusual taxable benefits. Use the register to keep Canadian pay-run facts tidy; escalate edge cases instead of hiding them in a Notes cell nobody reads.
Field notes from Canadian payroll register cycles
After every pay run, lock a dated copy of the register totals before you change rates or claim codes. Mid-year TD1 updates and CPP/EI exemption flags are common — if you overwrite the live row without an archive, February T4 reconciling becomes guesswork.
Keep employer CPP and EI in their own columns. Remittance math and general-ledger wage expense need those amounts; stuffing them into a single “taxes” cell forces you to re-split later. When finance asks “what did we remit last month?”, you should filter the register by pay dates in that remittance period and sum — not rebuild from memory.
FAQ
How does a payroll register help with T4s?
YTD sums from register columns for employment income, CPP, EI, and tax deducted become the starting point for T4 boxes. Without a clean register, you rebuild history from bank stubs.
Which T4 boxes map from a typical register?
Common mappings include Box 14 (employment income), Box 16 (CPP contributions), Box 18 (EI premiums), and Box 22 (income tax deducted). Confirm current box rules on the CRA T4 page.
When are T4s due?
Employers generally must file T4 slips and the T4 Summary by the last day of February following the calendar year — confirm current CRA deadlines.
What if register totals disagree with CRA remittances?
Stop filing and reconcile. Missing off-cycle runs, voids, or remittances allocated to the wrong period are common causes.
Can a template generate official T4 PDFs?
Most Excel registers help you assemble box totals; you still file through CRA channels. Check what your specific workbook claims.
Related reading in this cluster
Stay inside one Canadian payroll register workflow: start from the pillar guide, evaluate sheets with payroll register template excel, set columns via payroll register format in excel, run deductions with canadian payroll cpp ei excel, balance year-end using t4 payroll register excel, and connect master data in employee payroll register excel.
