How to reconcile a bank statement in Excel (step by step with a free template)
Reconciling a bank statement in Excel means proving that your books and the bank agree, and writing down every dollar that doesn't. You can do it by hand with a free two-sided template, or skip the spreadsheet and let a checker match the statement to a books export. Below: the step-by-step with Utilly's free bank reconciliation template, what to do with uncleared items, how to find a leftover difference, and when the automated tool is faster.
Short answer: yes — download the free bank reconciliation template (.xlsx), enter the statement's ending balance and your books' ending balance, tick off the lines that match, and list what's left as deposits in transit, outstanding checks, bank fees or interest. When both adjusted sides agree, you're reconciled. Or skip the spreadsheet: upload the statement and a books export to the free bank reconciliation checker and get the unmatched list in seconds.
Free bank reconciliation template for Excel
Blank Reconciliation sheet, a worked Example that balances to the cent, and How to use. Ordinary spreadsheet formulas only — opens in Excel, Google Sheets (File → Import), LibreOffice and Numbers. No sign-up, no macros.
1. Gather the statement and the books
You need both sides for the same account and the same ending date:
- Bank statement. The PDF from online banking (or a CSV/Excel download of the same period). Note the ending balance printed on page 1. If you only have the PDF and want rows to tick against, convert it first with the bank statement PDF to Excel converter — the free preview already checks that the rows add up to that ending balance. Practising on a made-up month? Use the sample bank statement Excel file.
- Books. A report of the same bank account up to the statement end date: in QuickBooks Online, an Account QuickReport; in Desktop, the register or a Custom Transaction Detail report; in Xero, Account Transactions; or a general ledger / cash book export for that account. Money in should be positive and money out negative on both sides (flip a ledger's Debit/Credit columns if needed).
If last month never reconciled, fix that first — this month's beginning balance is last month's ending balance. See QuickBooks reconciliation beginning balance off.
2. Open the template and enter the two balances
- Download the .xlsx and open it in Excel (or File → Import in Google Sheets).
- On the Reconciliation sheet, fill the yellow header cells: account name, statement end date, prepared by.
- Enter the ending balance per bank statement on the bank side and the ending balance per books on the book side.
The template's status line will already show a difference. That's normal — you haven't listed the reconciling items yet. The worked Example sheet shows a finished month that lands on 0.00; the full layout is explained on the bank reconciliation template guide.
3. Tick matching lines
Compare the statement rows to the books. Match checks by number first (they can clear weeks later), then other amounts with dates a few days apart. Anything that appears on both sides with the same amount is done — leave it alone.
For a long statement, put the bank rows on one sheet and the books on another and use COUNTIFS on amount plus a date window, or XLOOKUP. The formulas are in compare a bank statement to the general ledger in Excel. What you're left with after ticking is what goes into the template's lists.
4. List uncleared items (bank side and book side)
Each leftover goes on exactly one side of the template:
| What's left | Put it on | Why |
|---|---|---|
| Deposit in your books that isn't on the statement yet (deposit in transit) | Bank side, add | The bank hasn't recorded it; no journal entry |
| Check or payment in your books that hasn't cleared (outstanding) | Bank side, subtract | It clears when cashed; no journal entry |
| Bank error (wrong amount, or another customer's item) | Bank side, add or subtract | The bank corrects it |
| Interest earned or a credit the bank collected | Book side, add | You need a journal entry |
| Bank fee, NSF (returned) check, or automatic payment you haven't entered | Book side, subtract | You need a journal entry |
| Book error (you recorded the wrong amount) | Book side, add or subtract | Correcting entry |
Rule of thumb: if the bank hasn't recorded it, it goes on the bank side. If you haven't recorded it, it goes on the book side. Bring forward last month's outstanding checks and deposits in transit that still haven't cleared. Only book-side items change your ledger.
As you fill the yellow input rows, the template totals each list and updates the adjusted bank balance, the adjusted book balance, and the difference.
5. When the difference isn't 0.00
When both adjusted sides agree, the status reads Reconciled. If not, the template's Where to look line uses the size of the leftover:
- Equals one transaction: that amount is missing from a list, or was ticked on one side only.
- Twice a transaction: that item has the wrong sign (a payment entered as a deposit).
- Divisible by 9: look for transposed digits (154.80 entered as 145.80).
- An old outstanding item: a check that will never clear may need voiding; a stale deposit in transit is worth investigating.
Work through the seven usual causes in order in how to find a bank reconciliation discrepancy. After an import, also check for duplicate transactions.
6. Post the book-side entries
Every book-side item needs an entry dated in the statement period, or next month starts out of balance: interest as a deposit (or credit to interest income), fees and NSF checks as expenses or against receivables, and corrections to mistyped amounts. Bank-side items need nothing — they clear on next month's statement, where you tick them off.
Copy the Reconciliation sheet for each month (right-click the tab → Move or Copy → Create a copy) so every workpaper stays on file. Reconcile oldest first.
7. Skip the spreadsheet: the free checker
Filling in the template is quick once you have the lists. Building those lists — ticking hundreds of lines by hand — is the slow part. Utilly's bank reconciliation checker does that matching for you:
Match the statement to your books automatically
Upload the bank statement (PDF, CSV, Excel, OFX, QFX or QBO) and an export of the same account from QuickBooks, Xero or any register CSV. It matches by amount within a date window (checks by number) and lists what's in your books but not on the statement (outstanding checks and deposits in transit), what's on the statement but not in your books (fees and interest), amounts that differ, reversed signs and possible duplicates — and shows how they add up to the difference. You get the summary plus the first three of each unmatched kind free, with no account needed; unlocking the full CSV and PDF report costs 1 credit. Files are processed in memory and deleted after 1 hour.
You can still drop the checker's lists into the Excel template as the monthly workpaper for review. Statement only as a PDF? Convert it first with PDF to Excel or PDF to CSV, then upload that as the bank file.
8. Credit cards and catch-up work
- Credit cards. The balance is money you owe, so the signs run the other way. Use the separate credit card reconciliation template, or keep this bank template and enter balances as negatives. Charges that haven't posted go under outstanding payments; a payment you've sent that hasn't posted goes under deposits in transit.
- Catch-up bookkeeping. Tie out every statement before you import, then reconcile oldest first so each month's beginning balance is solid. The process is in catch-up bookkeeping with bank statements.
- Day-to-day register. For a small account kept in a spreadsheet, the check register template has a Cleared column; its cleared balance against the statement is the same proof without the two-sided workpaper.
Method: the two-sided format (adjusted bank balance and adjusted book balance) is the standard bank reconciliation layout. The template's Example business, names and amounts are fictional. Template and checker details checked November 5, 2026.
FAQ
Can you reconcile a bank statement in Excel?
Yes. Download a two-sided bank reconciliation template, enter the statement ending balance and your books ending balance for the same date, tick off the lines that match, and list what's left as deposits in transit, outstanding checks, bank fees or interest. When both adjusted sides agree, the account is reconciled. Utilly's free template does the totals and shows where to look if the difference isn't 0.00.
What is the bank reconciliation format in Excel?
Two sides that should land on the same number. The bank side starts from the statement ending balance, adds deposits in transit and subtracts outstanding checks. The book side starts from the ledger balance, adds interest and other credits not yet recorded, and subtracts fees, NSF checks and charges not yet recorded. Only book-side items need a journal entry.
How do I find the difference when a bank reconciliation won't balance?
Use the size of the leftover. Equal to one transaction usually means that amount is missing or ticked on one side only; twice a transaction means a reversed sign; divisible by 9 often means transposed digits. Utilly's template spells these out on a Where to look line, and the discrepancy guide walks through seven causes in order.
Do outstanding checks cause a reconciliation difference?
Not by themselves. Checks and deposits that haven't cleared stay on the bank side as outstanding items and are expected. They cause a difference only if you tick them as cleared, leave them off the list, or entered them in the wrong account.
Is there a free bank reconciliation template for Excel?
Yes. Utilly's .xlsx has a blank Reconciliation sheet, a worked Example that balances, and instructions. Ordinary spreadsheet formulas only, so it opens in Excel, Google Sheets (File, Import), LibreOffice and Numbers. No sign-up.
Can I reconcile without building the spreadsheet every month?
Yes. Utilly's bank reconciliation checker takes the statement (PDF, CSV, Excel, OFX, QFX or QBO) and a books export, matches by amount within a date window and checks by number, and lists missing items, amount differences, reversed signs and duplicates. The summary and the first 3 items of each kind are free; the full report is 1 credit.
Does this work for credit cards?
Yes, with the signs flipped: the balance is money you owe. Use Utilly's credit card reconciliation template, or keep the bank template and enter balances as negatives. Charges that haven't posted go under outstanding payments; a payment you've sent that hasn't posted goes under deposits in transit.
Related guides
- Free bank reconciliation template for Excel (with a worked example)
- How to find a bank reconciliation discrepancy: 7 causes and a 15-minute method
- QuickBooks reconciliation not matching: beginning balance is off, or the difference isn't 0.00
- Free sample bank statement in Excel (with a blank template)
- Free credit card reconciliation template for Excel (with a worked example)
- Catch-up bookkeeping: getting 12 months of bank statements into QuickBooks or Xero and reconciled
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
