# How do I perform an account reconciliation in a spreadsheet?

Applies to: United States · Updated 2026-09-26

Name the independent record the balance should agree with. Build a sheet that takes both balances from their sources, lists every unmatched item on either side with the fields that identify it, and shows the adjusted balances agreeing without a typed-in balancing figure. Classify each item as timing, still to record, or error, and post the entries it calls for: the account is not reconciled until they are in the books. Then sign, date and roll the sheet forward.

## What record should the balance be compared against?

Washington's Office of Financial Management defines a reconciliation as correlating one set of records with another, or with a physical inventory count, and says the process involves identifying, explaining and correcting differences. Any account can be reconciled this way, whether it is a loan, an accrual, a clearing account or prepaid expenses, provided something other than your ledger says what its balance should be. Naming that record is the first step, not something to assume. If you cannot name one, there is nothing to reconcile against, however good the sheet looks.

The comparison balance comes from one of three places, and they do not prove the same thing:

| Where the comparison balance comes from | What agreement with it shows |
|---|---|
| A record kept by an outside party, such as a lender's statement, a vendor's statement or a payment processor's payout report | That your balance agrees with books kept independently of yours |
| An internal subledger or schedule, such as a fixed-asset register, an accrual schedule or an open-items list kept elsewhere in your system | That two of your own records agree, which does not rule out the same error in both |
| A recomputation from source documents, such as a prepaid balance rebuilt contract by contract | That the balance matches what its inputs imply, which is only as good as the tie from each input to its invoice or contract |

The audit-evidence standard the Public Company Accounting Oversight Board (PCAOB) sets for auditors says that, in general, evidence from a knowledgeable source independent of the company is more reliable than evidence obtained only from the company's own records. That sets the plain limit of an internal comparison. Tying the ledger to your own subledger shows that the ledger captured what the subledger holds; it does not show that the subledger is right. Where an item matters, add evidence from outside that loop: a statement, a count, the contract itself. The third route rebuilds the balance from invoices, receipts and contracts; California's State Administrative Manual requires its agencies to reconcile account balances to supporting documentation.

## How should the worksheet be laid out?

Put the proof on one summary tab and the evidence on the tabs behind it: the ledger export, the comparison record, and the open-items list carried from earlier periods. The summary is what makes the file a reconciliation rather than a list, and it needs these parts:

- A header with the account name and number, the as-of date, and, as California's manual requires of its agencies, the names of the preparer and the reviewer with the date each signed
- The balance per the ledger, pulled by formula from the ledger tab
- The balance per the comparison record, pulled by formula from its tab
- The reconciling items, each placed on the side it adjusts, with its reference, date, class and the action it needs
- The adjusted balance on each side, and an unexplained difference that must show zero

Here is the summary for a business line of credit at 30 June, with invented figures:

| Line | Item | Reference | Date first appeared | Class | Action in the books | Amount |
|---|---|---|---|---|---|---|
| | Account: business line of credit, no. 2150 | | | | | |
| | As of: 30 June | | | | | |
| A | Balance per general ledger, from the ledger tab | | | | | 23,660.00 |
| B | Balance per lender's statement, from the statement tab | | | | | 24,610.00 |
| C | Difference, B minus A | | | | | 950.00 |
| 1 | Paydown sent and recorded 29 June, applied by the lender 1 July | Payment 3318 | 29 June | Timing | None; confirm it on the July statement | -2,000.00 |
| D | Adjusted lender balance, B plus item 1 | | | | | 22,610.00 |
| 2 | Annual fee the lender charged 20 June, not yet in the books | Lender's fee notice | 20 June | Still to record | Record it | 150.00 |
| 3 | Paydown of 14 May posted to the equipment loan instead of this account | Payment 3207 | 14 May | Error in books | Correct it | -1,200.00 |
| E | Adjusted book balance, A plus items 2 and 3 | | | | | 22,610.00 |
| F | Unexplained difference, D minus E | | | | | 0.00 |
| | Prepared by: J. Ortiz, 3 July | | | | | |
| | Reviewed by: R. Patel, 6 July | | | | | |

The three items account for the whole 950.00 difference: 2,000.00 plus 150.00 less 1,200.00. Nothing is left over, so nothing has to be labeled to make the sheet agree. The correction for item 3 also fixes the equipment loan, which was reduced by the same 1,200.00 in error. Item 3 turned up only because both detail tabs ran from 31 March, the last date this account agreed; June's lines alone would not have shown it. The preparer and the reviewer sign and date below line F.

## How do you bring both sides into the sheet?

Export the ledger detail for the account, meaning every posting in the period or the open items that make up the balance, and the comparison record's detail. The start date for both exports is the as-of date of last period's signed sheet or, for a first reconciliation or a cleanup, the most recent date you can prove agreement, such as the date of a statement you have tied to the ledger, or the day the account opened. Bring in every line after that date on both sides, put any items still open at that date on the open-items tab, and check that the opening balances agree after them before you match anything.

Paste each export onto its own tab without editing it, and note at the top the report's name, when it was run and any filters used. If the record exists only as a PDF statement, key its lines and closing balance onto its tab once, and check that the lines foot to the statement's own totals before the summary uses them.

Keep every field that identifies a line: date, reference or document number, counterparty, description, amount, and the system's transaction number where there is one. Under PCAOB AS 1215, auditors' documentation of procedures that inspect documents should identify the items inspected; hold your sheet to the same test. Drop the fields and the cost shows up later: on amount alone you cannot tell which of three 500.00 payments matches which, find the document you need to investigate an item, age an item that has no date, or let a reviewer trace anything back.

Before matching, prove that each tab is complete: its line count and totals should agree with the report it came from, and the ledger tab should arrive at the same closing balance as the trial balance. The PCAOB's audit-evidence standard has auditors test the accuracy and completeness of company-produced information they rely on, or the controls over it, and here the reason to borrow that test is concrete. If the ledger export is short, entries you did record will look like items still to record, and posting them would record them twice.

## How do you match items and account for the whole difference?

Add a Match column to both detail tabs. When a ledger line and a record line are the same transaction for the same amount, give both the same match number. If they are the same transaction at different amounts, give both the same match number, but bring the amount difference onto the summary as its own reconciling item with both references, and classify it like any other item: a fee not yet recorded, for example, or an error in one of the records. Where one payment settles several invoices, give the whole group one number and check that both sides of the group total the same. Then filter each tab to the lines without a match number. Those lines and any amount differences are your reconciling items. Bring each onto the summary by reference to its row rather than retyping it.

Then attribute the difference. For each unmatched line, find which transaction is the source of the difference and what it represents. As the Washington State Auditor's manual expects of a bank reconciliation, no difference should remain once the reconciling items are applied.

The tempting shortcut runs the other way: subtract one balance from the other, type a label beside the result and call it a reconciling item. That figure has been computed, not explained. Keep the difference in a check cell computed from the two adjusted balances, and treat anything but zero as unfinished work.

A first cleanup of a neglected account may turn up old differences that research cannot trace. Leave whatever remains visible as unexplained rather than dressing it up as an item, do not sign the sheet as agreed, and take the amount to whoever approves adjustments in your business.

## What does each reconciling item require in the books?

Classify every item before acting on it, because the class decides whether it waits or needs an entry:

| Class of item | What it requires in the books |
|---|---|
| Timing difference, as Treasury's guidance to federal agencies describes it: both sides record the same transaction at different times, such as a payment you recorded that the lender applied after month-end | No entry. Carry it and confirm that it clears |
| Still to record: the comparison record shows a real transaction your books lack, such as a fee the lender charged | Record it, as the Washington State Auditor's manual expects for bank-statement items |
| Error in your books: a wrong amount, a wrong account or a duplicate | Correct it with an entry, as the State Auditor's manual expects |
| Error in your own comparison record: the subledger, schedule or recomputation | Correct that record, as Treasury's guidance requires of whichever side erred, and the ledger too if the same error reached it |
| Error in the other party's record, such as your payment applied to the wrong account | Take it up with them, as the State Auditor's manual expects for bank errors, and carry it only while it is being resolved |

As Treasury's guidance requires of federal agencies' adjustments, every entry you post from the sheet should be researched and traceable to a supporting document: in the example, the lender's notice of the fee and the payment record for the misposted paydown.

## How does the sheet prove the tie instead of just displaying it?

A spreadsheet can be made to show agreement that does not exist, so the proof has to be built into the structure:

- The two balances are formulas that point to the source tabs, never numbers typed onto the summary.
- Each reconciling item points to its row on a detail tab.
- Every total is a sum over its rows, so the sheet foots by itself.
- The unexplained difference is a formula, and the only typed entries on the summary are words: descriptions, classes, actions and sign-offs.

A typed balance is the quiet failure. When you re-export the ledger after a late entry, a linked balance picks up the change and the tie breaks where it should. A typed balance keeps the old figure, the sheet still ties, and nothing on it says it has gone stale. The standard auditors hold their own working papers to is the right test: documentation should give a clear understanding of its purpose, its source and the conclusion reached.

## When is the account actually reconciled?

When the entries the sheet identified are posted, not when the sheet ties. Reconciling includes correcting differences, not only finding them. California's State Administrative Manual sets its agencies a standard worth borrowing: errors are corrected as soon as possible, and reconciling differences are resolved before financial reports are prepared.

Post the entries for every item still to record and every error in your books, then re-export the ledger and refresh the sheet. The book-side items should drop out, the balance per the ledger should now equal the adjusted book balance, and only timing differences and errors in an outside party's record should remain. In the example, once the fee and the misposting are posted, the ledger shows 22,610.00 and the only item left is the 2,000.00 paydown in transit.

If the same kind of correction turns up period after period, fix whatever keeps producing it. Treasury's guidance to federal agencies requires corrective action to address the root cause of the difference where possible.

## How do you carry the sheet from one period to the next?

Whether you need a roll-forward depends on the job:

- **One-off cleanup.** The work ends when every difference is attributed and every correction is posted. If the account will be reconciled from then on, that final sheet becomes the opening position.
- **Recurring control.** Each period starts from a copy of the last one and works through the open items before adding new ones.

For a recurring reconciliation, take these steps each period:

1. Clear what has cleared. Mark each timing difference that now appears in the comparison record with the reference and date that cleared it.
2. Age what has not. Keep the date each item first appeared, and have the sheet compute its age at the new as-of date.
3. Escalate anything overdue. A timing difference still open after it should have cleared can no longer be treated as timing, so investigate it and reclassify it as an item to record or an error. An error in the other party's record that they have not corrected when they should have is overdue too: follow up with your evidence, record each follow-up on the sheet, and take it to whoever approves adjustments in your business.
4. Add the new period's items and complete the summary as before.

The failure this prevents is an item that sounds explained and rides forward period after period. The sheet keeps tying while a real loss or error sits inside it.

## What should a reviewer be able to re-perform, and how is the sheet filed?

Work done in a spreadsheet leaves no trace in the ledger, so the retained file is all the evidence there is. Auditors' documentation must contain enough information for an experienced auditor with no previous connection to the work to understand what was done, the evidence obtained and the conclusions reached; hold your file to the same bar.

From your file, a reviewer should be able to do each of these things:

- Agree the ledger balance to the trial balance at the as-of date, and the comparison balance to the statement or schedule
- Re-foot the detail tabs and the summary
- Trace a sample of reconciling items to their source documents
- Find the entry posted for each correction
- See who prepared and who reviewed the reconciliation, and when

File the signed workbook itself in the folder for that period's close, saved read-only, with a PDF of every tab including the Match columns, and the ledger export and comparison record it was built from. Start next period from a copy, never from the filed file. If you keep the books alone, ask someone else to review, such as a co-owner or your outside accountant.

## When does the work belong back inside the accounting system?

A spreadsheet sits outside the accounting system's controls. The PCAOB's audit-evidence standard holds that, in general, company information is more reliable when the controls over it, including IT general controls and automated application controls, are effective; your sheet has only the controls you build into it. At the least, re-export the ledger when you sign, so nothing posted to the period after your first export is missed.

Three signs show that the work has outgrown the sheet:

- **Volume.** Once an account carries more items than you can mark and review by hand, you can no longer vouch that every row is present and every match is right. Counts and totals prove that no block is missing, but not that each pairing is right; past that point a hand-worked sheet is no longer a reliable instrument for this account.
- **The sheet has become the record.** If the open-items list is the only place the account's detail lives, that detail belongs in the books, in its own subledger or accounts.
- **The same corrections keep recurring.** Fix the process that produces them rather than reconciling around it.

The routes from there, whether the software's own matching tools, an enterprise system's reconciliation module, scripted matching or an outside provider, each need their own setup, and none of them is a worksheet.

## Sources

1. Washington State Office of Financial Management — *General ledger reconciliation*, undated. https://ofm.wa.gov/accounting/general-ledger-reconciliation/
2. Public Company Accounting Oversight Board — *AS 1105: Audit Evidence*, undated. https://pcaobus.org/oversight/standards/auditing-standards/details/AS1105
3. California Department of General Services — *State Administrative Manual, Reconciliations - General - 7901*, revised 06/2021. https://www.dgs.ca.gov/Resources/SAM/TOC/7900/7901
4. Public Company Accounting Oversight Board — *AS 1215: Audit Documentation*, undated. https://pcaobus.org/oversight/standards/auditing-standards/details/AS1215
5. Office of the Washington State Auditor — *BARS GAAP Manual, Bank Reconciliations*, section 3.1.9. https://sao.wa.gov/bars-annual-filing/bars-gaap-manual/accounting/accounting-principles-and-internal-control/bank-reconciliations
6. Bureau of the Fiscal Service, U.S. Department of the Treasury — *Types of Reconciliations to be Performed by Agencies*, last updated January 27, 2022. https://fiscal.treasury.gov/accounting/central-accounting-reporting-system-cars/types-of-reconciliations-agencies

## Related questions

- [How do I reconcile balance-sheet and general-ledger accounts (account reconciliations beyond the bank account) as part of the accounting close?](https://uppago.com/resources/how-to-reconcile-balance-sheet-and-general-ledger-accounts)
- [How do I perform a bank reconciliation in a spreadsheet, and is there an example or worked layout of a bank reconciliation statement in one?](https://uppago.com/resources/how-to-do-a-bank-reconciliation-in-a-spreadsheet-with-a-worked-example)
- [What does an account reconciliation look like — can I see an example of one?](https://uppago.com/resources/what-an-account-reconciliation-looks-like-with-a-worked-example)
- [Which of my accounts actually need to be reconciled, and how often?](https://uppago.com/resources/which-accounts-need-to-be-reconciled-and-how-often)
- [What is an account reconciliation statement as a document, and how is one prepared and presented?](https://uppago.com/resources/what-is-an-account-reconciliation-statement-and-how-is-it-prepared)