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?

What goes in the workbook, and why keep the areas apart?

Set up one workbook per bank account and statement period, with four areas:

• Bank tab. The statement lines sit here exactly as downloaded, plus an Amount column in the sheet's sign convention.
• Book tab. The ledger lines for the same account and dates sit here, plus any lines carried forward unpaired from last period.
• Marking area. Columns to the right of the data on both tabs count possible matches, hold a pair ID and hold the class of any line left unpaired.
• Rec tab. The reconciliation statement sits here with its header, check figures and sign-off.

The Washington State Auditor's best-practice guide recommends documenting a reconciliation with a schedule showing the comparisons made and the reconciling items in enough detail to enable effective review. Separate areas give you that: the imported data stays as produced, your marks sit beside it, and the statement reads from both. A merged list mixes source figures with your edits, so nobody can tell what was imported or, next period, which lines were still open.

Turn each data area into an Excel table (Home, then Format as Table, in Microsoft's help), name the tables Bank and Book, and give every imported line a Period label for its statement month, such as Apr. Microsoft says the names in structured references adjust whenever you add or remove data from the table, so =SUM(Book[Amount]) takes in rows added later, while a fixed range such as =SUM(D2:D40) silently stops at row 40. In a table formula, [@Amount] means the Amount cell on the formula's own row. Paste at least two data rows into each table before typing its formulas; Microsoft warns that formulas entered in a one-row table may give unexpected results when more rows are added.

How do you get both sets of lines in and make them comparable?

Statement lines. Download the account activity for exactly the statement period. Wells Fargo's help, for example, has you confirm the From and To dates and choose Comma Delimited (ASCII, Spreadsheet). If your bank offers only a rendered statement, key the lines in. Take the opening and closing balances from the statement as issued, not from the download.

Ledger lines. Export a transaction-level report for the bank account over the same dates. Intuit's help for QuickBooks Online describes a General Ledger report that details the debits and credits for every transaction: add the Credit and Debit columns, use Filter to choose the account, then choose Export/Print and Export to Excel, selecting Enable Editing in Excel if data seems to be missing.

Cut-off. Georgia's State Accounting Office policy says a reconciliation should identify the general ledger balance as of the same date as the bank statement. If the statement closes on the 28th, take the ledger balance at the 28th; any other date leaves transactions no reconciling item explains, and you will hunt for an item that does not exist.

Before any matching, make four fields comparable on both tabs:

• Dates. Microsoft notes that imported or pasted data can arrive as text, which will not match a real date; text dates with two-digit years can be converted from Excel's error-checking button (Convert XX to 20XX), and its DATEVALUE function converts most other text dates to a serial number.
• Amounts. Amounts must be plain numbers, with no currency symbols or text.
• Sign. Money in is positive and money out negative throughout: a file with Debit and Credit columns needs an Amount column subtracting one from the other, and a single amount column may need reversing, so check that a deposit you know shows as positive on both tabs.
• Reference. A Ref column on both tabs holds the check or deposit number where the item has one, typed across when the bank buries it in the description.

Skip the sign step and every genuine pair shows as unmatched on both tabs, doubling the apparent reconciling items and hiding the real ones.

How do you pair items and isolate what is left?

A bank reconciliation guide sheet from the U.S. Justice Department's Office of Justice Programs starts by comparing the two records and marking the items found on both. In a spreadsheet the mark is a pair ID, such as P01, typed on both lines so each mark points to its partner. Choose how pairs are found before you build, because formulas and failures differ:

Microsoft's documentation says XLOOKUP returns the item for the first match it finds, and your if_not_found text when no valid match exists, so a duplicated reference quietly pairs with the first line. COUNTIFS, in Microsoft's words, counts the number of times all criteria are met, which supplies the guard: a Hits column counting lines on the other tab with the line's key, and an Own column counting lines on its own tab with that key. Accept a pair only when both are 1; 0 Hits means nothing matches, and 2 or more on either count means the key is not unique, so pair those lines by eye. Set the window, 31 days above, wider than your checks take to clear; check 1038 below took eight days. On the Book tab the window runs forward: =COUNTIFS(Bank[Amount],[@Amount],Bank[Date],">="&[@Date],Bank[Date],"<="&([@Date]+31)).

Microsoft notes that XLOOKUP is not available in Excel 2016 and Excel 2019; there, key the Hits and Own columns on Ref instead. In another spreadsheet application, check its vendor's help for equivalent table, lookup and protection features.

Every line left without a pair ID is a reconciling item; give it a class from the next section so the statement can total it. When a pair agrees on everything except amount, give both lines the pair ID, check the source document, and enter the difference on the error line for the side that is wrong.

What changes with a high volume of small, similar items?

Card settlements are the usual case. Three adjustments keep pairing reliable:

• Pair at the bank's level. If the bank shows one deposit per settlement batch and the ledger one line per sale, total the ledger by batch in a small table with Microsoft's SUMIFS, which adds values that meet multiple criteria, as in =SUMIFS(Book[Amount],Book[Batch],[@Batch]), and pair the totals.
• Key on fields both sides carry. Use amount and a settlement-date window, plus a batch or trace number when both files have one.
• Accept only unique keys. Equal amounts are common at volume, so ticking by eye and a bare lookup both produce false pairs.

Which direction should the statement run, and which lines does it need?

The statement can run in three directions. Pick one and apply every adjustment that way; a sheet that mixes them flips some signs and cannot agree:

The Office of Justice Programs sample is two-sided, adjusting both the bank balance and the cash balance. OpenStax's Principles of Accounting states the test: prepared accurately, the reconciliation brings the bank balance and the company balance to the same amount. Georgia's policy uses the book-to-bank form, and the bank-to-book form is its arithmetic mirror. In the worked sheet below, book to bank runs 10,065.63 + (−330.88) − 94.50 = 9,640.25: the ledger balance, plus rows 10 to 12, minus rows 5 to 7. The two-sided form suits a spreadsheet best, because each class sits against the balance it adjusts and the book-side block is the list of entries still to record.

OpenStax also defines the classes: a deposit in transit is on the books but not yet recorded by the bank, an outstanding check is deducted in the company's records but not yet cashed, bank fees and interest reach the bank account before the books, and errors can start with either the business or the bank. Give each class a line, even when its total is 0.00:

How each class is then handled is its own question.

What does a complete worked sheet look like?

All figures are invented. The statement runs April 1 to 30; deposit D-0331 and check 1038 were carried forward from March. Because the sheet is small, its counts key on amount alone; a real sheet adds Ref or a date window as a second criterion.

The Bank tab (table Bank) holds the statement lines:

The Book tab (table Book) holds the carried-forward lines and April's ledger lines:

The Book tab's marking formulas follow; the Bank tab uses Hits and Own with Bank and Book swapped:

The Rec tab holds the statement. Every amount carries its sign, so each block is simply added and the deduction rows show negative totals:

Worked in order, the summary block runs:

1. Start from the statement balance, 9,640.25, and add the deposit in transit: 9,640.25 + 1,850.00 = 11,490.25.
2. Deduct checks 1047 and 1049, 620.00 + 1,135.50 = 1,755.50: 11,490.25 − 1,755.50 = 9,734.75. With no bank errors, that is the adjusted bank balance.
3. Start from the ledger balance, 10,065.63, and add the interest: 10,065.63 + 4.12 = 10,069.75.
4. Deduct the returned item and the service charge, 310.00 + 25.00 = 335.00: 10,069.75 − 335.00 = 9,734.75. With no book errors, that is the adjusted book balance.
5. Take the difference: 9,734.75 − 9,734.75 = 0.00.

The check figures agree too: April's statement lines total 1,519.12 (9,640.25 − 8,121.13) and April's ledger lines total 1,284.50 (10,065.63 − 8,781.13). Keep rows 1 to 14 on one printable page, with each tab's unpaired lines behind it, so a lender or accountant can receive the statement without the working tabs.

What controls must you add because a spreadsheet has none?

The Washington State Auditor's guide recommends adequate security controls over any spreadsheet used to reconcile, naming passwords, tracked changes, audit trails and check figures. The five habits below apply two of those, passwords and check figures; they give no tracked changes or audit trail, and the signed PDF kept apart from the working file shows only what was approved:

• A new file every period. Save each period under a name carrying the account and statement date, and never overwrite last period's file to make this one: it is the only evidence the reconciliation was done and the only record of what was open. The Office of Justice Programs guide sheet, written for federal grant recipients, says reconciliation documentation should be saved so it is available for audit.
• A named preparer and reviewer. Enter who prepared the sheet and when in row 2. The Washington State Auditor's guide says a supervisory review should always be documented by signature and date.
• A locked finished period. Protect the finished tabs through Review, then Protect Sheet, and set a password, which Microsoft says prevents other people from removing the protection. Microsoft also says worksheet protection isn't intended as a security feature, so keep a PDF or printout of the signed statement, with the bank's statement, apart from the working file.
• Ranges that grow. Keep every total on table references and read rows 16 and 18 before signing; a line dropped from a range shows up there.
• No balancing line. If row 14 is not 0.00, leave it showing; a typed line forcing it to zero makes the sheet look finished while hiding what the reconciliation exists to find. Finding the cause is a separate question.

How do you carry outstanding items into next period's sheet?

California's Department of Finance bank reconciliation procedure begins each month by carrying the prior month's ending balance in as the beginning balance and carrying any outstanding reconciling items into the current month. In the workbook, that takes four steps:

1. Save the finished file under the new period's name, and leave the old file untouched from then on.
2. In the new file, keep every unpaired Book-tab line with its original date and set its Period to B/F. Keep an unpaired Bank-tab line too, unless its ledger entry has since been posted dated on or before the old statement date.
3. Delete the paired lines, clear the Pair and Class columns, paste in the new statement and ledger lines, and label the pasted lines with the new month.
4. Type the new balances in rows 4 and 9, the opening figures in rows 15 and 17 and the new month in row 20, then confirm row 15 equals last period's row 4 and row 17 equals last period's row 19.

In the example, May opens with D-0430, 1047 and 1049 as B/F lines, and Age keeps counting from their original dates, so an item that never clears becomes visible instead of being carried silently. The Washington State Auditor's guide says an item that cannot be identified should be isolated and observed during the next reconciliation. What to do about a stale check is a separate question.

On a first-ever reconciliation there is no carried list: export the ledger from an earlier start date so older items clearing on this statement have lines to pair with, label ledger lines dated before the statement period B/F, and examine any older line left unpaired before listing it as outstanding.

How do you check the finished sheet before signing it?

Before signing, work through these checks in order:

1. Confirm rows 16 and 18 read 0.00, so no line is missing from either tab.
2. Compare rows 4 and 15 with the statement as issued, not with the download. The reviewer does the same: the Washington State Auditor's guide says reviewers should obtain original bank statements or view-only access to online statements.
3. Run the ledger balance at the statement date again and compare it with row 9.
4. Before accepting the classes, confirm no unpaired Bank-tab line shares an amount or Ref with an unpaired Book-tab line; if one does, check both against the source documents.
5. Add each class's unpaired lines by hand and compare the totals with rows 5, 6, 10 and 11.
6. Confirm every pair ID appears on both tabs, every unpaired line has a class and every error entry has a note.
7. Confirm row 14 reads 0.00 with no typed balancing line, then sign row 2 and pass the file to the reviewer.

What changes when the ledger lives in accounting software?

The spreadsheet proves only that the statement balance and the ledger balance at one date differ by the listed items. It records nothing in the software, whose own reconciliation status stays unchanged; reconciling inside the software is a separate question.

The book-side block lists the entries still to make: OpenStax notes that transactions the bank knows about but the company does not must be journalized. Record them. If you date them on or before the statement date, rerun the ledger balance at that date and confirm it equals the adjusted book balance, 9,734.75 in the example; if you date them in the next period, it stays at row 9's figure and the Bank-tab lines carry forward under roll-forward step 2. Either way, enter the rerun balance in row 19.

At the start of each period, compare the ledger balance the software now reports for the last statement date with row 19 of last period's sheet. If it has moved, a transaction in a reconciled period was posted or edited; find it before building the new sheet.