How to Reconcile a Bank Statement in Google Sheets

TL;DR: To reconcile a bank statement in Google Sheets, put the bank's transactions on one tab and your own records on another, give each row a match key (date window plus amount), use COUNTIFS or XLOOKUP to flag matched and unmatched rows, then build a summary: statement closing balance, plus deposits in transit, minus outstanding payments, should equal your book balance. Any remaining difference is something to investigate, not something to plug.

We've written about reconciling a bank statement in Excel before, and plenty of readers asked for the Google Sheets version. The logic is the same; what changes are a few functions, how you get data in, and the fact that Sheets makes it easy to share a reconciliation with a client or a colleague for review. This guide walks through a complete monthly reconciliation built entirely in Sheets.

What you need

1. The bank statement for the month, ideally as data rather than a PDF.
2. Your records for the same account: a cash book, a register exported from accounting software, or a list you keep yourself.
3. Last month's reconciliation, if there is one, so you know which items were outstanding at the start.

If the statement is a PDF, convert it to CSV first. SheetStatement extracts the transactions and checks the total against the statement balances, so you start from complete data. Typing a statement into a sheet by hand is the most common source of reconciliation differences we see.

Step 1: Set up the tabs

Create a new spreadsheet with four tabs:

• Bank: the statement transactions.
• Books: your recorded transactions.
• Outstanding: items carried from last month.
• Summary: the reconciliation itself.

On Bank and Books, use the same columns: Date, Description, Amount, Key, Matched. Amounts should be signed the same way on both tabs: money in positive, money out negative.

Step 2: Import the data

In Sheets, use File > Import, upload the CSV, and choose to insert it into the Bank tab (or a new tab you then copy from). Check three things immediately:

1. Dates are real dates. Click a date cell and look at the formula bar; if it's text, select the column and use Format > Number > Date, or rebuild it with =DATEVALUE(). Locale matters: a UK-format CSV in a US-locale spreadsheet can swap day and month. Set the locale in File > Settings before importing.
2. Amounts are numbers. Right-aligned values are usually numbers; left-aligned ones are probably text. Use =VALUE() or remove currency symbols with Find and replace.
3. The total ties out. =SUM(C2:C) on the Bank tab should equal closing balance minus opening balance.

If step 3 doesn't agree, stop and fix the data. Reconciling from an incomplete statement wastes time. The usual culprits are a page missed during conversion, a header or subtotal row imported as a transaction, or a balance-forward line counted as a deposit. Delete subtotal and balance rows, keep only real transactions, and check the total again.

Step 3: Build a match key

The simplest key is the amount, because amounts rarely change between your books and the bank. Dates do: a payment you recorded on the 28th might clear on the 2nd. So the matching uses the amount first and the date as a tie-breaker.

In D2 of both tabs:

=TEXT(C2,"0.00")

That turns the amount into a consistent text key. You'll use the date separately.

Step 4: Flag matches

On the Bank tab, in E2, count how many Books rows have the same amount within seven days:

=COUNTIFS(Books!C:C, C2, Books!A:A, ">="&(A2-7), Books!A:A, "<="&(A2+7))

Do the same on the Books tab, pointing at Bank. Then read the results:

• 1: a clean match.
• 0: no match. On the Bank tab, this is something the bank recorded that you haven't (fees, interest, a missed deposit). On the Books tab, it's an outstanding item or an error.
• 2 or more: several possible matches, typically repeated equal amounts. Review these by hand.

To fill the whole column at once, wrap the logic with ARRAYFORMULA or MAP. A version using MAP and LAMBDA, available in Google Sheets:

=MAP(A2:A, C2:C, LAMBDA(d, a, IF(a="", "", COUNTIFS(Books!C:C, a, Books!A:A, ">="&(d-7), Books!A:A, "<="&(d+7)))))

Pulling matched details side by side

Counts tell you whether a row matched; they don't show you what it matched to. For review, it's useful to bring the matched row's description and date next to each bank row. On the Bank tab, in a new column:

=IFERROR(INDEX(FILTER(Books!B:B, Books!C:C=C2, ABS(Books!A:A-A2)<=7), 1), "")

That returns the description of the first Books row with the same amount within seven days. Put the date next to it with the same formula pointing at Books!A:A. Scanning down the two description columns, mismatches jump out: a 120.00 bank debit to the electricity company "matched" to a 120.00 book entry for stationery is almost certainly two different transactions that happen to have the same amount.

XLOOKUP works too if you only need an exact amount match without a date window:

=XLOOKUP(C2, Books!C:C, Books!B:B, "no match")

Highlighting with conditional formatting

Colour makes a reconciliation much faster to read:

1. Select the Bank tab's data range.
2. Open Format > Conditional formatting.
3. Choose Custom formula is and enter =$E2=0 with a red fill for unmatched rows.
4. Add a second rule, =$E2>1, with an amber fill for multiple matches.

Do the same on the Books tab. You now have a visual to-do list: red rows need explaining, amber rows need pairing, and everything else is done.

Recording the adjustments

Reconciliation isn't finished when the difference reaches zero on paper. Items on the "bank items not in books" line, such as fees, interest and the unrecorded donation in the example below, need entering into your records. Add them to the Books tab (or your cash book) with the bank date and a note like "per bank rec, March". Next month, they'll match automatically and drop off the summary.

Outstanding items carry forward instead. Copy them to next month's Outstanding tab with their original dates, so you can see how long each has been waiting.

A monthly routine

Once set up, the routine takes about fifteen minutes for a small account:

1. Download or convert the statement and import it to a fresh copy of the template.
2. Paste in this month's book entries.
3. Check the Bank tab total against the statement.
4. Review red and amber rows.
5. Record bank-only items in your books.
6. Confirm the difference is zero and fill in the sign-off block.
7. Carry outstanding items to next month.

Step 5: Resolve the duplicates

Rows with a count of 2 or more need a decision. Three monthly subscriptions for 29.99 might match each other in any order. Use the description and date to pair them, and type the pairing into a notes column, for example "matched to Books row 41". If it matters which pairs with which, add a sequence number: the first 29.99 in Bank matches the first in Books, and so on. A helper column with =COUNTIFS(C$2:C2, C2) gives you that sequence.

Step 6: Bring in last month's outstanding items

Items that were outstanding at the end of last month (checks written but not cashed, deposits not yet cleared) should mostly clear this month. List them on the Outstanding tab and check each against the Bank tab. Cleared ones drop off; uncleared ones carry forward.

Our guide on outstanding checks and deposits in transit explains how to handle items that stay outstanding for months.

Step 7: Build the summary

On the Summary tab:

Line	Formula
Balance per bank statement	(type from statement)
Add: deposits in transit	=SUMIFS(Books!C:C, Books!E:E, 0, Books!C:C, ">0")
Less: outstanding payments	=SUMIFS(Books!C:C, Books!E:E, 0, Books!C:C, "<0")
Adjusted bank balance	=B1+B2+B3
Balance per books	(from your records)
Add: bank items not in books	=SUM(FILTER(Bank!C:C, Bank!E:E=0))
Adjusted book balance	=B5+B6
Difference	=B4-B7

Outstanding payments are already negative, so adding them reduces the balance. Bank items not in books (fees, interest) need recording in your books; once you've done that, they disappear from this line next time.

When the difference is zero, the account is reconciled.

Step 8: Investigate any difference

If the difference isn't zero:

1. Divide it by 9. If it divides evenly, look for a transposition, like 54.00 recorded as 45.00.
2. Halve it. If a transaction of that amount exists, it may have been recorded with the wrong sign.
3. Search for it. =FILTER(Bank!A:C, Bank!C:C=ABS(B8)) and the same on Books.
4. Check the opening position. If last month's reconciliation was wrong, this month's will be too.

Our article on finding bank reconciliation discrepancies covers these techniques in more depth.

A worked example

A small charity keeps its books in Sheets. The treasurer reconciles the main account monthly.

• Statement closing balance: 8,412.60.
• Books balance: 8,105.35.
• Matching flags 41 of 44 bank rows and 43 of 45 book rows.
• Unmatched in Books: a check for 350.00 written on the 29th (outstanding) and a deposit of 60.00 banked on the 31st (in transit).
• Unmatched in Bank: a monthly service charge of 7.50, and interest of 0.25. A third unmatched bank row, for 49.75, turns out to be a donation received by transfer that nobody recorded.

Summary:

• Adjusted bank balance: 8,412.60 + 60.00 − 350.00 = 8,122.60.
• Adjusted book balance: 8,105.35 − 7.50 + 0.25 + 49.75 = 8,147.85.

That leaves a difference of 25.25, so something's still off. Checking the duplicates column, the treasurer finds one 25.25 payment matched twice in Books: a direct debit recorded once on the 1st and again on the 3rd. Removing the duplicate brings the adjusted book balance to 8,122.60. Difference: zero.

The duplicate matching flag (count of 2) pointed straight at the problem, once someone looked at it.

Sharing and review

One of the real advantages of Sheets is review. Share the file with your accountant or a second trustee with comment access, and they can ask questions directly on the rows. A few habits make review easier:

• Protect the formula columns (Data > Protect sheets and ranges) so a reviewer doesn't break them.
• Use the version history (File > Version history) instead of keeping copies named "final v2".
• Add a sign-off block on the Summary tab: prepared by, date, reviewed by, date.
• Keep one file per account per year, with a tab per month, or one file per month if the data is large.

Using a template

Once your first reconciliation works, turn it into a template: clear the data from Bank and Books, keep the formulas and the summary, and save it with a name like "Bank rec template". Each month, make a copy, paste in the new data, and update the outstanding items. Our Excel bank reconciliation template guide shows a template layout that translates directly to Sheets.

Google Sheets limits to know about

Google Sheets handles a typical small business month (a few hundred transactions) with no trouble. Very large data sets, such as several years of a busy account in one file, can get slow, especially with many COUNTIFS formulas over whole columns. If that happens:

• Limit ranges to the rows you need (for example Books!C2:C2000 rather than Books!C:C).
• Split by month or quarter.
• Replace formulas with values once a month is reconciled and signed off.

When to use accounting software instead

If you're reconciling several accounts every month and recording the results in accounting software anyway, use the software's reconciliation feature: it records which transactions have cleared and prevents later edits to reconciled periods. Sheets is ideal for small organizations without accounting software, for one-off reviews, for catch-up work, and for checking a reconciliation done elsewhere.

Reconciling credit cards in Sheets

The same layout works for credit cards. Reverse your sign thinking: charges increase what you owe, payments reduce it. Keep the convention consistent between the two tabs, and the matching formulas work unchanged. Our credit card reconciliation guide covers the card-specific details, such as pending charges and statement dates.

Troubleshooting the formulas

A few problems come up again and again when people build this in Sheets.

COUNTIFS returns 0 for rows that obviously match. Usually one side has text amounts or text dates. Check with =ISNUMBER(C2) and =ISNUMBER(A2) on both tabs. Imported CSVs in a different locale are the usual cause: a UK file opened in a US-locale spreadsheet turns 03/04/2026 into March 4, or leaves dates like 25/04/2026 as text because there's no 25th month. Fix the locale in File > Settings and re-import.

Amounts that should match differ by a cent. Floating point again. Round both sides: =ROUND(C2,2) in a helper column, and match on that.

The formula is slow. Whole-column references in thousands of COUNTIFS formulas slow Sheets down. Limit ranges to the rows you use, or use a single MAP formula at the top of the column instead of one formula per row.

Matches across months. A payment recorded on the 30th might clear on the 2nd of next month and match nothing this month. That's correct: it's outstanding. Don't widen the date window to catch it, or you'll create false matches with next month's items.

A mini checklist before you sign off

1. Bank tab total equals closing minus opening balance.
2. No unexplained red rows on either tab.
3. Every amber row paired or explained in the notes column.
4. Bank-only items recorded in the books.
5. Outstanding items listed with dates, and last month's list checked.
6. Difference is exactly zero.
7. Sign-off block completed, and the file shared with the reviewer.

Edge case: several accounts in one file

If you reconcile a current account and a savings account for the same organization, keep a separate Bank and Books tab pair for each, and one Summary tab with a section per account. Transfers between the two accounts should appear on both: out of one, into the other. If a transfer clears in one account on the 31st and the other on the 1st, it's in transit on one side, and your summary will show it as such. That's correct, and it's worth a note so nobody "fixes" it next month.

FAQ

Can you do a bank reconciliation in Google Sheets?

Yes. Put bank and book transactions on separate tabs, flag matches with COUNTIFS, and build a summary that adjusts both balances for outstanding items. Sheets handles typical monthly volumes easily.

How do I import a bank statement into Google Sheets?

Use File > Import with a CSV file. If your statement is a PDF, convert it to CSV first. Check that dates and amounts imported as dates and numbers, and that the total matches the statement.

What formula matches transactions in Google Sheets?

COUNTIFS on amount with a date window is a reliable starting point. XLOOKUP or FILTER helps you pull the matched row's details for review.

Why does my reconciliation show a difference?

Common causes are unrecorded bank fees or interest, duplicates, transpositions, sign errors and incorrect opening positions. Divide the difference by 9, halve it, and search for it to narrow things down.

Is there a Google Sheets bank reconciliation template?

Google offers some templates, and you can build your own in under an hour with the layout above. Save it once and copy it each month.

Should I reconcile in Sheets or in my accounting software?

If you use accounting software, reconcile there. Sheets is best for organizations without it, for catch-up and review work, and for one-off checks.