Automate Payroll Reconciliation in Excel with Claude AI
Progress1 of 4
1
The finished reconciliation
2
Prepare your data
3
Prompt, FAQ & related
4
Quick quiz
Section 01
What the finished payroll reconciliation looks like
Automate Payroll Reconciliation in Excel with Claude AI
This course gives you a Claude prompt that takes your payroll export and your general ledger extract and produces a line-by-line reconciliation — matched items confirmed, variances flagged with a root cause, and a summary ready for the month-end close pack. No VLOOKUP nesting required.
The Finished Payroll Reconciliation Output
Below is the reconciliation table Claude produces, grouped by cost centre. Each row compares what the payroll system reported against what the GL recorded. Green means the two match within tolerance. Red means a variance needs investigating before you can close the period.
A
B
C
D
E
F
1
Cost Centre
GL Account
Payroll Total
GL Posted
Variance
Status
2
Sales – UK
6100
84,200.00
84,200.00
0.00
MATCH
3
Finance
6100
31,450.00
31,450.00
0.00
MATCH
4
Operations
6100
57,800.00
55,300.00
-2,500.00
VARIANCE
5
Marketing
6100
22,100.00
22,100.00
0.00
MATCH
6
Employer NIC
6200
19,460.00
21,960.00
+2,500.00
VARIANCE
7
Pension
6300
8,750.00
8,750.00
0.00
MATCH
Operations gross pay is £2,500 below the GL posting — the equal and opposite variance in Employer NIC (6200) suggests a miscoding between accounts, not a missing payment.
Cost-centre-level comparison of payroll system totals against GL postings
Automatic variance calculation with equal-and-opposite pattern detection (likely GL miscoding)
Separate rows for gross pay, employer taxes, and pension so each component reconciles independently
A summary section showing total payroll cost, total GL postings, and net variance for sign-off
Fits naturally with: Payroll reconciliation is a core step in any month-end close process. Once your numbers are clean, the bank reconciliation course shows how to confirm the net pay transfer cleared your account — completing the payroll audit trail.
Prepare Your Payroll and GL Data
Claude needs two exports: one from your payroll system and one from your accounting system. The columns don't need to be identical — Claude will match on cost centre and GL account code — but both files need those two identifiers present.
Payroll System Export
A
B
C
D
E
1
Employee ID
Cost Centre
GL Account
Pay Type
Amount
2
EMP-0041
Operations
6100
Gross Pay
4,800.00
3
EMP-0041
Operations
6200
Employer NIC
531.20
4
EMP-0041
Operations
6300
Pension
240.00
5
EMP-0057
Finance
6100
Gross Pay
5,600.00
General Ledger Extract
A
B
C
D
E
1
Journal Ref
Cost Centre
GL Account
Description
Amount
2
JNL-2025-06-44
Operations
6100
June payroll
55,300.00
3
JNL-2025-06-44
Operations
6200
June payroll – NIC
21,960.00
4
JNL-2025-06-44
Finance
6100
June payroll
31,450.00
01
Aggregate payroll to cost-centre level before pasting
The payroll export has one row per employee per pay type. Sum these to cost-centre level yourself before running the prompt — for example, sum all 6100 entries for Operations across every employee. Claude will reconcile at that aggregated level. If you want employee-level detail, adjust the grouping instruction in the prompt accordingly.
02
Make sure GL account codes match between datasets
If your payroll system calls the account "6100-WAGES" and your GL calls it "6100", the reconciliation will fail on that row. Standardise to the GL code format before pasting. Alternatively, include a mapping table in the prompt — see the mapping section of the prompt below.
03
Flag off-cycle runs with a Pay Type of "Off-cycle"
Bonus payments, termination payouts, and correction runs processed outside the normal pay schedule are easy to miss. Tag them in the Pay Type column as "Off-cycle" — the prompt tells Claude to list them separately so they're visible at review without needing a separate reconciliation. This is especially useful when preparing for a bank reconciliation, where off-cycle runs can cause the net pay transfer to look out of sequence.
Tip: If you use tiered payroll structures — different NIC thresholds or pension contribution rates by salary band — the IF and IFS formulas course shows how to model those conditions in Excel so you can cross-check Claude's groupings against your own calculated totals before running the reconciliation.
The Exact Claude Prompt for Payroll Reconciliation
Copy this prompt into Claude. Replace the bracketed sections with your payroll and GL data. The tolerance setting lets you define what counts as a genuine variance versus a rounding difference — set it to £0.50 for most payroll runs.
Prompt — paste into Claude
I need to reconcile my payroll run against the general ledger for [Month / Period]. Below are my two datasets.
PAYROLL SYSTEM TOTALS (aggregated by Cost Centre and GL Account):
[Paste your payroll data here — columns: Cost Centre | GL Account | Pay Type | Amount]
GENERAL LEDGER POSTINGS (for the same period):
[Paste your GL extract here — columns: Journal Ref | Cost Centre | GL Account | Description | Amount]
GL ACCOUNT CODE MAPPING (include only if your payroll system uses different codes from your GL):
[e.g. "6100-WAGES" in payroll = "6100" in GL; leave blank if codes match]
RECONCILIATION SETTINGS:
- Tolerance: £[e.g. 0.50] — differences within this amount should be marked MATCH, not VARIANCE
- Group by: Cost Centre and GL Account
- Period: [e.g. June 2025]
Rules:
- Match payroll totals to GL postings by Cost Centre AND GL Account. Do not match across accounts.
- Calculate Variance as: GL Posted minus Payroll Total. A negative number means the GL is understated.
- If a payroll line has no matching GL entry, mark it as MISSING FROM GL.
- If a GL entry has no matching payroll line, mark it as UNMATCHED GL ENTRY.
- Flag equal-and-opposite variances (e.g. GL account 6100 is -£2,500 and GL account 6200 is +£2,500) as a probable GL miscoding, not two separate issues.
- List all off-cycle pay items in a separate section at the bottom.
- Do NOT invent figures or infer what a correct amount should be. Flag variances exactly as the data shows them.
Output:
1. Reconciliation table: Cost Centre | GL Account | Pay Type | Payroll Total | GL Posted | Variance | Status
2. Equal-and-opposite variance summary (if any)
3. Off-cycle items list
4. Sign-off summary: Total payroll cost | Total GL postings | Net variance | Number of unreconciled items
Why Payroll Reconciliation Catches What Audit Trails Miss
Most payroll systems produce a detailed audit trail — who was paid, how much, when. Most GL systems record the journal entries. What neither system does automatically is compare the two. A payroll run for £57,800 in Operations can sit in the GL as £55,300 for an entire quarter if nobody runs the reconciliation. The £2,500 didn't disappear — it got coded to Employer NIC (account 6200) instead of Gross Pay (account 6100). Financially neutral at the total level, but wrong at the line level, and a problem during any statutory reporting or audit.
The equal-and-opposite check in the prompt above is the fastest way to find miscoding. When one account is short by exactly the same amount another is over, the total wages bill is correct — but the expense analysis by type is not. That distinction matters when you're reporting on headcount costs versus on-costs to the board.
Common Variance Causes
A
B
C
1
Variance Type
Typical Cause
How Claude Flags It
2
GL understated vs payroll
Journal posted in wrong period
VARIANCE with negative amount
3
Equal-and-opposite across accounts
Employer cost coded to wrong GL account
Flagged as PROBABLE MISCODING
4
Payroll line with no GL match
Off-cycle run not journalised
MISSING FROM GL
5
GL entry with no payroll match
Manual adjustment not in payroll system
UNMATCHED GL ENTRY
Frequently Asked Questions
What is payroll reconciliation and why does it matter for month-end close?
Payroll reconciliation is the process of verifying that total payroll costs recorded in your general ledger match what was actually paid through your payroll system — including gross pay, employer taxes, and benefits. Without it, the salary line on your P&L can carry errors for weeks before anyone notices.
Can Claude reconcile payroll across multiple pay frequencies in one pass?
Yes. If you run weekly payroll for hourly workers and monthly payroll for salaried staff, include both datasets and specify which cost centre or GL account each feeds into. Claude will reconcile them separately and then produce a combined total for comparison against your GL.
What causes most payroll-to-GL variances?
The three most common causes are: a payroll run posted to the wrong accounting period (timing difference); employer tax or benefit costs coded to the wrong GL account; and a manual journal that adjusted gross pay without a matching payroll entry. The reconciliation template surfaces all three.
Does Claude work with exports from ADP, Workday, or SAP?
Claude works with whatever data you paste into the prompt or upload as a file. Most payroll and ERP systems export to CSV or Excel. Clean the export to match the column layout shown in this course and the prompt handles the rest.
How do I handle mid-month pay adjustments or off-cycle runs?
Include them as separate rows in your payroll extract with a Pay Type of "Off-cycle". The prompt instructs Claude to list them in a dedicated section at the bottom of the output so reviewers can see them without searching through the main table.
Can I use this reconciliation to check individual employee records?
The prompt is designed for cost-centre or department-level reconciliation. For employee-level checking, change the grouping instruction in the prompt from "by Cost Centre and GL Account" to "by Employee ID and GL Account". Everything else remains the same.