Payroll Register Format in Excel: Columns That Map to CPP, EI, and Net Pay
Build a payroll register format in excel with Canadian columns: earnings, CPP, EI, tax, employer shares, net pay, and period totals you can audit.
For the cluster overview, start at the Canadian payroll register template excel pillar. People search payroll register format in excel when they want a column recipe, not a brand pitch. This guide gives a Canadian-ready format: headers you can type today, formulas that balance, and totals that still make sense when T4 season arrives.
Key takeaway: Format the register as a flat table — one employee × one pay period per row — with separate CPP, EI, tax, and employer contribution columns. Flat tables beat nested “pretty” layouts for SUMIFS and year-end mapping.
Recommended column schema (Canadian)
Core register columns
| Column | Example header | Notes |
|---|---|---|
| A | EmployeeID | Stable key to Employee Info |
| B | EmployeeName | Display only; ID is source of truth |
| C | PayPeriodStart | Date |
| D | PayPeriodEnd | Date |
| E | PayDate | Remittance timing |
| F | RegularPay | Or Hours × Rate on helper cols |
| G | OvertimePay | Optional |
| H | VacationPay | Optional; track carefully for T4 |
| I | GrossPay | Sum of earnings |
| J | PensionableEarnings | If different from gross |
| K | InsurableEarnings | If different from gross |
| L | CPP_Employee | From PDOC / tables |
| M | EI_Employee | From PDOC / tables |
| N | IncomeTax | Federal + provincial combined or split |
| O | OtherDeductions | Benefits, RRSP, garnishments |
| P | CPP_Employer | Remittance + expense |
| Q | EI_Employer | Remittance + expense |
| R | NetPay | Gross − employee deductions |
| S | PaymentMethod | EFT / cheque / other |
| T | Notes | Void, adjustment, off-cycle |
This mirrors what ADP and OnPay describe for register contents, with Canadian statutory names instead of FICA.
Formulas that keep the row honest
Wire one register row
- GrossPay = SUM of earnings columns for that row.
- NetPay = GrossPay − CPP_Employee − EI_Employee − IncomeTax − OtherDeductions.
- Period totals = SUM of each money column for the pay date filter.
- Employer remittance for the run = SUM(CPP_Employee + EI_Employee + IncomeTax + CPP_Employer + EI_Employer) for that pay date (confirm your remittance composition with CRA guidance).
- Optional: pull EmployeeName with XLOOKUP from Employee Info using EmployeeID.
Warning: Do not hardcode CPP/EI rates into cells as if they never change. Store the amounts calculated from current CRA tools, or maintain a dated rates sheet you review when CRA updates take effect.
Continuous register vs one sheet per pay period
Continuous register (recommended for small teams): add rows forever; filter by PayDate; YTD = SUMIFS. One sheet per period: clearer printing; harder T4 rollups unless you consolidate.
HR Insider recommends entering each finalized register’s totals into a year file — same discipline whether your operational register is continuous or per period.
Formatting hygiene
- Freeze header row; use Excel Tables for structured references.
- Data Validation on EmployeeID from the Employee Info list.
- Conditional format NetPay < 0 (should never happen for normal runs).
- Archive a PDF/CSV of each finalized run before edits.
- Never delete voided rows — reverse with negative adjustments and a Notes flag.
Link to a ready workbook
If you would rather start from labeled sheets than blank columns, use the PlanoraNest Canadian payroll register template and adapt field names to your pay codes. Sibling guides: payroll register template excel, canadian payroll cpp ei excel.
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.
FAQ
What is the standard payroll register format in Excel?
There is no single CRA-mandated spreadsheet layout. Best practice is one row per employee per pay period with earnings, each deduction, employer contributions, and net pay, plus period totals.
Which columns matter most for Canada?
Gross (and earnings splits if needed), CPP employee, EI employee, income tax, other deductions, employer CPP, employer EI, and net pay — with pay period dates and employee ID.
Should I put each pay run on a new sheet?
Either a continuous register with a Pay Date column or one sheet per period can work. Continuous registers are easier to SUMIFS for YTD and T4.
How do I stop people editing formulas?
Protect formula cells, color-code inputs, and document rules on a Start Here sheet.
Where can I get a pre-formatted Canadian register?
Use a structured workbook such as PlanoNest’s Canadian payroll register template — one-time purchase, instant download; see the product page for pricing.
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.
