Review / tutorial
How to reconcile a bank statement in Excel (step by step, with formulas)
Put the statement rows on one sheet and your ledger on another. Match them with COUNTIFS or XLOOKUP, then list the unmatched items. Adjust the bank balance for timing items, such as deposits in transit and outstanding checks, and adjust the book balance for bank-only items, such as fees and interest. The two adjusted balances should agree to 0.00.
A worked example
A reconciliation summary that agrees to 0.00
Fictional example · March reconciliation summary
| Bank side | Amount | Book side | Amount |
|---|---|---|---|
| Closing balance per statement | 5,750.00 | Balance per ledger | 5,365.00 |
| Add deposit in transit | 600.00 | Add interest paid by the bank | 5.00 |
| Less outstanding check | (1,020.00) | Less bank fee | (40.00) |
| Adjusted bank balance | 5,330.00 | Adjusted book balance | 5,330.00 |
Step by step.
Get the statement into rows
Put one transaction per row on a sheet named Bank, with a date, a description and a signed amount (money in positive, money out negative). If your bank only gives you a PDF, convert it first. Fulla Ledger’s XLSX export has three sheets: Transactions holds the approved rows, Statement holds statement-level context, and Original Fields keeps the original labeled values from the page. You review each row against its source page and approve before you export.
Prove the statement adds up
Before matching anything, check that opening balance plus credits minus debits equals the closing balance printed on the statement. With signed amounts in column C and the opening balance in cell F1, the expected closing balance is =F1+SUM(C2:C200). For a running balance, put =F1+C2 in D2 and =D2+C3 in D3, then fill down. If the last running balance differs from the printed closing balance, fix the rows before you reconcile.
Match each row against your ledger
Put your ledger rows on a sheet named Ledger with the same layout. On the Bank sheet, add a Matched column with =COUNTIFS(Ledger!$A$2:$A$500,A2,Ledger!$C$2:$C$500,C2). A result of 0 means no ledger row has that date and amount; 1 means one match; 2 or more means a possible duplicate. To see which ledger entry matched, use =XLOOKUP(C2,Ledger!$C$2:$C$500,Ledger!$B$2:$B$500,"Not in ledger"). Repeat the COUNTIFS on the Ledger sheet, pointing at the Bank sheet, to find book entries the bank has not processed yet.
Build the reconciliation summary
On a Summary sheet, start the bank side with the statement closing balance, add deposits in transit and subtract outstanding checks. Start the book side with the ledger balance on the same date, add bank-only credits such as interest and subtract bank-only charges such as fees. Use SUMIFS on a Type column to total each group, then compute the difference between the two adjusted balances. A completed reconciliation shows a difference of 0.00.
What you need
You need the bank statement for the period, with its opening and closing balances, and your own record of the same account for the same dates: a cash book, a ledger export from your accounting software, or a spreadsheet you keep. You also need the list of items left open from last month’s reconciliation, because outstanding checks and deposits in transit often clear in the following month.
This guide covers the spreadsheet method. For the concepts behind it, such as why timing differences happen and which side each adjustment belongs on, read the general guide to reconciling a bank statement first.
A worked example
This example is fictional. The statement for March shows an opening balance of 5,000.00, credits of 3,200.00, debits of 2,450.00 and a closing balance of 5,750.00. The check passes: 5,000.00 plus 3,200.00 minus 2,450.00 is 5,750.00.
The ledger shows 5,365.00 on 31 March. Matching leaves four unmatched items. A customer deposit of 600.00 was recorded in the ledger on 31 March but reached the bank in April, so it is a deposit in transit. A check for 1,020.00 was written in March and not yet cashed, so it is outstanding. The bank charged a 40.00 fee and paid 5.00 of interest, and neither is in the ledger yet.
Bank side: 5,750.00 plus 600.00 minus 1,020.00 gives 5,330.00. Book side: 5,365.00 plus 5.00 minus 40.00 gives 5,330.00. The difference is 0.00. Record the fee and the interest in the ledger, and carry the deposit and the check forward to April’s reconciliation.
When it will not balance
Work through the likely causes in this order, because the early ones are the quickest to rule out.
- The statement itself does not add up: a missing page, a row read with the wrong sign, or a wrapped description split into two rows. Recheck the running balance against the printed closing balance.
- A sign is reversed: a difference equal to twice one transaction usually means a debit was entered as a credit, or the other way round.
- Two digits are swapped: a difference that divides evenly by 9 often points to a transposition, such as 54.00 entered as 45.00.
- A duplicate: a COUNTIFS result of 2 or more on either sheet. Excel’s Remove Duplicates tool deletes rows permanently, so use it only on a copy, and only after you confirm the rows really are duplicates and not two genuine payments of the same amount on the same day.
- A date mismatch: the ledger date and the bank date differ by a day or two, so COUNTIFS on date and amount returns 0. Match on amount alone for those rows and check the dates by eye.
- An item carried from last month: an outstanding check from a previous period that has now cleared, or one that is still open.
A monthly checklist
Keep the workbook from month to month and run the same checks each time.
- The statement covers the full period with every page present.
- Opening balance matches last month’s closing balance.
- Opening balance plus the sum of signed amounts equals the printed closing balance.
- Every bank row has a Matched result, and every 0 is explained.
- Every ledger row the bank has not processed is listed as in transit or outstanding.
- Bank-only items such as fees and interest are recorded in the ledger.
- The difference on the Summary sheet is 0.00.
- Open items are copied to next month’s workbook.
Sources and documentation
- Microsoft: SUMIFS function(opens in a new tab)
- Microsoft: COUNTIFS function(opens in a new tab)
- Microsoft: XLOOKUP function(opens in a new tab)
- Microsoft: find and remove duplicates(opens in a new tab)
Follow the source links for original definitions and current product requirements.
Questions about this guide
Which Excel formula is best for matching bank transactions?
COUNTIFS is the simplest: it counts how many ledger rows share a date and amount with the bank row, so 0 flags an unmatched item and 2 or more flags a possible duplicate. XLOOKUP is useful when you also want to bring back the matching ledger description or reference.
Does Excel have a built-in bank reconciliation tool?
Microsoft documents general functions such as SUMIFS, COUNTIFS and XLOOKUP and the Remove Duplicates tool, which are the building blocks used here. You build the reconciliation layout yourself.
What if my bank statement is a PDF?
Convert it into rows first, then check that the rows add up to the printed closing balance before you match anything. Fulla Ledger converts statement PDFs into rows you review against the source page and export as an XLSX workbook.
Should I put deposits in transit on the bank side or the book side?
The bank side. They are already in your ledger but not yet on the statement, so you add them to the statement closing balance. Outstanding checks are subtracted on the bank side for the same reason.
What does a difference of 0.00 prove?
That the two adjusted balances agree. It does not prove every row is right, because two errors can cancel each other out. Unexplained 0 results in the Matched column still need checking.