How to Extract UK P60 Data into Excel
for Payroll Reconciliation (2026 Guide)

What's on a P60 — and Why Each Field Matters for Your Spreadsheet

The P60, formally the End of Year Certificate, is a statutory document governed by HM Revenue and Customs (HMRC). Its content is not a suggestion from payroll software designers — it is prescribed by law under HMRC specification RD1, which defines every field every substitute P60 layout must carry for a given tax year. The tax year runs from 6 April to 5 April, and the fields on the form cover the full twelve months of that period for each employee.

Understanding what each statutory field is for — rather than just knowing its name — is what determines whether your extracted spreadsheet passes reconciliation against your Full Payment Submission (FPS) data on the first pass or generates a afternoon of cross-referencing. Here are the fields that matter for most payroll reconciliation work, grouped by their downstream function:

The practical consequence of this field set is that a P60 extraction spreadsheet for 150 employees produces 150 rows and roughly 20 to 25 columns depending on how granular you go with NI band breakdowns. Manual entry across that grid — locating each box on each certificate, typing the value, double-checking the NI letter — is where the five hours go. AI-powered document extraction removes the locating-and-typing step by reading the certificate semantically rather than relying on pixel coordinates.

Why the Same P60 Data Looks Different Across Payroll Software

If every P60 looked identical — same box positions, same label placement, same font — extraction would be a solved problem with any template-based OCR tool. But HMRC's RD1 specification explicitly permits "variations in format and layout" for substitute forms, and every major payroll software provider exercises that permission differently.

Sage Payroll might print the employee NINO in the top-right quadrant with the PAYE reference in a separate block below. Xero Payroll might place them side by side. BrightPay might use a three-column grid. IRIS Staffology might stack everything in a single vertical column. The paper size, ink colour, and box arrangement are all at the employer's or software provider's discretion — the only constraint is that all statutory fields must appear on one sheet of paper.

This is not a bug in the specification. It exists because employers have used different payroll software for decades, each with its own printing engine, and HMRC's approach is to mandate the data content rather than the visual layout. The result for anyone doing extraction is that every payroll provider's P60 is a layout variant of the same underlying data schema — and a template-based extraction tool that trained on Sage's layout will fail on Xero's.

The NI category letter section makes this visible in a way that costs real time. When an employee's NI category letter changed mid-year — for example, from A to C upon reaching State Pension age — the P60 must show two separate NI rows under different category letters. Sage might print these as two adjacent rows with letter labels on the left. Xero might print them as separate table sections with the letter as a section header. A template looking for "a row with an NI letter in column 1" catches one format and misses the other. Semantic extraction — reading by meaning rather than by position — handles both layouts because it understands that "NI category letter" is a column value regardless of its visual presentation.

Setting Up Your P60 Extraction Workflow

The workflow that replaces manual P60 transcription has three steps, and the configuration step — defining your columns — is what you do once and reuse for every tax year, every payroll provider, and every batch of employees.

This workflow works for any number of employees and any mix of payroll providers. The column definition is reusable across tax years because HMRC's statutory field set changes only when legislation changes — and when it does (as with the addition of Statutory Neonatal Care Pay in the 2025-26 specification), you add the new column to your definition without rebuilding the rest.

Three P60 Extraction Workflows That Pay for Themselves Every Year-End

Most P60 extraction falls into one of three patterns, each with its own batch shape and output emphasis. Matching your workflow to the right output structure is what turns a generic "extract data" tool into something your team actually uses every May.

Self Assessment Preparation (March–May Window)

An accounting practice serving individual clients receives P60s alongside bank statements, dividend vouchers, and P11D forms in the run-up to the 31 January Self Assessment filing deadline. Clients with multiple concurrent employments produce two or more P60 rows for the same tax year. The headline columns for this workflow are Total Pay for Year, Tax Deducted, NINO, and Employer PAYE Reference — these map directly to the Employment pages of the SA100 tax return. A client with two part-time jobs generates two rows, and the "Total for Year" column on each P60 gives the figures that need to be entered separately on the return.

This is the highest-volume window for P60 extraction because it is when practices handle the largest number of client documents in the shortest time. Replacing manual transcription here does not just save hours — it eliminates the most common source of SA302 inquiry triggers: transcription errors where a pay figure from one P60 was typed into the wrong employment line.

Payroll Bureau Reconciliation Against FPS Submissions

A payroll bureau running year-end for multiple employer clients needs to confirm that the figures on each employee's P60 match the year-end Full Payment Submission (FPS) totals sent to HMRC. The reconciliation runs employer by employer: extract all P60s for Employer A into a spreadsheet, then diff the pay and tax totals against the bureau's own FPS extract for that employer. Column alignment is what makes the diff meaningful — if the P60 "Pay in This Employment" column sits next to the FPS "Total Pay" column, the variance formula is a single subtraction per row.

The NI category letter column is especially important in this workflow. An employee whose letter changed from A to C mid-year will show two NI rows on the P60 under different letters. The bureau's reconciliation needs both rows to verify that total contributions match the FPS — and a single "total NI" column that collapsed both rows into one number would hide a category-letter mismatch that HMRC may flag months later.

Income Verification at Scale

Mortgage providers, letting agents, employment-screening firms, and immigration advisers routinely request P60s as proof of prior-year income. The verification workflow is high-volume and narrow-field: Name, NINO, Employer PAYE Reference, Total Pay for Year. The other statutory fields — statutory payments, student loan deductions, NI band breakdowns — stay in the output as reference data, but the verification decision turns on the pay figure and the employer reference that ties it to a real entity.

Because this workflow often involves P60s from unfamiliar payroll providers — an applicant might bring a P60 from an employer using a niche payroll system the verifier has never seen — the extraction tool's ability to handle any layout without pre-configuration is what determines whether the verification pipeline can be automated or whether someone has to open each PDF and type the figure by hand.

Validating Your Extracted P60 Data Before It Hits the Payroll Spreadsheet

Even at high extraction accuracy, the operator owes the downstream reconciliation or verification check a sanity pass. The checks below are P60-specific and run column by column in Excel — none require auditing every field on every row. They are shape checks designed to surface the handful of rows worth eyeballing against the source P60.

The reason this checklist is tractable — rather than aspirational — is that the extracted data is already structured in columns. Each row carries the source file reference, so any flagged row is a click away from the original P60. Manual transcription would never sustain column-level checks like these at firm scale, and extraction is what makes the validation pass possible at all.

P60 vs W-2: What UK and US Teams Need to Know

UK payroll teams encountering US tax form extraction tools for the first time — or US firms with UK subsidiaries — often ask whether a P60 is essentially a UK W-2. The short answer is that they serve the same function (year-end employee earnings certificate) but differ in structure, field set, and downstream use in ways that matter for extraction setup.

A W-2 reports federal wages (Box 1), Social Security wages (Box 3), Medicare wages (Box 5), and state-level breakdowns across 20 numbered boxes — all for a calendar tax year (January 1–December 31). A P60 reports taxable pay and PAYE tax for a tax year running 6 April to 5 April, with National Insurance shown by category letter and earnings band rather than as a flat percentage deduction. A W-2 has no concept of NI category letters, no statutory payment breakdown, and no student loan plan differentiation. A P60 has no state-level reporting and no Social Security/Medicare split.

The extraction implication is that the two form types need different column definitions. A W-2 column set works for W-2s across all US employers; a P60 column set works for P60s across all UK payroll providers. But the two column sets do not overlap beyond the identity fields — and treating a P60 as a W-2 with different box numbers produces a spreadsheet that needs extensive post-extraction reformatting.

If your firm handles both, see our complete guide to W-2 and 1099 tax form extraction for the US-side workflow, and use a separate column definition for each form type. The batch processing approach is the same — upload files, define columns, export spreadsheet — but the column names are market-specific.

FAQ