Bank Reconciliation Example in Excel: Layout and Formulas

Quick Answer: To do a bank reconciliation in Excel, put the bank's transactions on one sheet and your ledger's on another, give every row a match key built from the amount and an occurrence count, and use COUNTIFS or XLOOKUP to mark each row matched or unmatched. A summary sheet then uses SUMIFS to total the unmatched items and shows the adjusted bank and book balances with a difference cell that should read zero.

A bank reconciliation in Excel works best as a three-sheet workbook: the bank's lines, your ledger's lines, and a summary that adds them up. Formulas do the matching, so the only rows you read by eye are the ones Excel could not pair. This guide gives the layout, the exact formulas, and how to get the bank side in without retyping it.

What layout should a reconciliation workbook use?

Keep each sheet as a plain table with one header row and no merged cells, then format it as an Excel Table so formulas extend as you paste new months in. Three sheets cover it:

Two rules prevent most formula headaches later:

• One signed Amount column on both sheets. Money out is negative, money in is positive. Separate Debit and Credit columns double the number of formulas and invite sign mistakes.
• Numbers stored as numbers. A pasted amount like 1,204.50 with a trailing space is text, and COUNTIFS will never match it to the number 1204.5. Select the column, and if the status bar shows a Count but no Sum, the values are text. Running Text to Columns on the column without changing any options usually converts them in place.

The rest of this guide assumes the Bank sheet has Date in column A, Description in B, Amount in C, Check No. in D, Key in E and Status in F, with data starting in row 2. The Ledger sheet uses the same letters.

How do you match bank lines to ledger lines in Excel?

Matching on amount alone fails the moment two transactions share an amount: two $49.99 software subscriptions, or three $1,500.00 transfers. The fix is a key that numbers repeated amounts in order.

Step 1: build the key. In Bank!E2:

=TEXT(C2,"0.00")&"|"&COUNTIFS($C$2:C2,C2)

The first $49.99 becomes -49.99|1, the second -49.99|2. Put the identical formula in Ledger!E2. The expanding range $C$2:C2 is what makes it count occurrences so far rather than in the whole column. Sort both sheets by date before building keys, so the first occurrence on one side pairs with the first on the other.

Step 2: flag the match. In Bank!F2:

=IF(COUNTIFS(Ledger!$E:$E,E2)>0,"Matched","Unmatched")

And in Ledger!F2, the mirror:

=IF(COUNTIFS(Bank!$E:$E,E2)>0,"Matched","Unmatched")

Microsoft's COUNTIFS reference notes that every extra range must be the same size as the first, so if you add a second condition, such as a date window, keep both ranges as full columns or both as the same table column.

Step 3 (optional): tighten with a date window. Amount-only keys can pair a March 3 payment with a March 28 one. To require that the ledger date sits within five days of the bank date, replace the Bank status formula with:

=IF(COUNTIFS(Ledger!$C:$C,C2,Ledger!$A:$A,">="&A2-5,Ledger!$A:$A,"<="&A2+5)>0,"Matched","Unmatched")

This version ignores the occurrence key, so use it as a second pass on rows the first pass left unmatched, not as a replacement.

Step 4: checks by number. Checks deserve their own lookup because the number is unique and the bank prints it. In a spare Bank column:

=IF(D2="","",XLOOKUP(D2,Ledger!$D:$D,Ledger!$C:$C,"not in ledger"))

That returns the ledger amount for the same check number, or the text not in ledger. Put =IF(D2="","",C2-G2) next to it and any nonzero result is a check recorded at the wrong amount. Per Microsoft's XLOOKUP page, XLOOKUP is in Excel for Microsoft 365, Excel 2021 and Excel 2024, but not Excel 2016 or 2019. On those versions use =IFERROR(INDEX(Ledger!$C:$C,MATCH(D2,Ledger!$D:$D,0)),"not in ledger").

Finish with a conditional formatting rule that highlights any cell containing the word Unmatched. The highlighted rows are your reconciling items.

The summary sheet and its formulas

The Rec sheet turns unmatched rows into the classic two-sided reconciliation. Assume the statement's ending balance is typed into Rec!B2 and the ledger's ending balance into Rec!B9.

Because outstanding payments are already negative, B5 adds them rather than subtracting. Mixing a signed column with a minus sign in the total is the most common reason a spreadsheet reconciliation is off by exactly twice the outstanding checks.

Watch the argument order. SUMIFS takes the range to add first, while the older SUMIF takes it third; Microsoft calls the swap a common source of errors.

The ROUND in B14 matters. Floating-point arithmetic can leave a difference of 0.0000000001 that displays as 0.00 but fails an =B14=0 test. Rounding to cents removes it.

A worked check of the layout: say the bank statement ends at $24,310.66, one ledger deposit of $3,075.00 is unmatched, and two ledger checks of $918.20 and $2,406.00 are unmatched. B3 shows 3,075.00, B4 shows −3,324.20, and B5 shows $24,061.46. If the ledger ends at $24,103.96, and the bank sheet's unmatched rows are a $42.50 wire fee, B11 is −42.50 and B12 is $24,061.46. B14 reads 0.00.

For a longer paper walkthrough with fees, interest and an error, see the bank reconciliation example.

Getting the bank side into Excel

The formulas are the easy part. The slow part is getting clean bank rows. In order of preference:

1. The bank's CSV or Excel download. Most online banking sites offer one for recent activity. It is the cleanest source when it reaches back far enough.
2. Excel's Get Data from PDF. Microsoft 365 can import tables from a PDF statement through the Get Data menu. It works on simple single-table layouts; on statements where the table breaks across pages or a description wraps onto a second line, expect to clean up split rows in Power Query.
3. A statement converter. QuickBankConvert takes the PDF you downloaded from the bank and returns an Excel file with Date, Description, Category, Amount and Balance columns, with amounts already signed the way the layout above expects. A free account downloads CSV or Excel for 3 statements a month, up to 10 pages each; pricing lists the larger plans.

Whatever the source, check two numbers before you match anything: the sum of the Amount column should equal closing balance minus opening balance. If it does not, a row was dropped or doubled in the import, and no formula downstream will fix that.

Carrying the workbook into next month

A reconciliation workbook earns its keep in month two:

• Copy, do not rebuild. Duplicate the workbook, clear the Bank and Ledger tables, and paste the new month's rows. The Table formulas refill themselves.
• Bring forward last month's unmatched ledger rows. Outstanding checks from May belong in June's Ledger sheet, or they will look like bank debits with no ledger entry when they finally clear.
• Lock the finished month. Save the completed file as a PDF or protect the sheet, so a later edit does not quietly change a reconciled balance.

If you run reconciliations across many accounts or clients, the formulas stay the same but the volume does not, and the guide to automating bank reconciliation covers when a spreadsheet stops being the right tool.

Related guides

• Bank reconciliation example with solution
• Bank statement reconciliation guide
• PDF bank statement to Excel
• Automating bank reconciliation