A bank reconciliation compares your bank statement to your accounting records to make sure they agree. Discrepancies could mean recording errors, timing differences, bank fees you missed, or in the worst case, fraud. Doing it in Google Sheets with the right formulas makes the process much faster than matching line by line manually.
Your Two Datasets
You need two lists: your bank statement transactions and your accounting records for the same period. Export both. Your bank statement has: date, description, and amount (deposits positive, withdrawals negative). Your accounting records have the same fields plus the account category.
Paste both into the same spreadsheet — bank statement on Sheet 1, accounting records on Sheet 2. Both should cover the same date range.
Matching Transactions
Add a Match column to Sheet 1 (bank statement) using MATCH to find each transaction in Sheet 2:
=IFERROR(MATCH(C2, Sheet2!C:C, 0), "NOT FOUND")
Where C2 is the transaction amount. If the exact amount is found in your accounting records, it returns the row number. If not, it returns NOT FOUND. Apply conditional formatting to highlight NOT FOUND rows in red.
This is a starting point — amounts alone can match incorrectly if you have duplicate transactions. For more precision, combine amount and date:
=IFERROR(MATCH(A2&C2, Sheet2!A:A&Sheet2!C:C, 0), "NOT FOUND")
This is an array formula — press Ctrl+Shift+Enter (or Cmd+Shift+Enter on Mac) to enter it.
Calculating the Reconciled Balance
Your reconciliation summary has four components:
- Bank statement ending balance
- Add: deposits in transit (recorded in books but not yet on bank statement)
- Subtract: outstanding checks (checks issued but not yet cleared)
- Equals: adjusted bank balance
This should equal your book balance. If it doesn't, the difference is your reconciling item to investigate.
Outstanding Items
Use COUNTIF to count unmatched items on each side:
=COUNTIF(G2:G100, "NOT FOUND")
And SUMIF to total their amounts:
=SUMIF(G2:G100, "NOT FOUND", C2:C100)
These are your timing differences and errors to investigate.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have both datasets already in the spreadsheet.
"I have my bank statement on Sheet 1 and accounting records on Sheet 2, both for May 2026. Reconcile them by matching transaction amounts and dates, flag unmatched items on both sides, and build a reconciliation summary showing the adjusted bank balance."
SheetXAI reads both sheets, matches transactions, flags discrepancies, and builds the reconciliation summary.
Example 2: Your data comes from your bank and accounting software.
"Pull May bank transactions from our bank feed and the same period from QuickBooks. Reconcile them and show me what's unmatched on each side."
SheetXAI connects to both sources and builds the full bank reconciliation.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Reconcile Two Spreadsheets in Excel, How to Build a Profit and Loss Report in Google Sheets, and Google Sheets AI Guide.
