# Bank Reconciliation Template Google Sheets Can Run

- Author: PreciseBankFlow (https://www.precisebankflow.com)
- Published: 2026-02-20
- Updated: 2026-02-20
- Category: Bank reconciliation
- Canonical: https://www.precisebankflow.com/blog/bank-reconciliation-template-google-sheets

> A bank reconciliation template Google Sheets can run: FILTER and QUERY matching, a self-checking balance block, sharing permissions and version history.
A bank reconciliation template in Google Sheets needs four tabs: statement lines, ledger lines, reconciling items and a summary. FILTER pulls the unmatched items onto their own list, QUERY totals reconciling items by type, and a balance block proves the adjusted bank and book figures agree. Sharing settings and version history then record who prepared and approved it.

## What tabs does a Google Sheets bank reconciliation template need?

A Google Sheets bank reconciliation template works best with four tabs: Statement, Ledger, Items and Summary. Statement and Ledger hold the raw lines for the month. Items lists every reconciling item with its side and type. Summary holds the balance block and the sign-off. Each tab has one job, so a broken formula is easy to trace.

The layout below is an example. Column letters are suggestions, and you can rename the tabs, but the formulas later in this post assume these names.

| Tab | Columns | Filled by |
| --- | --- | --- |
| Statement | A Date, B Description, C Amount (deposits positive, withdrawals negative), D Check or reference, E Status | Pasted or imported from the bank statement |
| Ledger | A Date, B Description, C Amount (same sign rule), D Check or reference, E Status | Exported from the accounting system |
| Items | A Type, B Side (Bank or Book), C Amount, D Note, E Source line | Built from the open lines on Statement and Ledger |
| Summary | Balance block, footing check, sign-off fields | Formulas only, plus names and dates |

Keep one sign convention across both line tabs. If deposits are positive on the statement and negative in the ledger, every match will fail, and the template will look broken when only the export is.

Put the statement's printed opening and closing balances at the top of the Summary tab, typed by hand from the PDF. They are the two figures the rest of the sheet is tested against, so they should never come from a formula.

## Matching bank lines with FILTER in a Google Sheets template

FILTER returns the rows of a range that pass a condition, which makes it the natural way to list unmatched items. Mark each statement and ledger line as Matched or Open in column E, then let FILTER pull every Open line onto the Items tab. The list rebuilds itself whenever a status changes.

### Step 1: a status formula on each line

On the Statement tab, a simple status test for cell E2 checks whether the ledger holds a line with the same amount and reference:

```
=IF(COUNTIFS(Ledger!$C$2:$C, C2, Ledger!$D$2:$D, D2) > 0, "Matched", "Open")
```

Use the mirror image on the Ledger tab. For lines with no reference, such as card payments, swap the reference test for a date window, for example the ledger date within three days of the statement date. This test is crude on purpose. Repeated amounts, such as two identical rent payments, can both show Matched against one ledger line, so review any amount that appears more than once by hand.

### Step 2: FILTER the open lines

On the Items tab, one formula lists every open statement line:

```
=IFNA(FILTER(Statement!A2:D, Statement!E2:E = "Open"), "No open statement lines")
```

The IFNA wrapper matters. According to [Google's documentation for the FILTER function](https://support.google.com/docs/answer/3093197), FILTER returns #N/A when no rows meet the condition. A month with nothing open is good news, and the sheet should say so in words instead of showing an error that looks like a fault.

Repeat the formula for the Ledger tab a few rows lower. Statement lines with no ledger match are usually book-side items, such as fees, interest or an unrecorded payment. Ledger lines with no statement match are usually bank-side timing items, such as outstanding checks and deposits in transit.

## QUERY totals for the bank reconciliation template

QUERY runs a short query language over a range, so one formula can total every reconciling item by type. On the Summary tab it turns a long Items list into a few lines: outstanding checks, deposits in transit, fees, interest and errors, each with its total. That is the table a reviewer reads first.

Once each open line on the Items tab has a type and a side, this formula groups them:

```
=QUERY(Items!A1:C, "select A, B, sum(C) where A is not null group by A, B label sum(C) 'Total'", 1)
```

[Google's QUERY function reference](https://support.google.com/docs/answer/3093343) explains the syntax: a data range, the query text in quotation marks and an optional count of header rows. It also notes that each column should hold one type of data. If someone types "n/a" into the amount column, QUERY treats the minority values as empty, and the total quietly drops them. Keep the amount column numeric, and use the Note column for words.

### Example output

The values below are examples for a fictional March.

| Type | Side | Total |
| --- | --- | ---: |
| Outstanding check | Bank | (2,615.00) |
| Deposit in transit | Bank | 1,980.00 |
| Bank fee | Book | (42.00) |
| Interest | Book | 3.18 |

## A self-checking balance block in Google Sheets

A self-checking balance block is a short set of cells that computes both adjusted balances from the Items tab and shows, in words, whether they agree. It never takes a typed total. If someone overwrites a formula with a number, the check should fail, not pass.

An example block on the Summary tab:

| Cell | Label | Formula |
| --- | --- | --- |
| B2 | Balance per bank (typed from PDF) | 48,210.55 |
| B3 | Bank-side items | `=SUMIFS(Items!C:C, Items!B:B, "Bank")` |
| B4 | Adjusted balance per bank | `=B2 + B3` |
| B6 | Balance per books (typed from ledger) | 47,614.37 |
| B7 | Book-side items | `=SUMIFS(Items!C:C, Items!B:B, "Book")` |
| B8 | Adjusted balance per books | `=B6 + B7` |
| B10 | Difference | `=ROUND(B4 - B8, 2)` |
| B11 | Result | `=IF(B10 = 0, "Agrees", "Difference: " & TEXT(B10, "#,##0.00"))` |

With the example values, B4 is 47,575.55 and B8 is 47,575.55, so B11 reads "Agrees".

Add a second test that the statement itself foots: typed opening balance, plus the sum of the Statement amount column, less the typed closing balance, should round to zero. If it does not, a line is missing from the paste, and no amount of matching will fix it. Use ROUND in both tests, because sums of currency amounts can leave tiny floating-point remainders that make an equal comparison fail.

The block follows the adjusted-balance method, where both sides are brought to a common figure. [Bank reconciliation methods compared](/blog/bank-reconciliation-methods) explains the alternatives, if your reviewer expects a bank-to-book layout instead.

## Who should edit the Google Sheets bank reconciliation template?

The preparer should edit the month's copy of the template, and the reviewer should be able to see its history. Keep the master template locked to one owner. Each month, make a copy for the period, share it with the preparer as an editor, and protect the Summary formulas so they cannot be overwritten by accident.

A practical permission setup:

- **Owner:** the controller or engagement lead. Owns the master template and the folder of monthly copies.
- **Preparer:** editor on the current month's copy only.
- **Reviewer:** needs edit access to browse version history, so give it, and agree that the reviewer does not change figures, only the sign-off cells.
- **Everyone else:** view access, or none. A reconciliation lists account numbers and payees, so treat the link as confidential.

Protect the formula cells on the Summary and Items tabs. A protected range does not stop a determined editor, but it stops the most common accident, which is typing over a formula with the number it used to show.

## Version history as the template's sign-off trail

Version history in Google Sheets records who changed the file and when, so it can serve as the template's sign-off trail. Name a version when the preparer finishes, and another when the reviewer signs. The named versions mark exactly what each person saw at the moment they approved it.

[Google's help page on version history](https://support.google.com/docs/answer/190843) sets out the limits worth knowing. You can name up to 15 versions per spreadsheet. You need edit permission to browse earlier versions. Restoring an earlier version clears the cell-by-cell history of edits made before the restore, so copy the file instead of restoring when you need to keep the trail.

A naming pattern that reads well later:

1. "Prepared 2026-04-05 JL" when the preparer finishes.
2. "Reviewed 2026-04-07 MK" when the reviewer signs.
3. "Reopened 2026-04-09 JL, late fee found" if anything changes after sign-off.

Fifteen names fit a monthly copy easily. They would not fit a single sheet reused all year, which is another reason to make one copy per month.

## When a Google Sheets bank reconciliation template is the wrong tool

A Google Sheets bank reconciliation template is the wrong tool once volume, repeated amounts or review needs outgrow simple formulas. Hundreds of lines a month, many identical amounts, several accounts closing together or an auditor who needs evidence for every match all push a shared sheet past what it does well.

Signs it is time to change:

- The status formulas mark too many lines Matched on amount alone, and the hand review takes longer than the reconciliation.
- Checks are written in volume, and gaps in the check sequence need their own test. Gaps also point to physical controls over blank checks, covered in [check stock security](/blog/check-stock-security).
- The statement period does not line up with month-end, so each copy needs a roll-forward block. [Reconciling a bank statement that ends mid month](/blog/reconcile-a-bank-statement-that-ends-mid-month) shows that extra step.
- The account is a simple checking account with a register, and a sheet is more than it needs. [Reconciling a checking account](/blog/how-to-reconcile-a-checking-account) compares a register with a spreadsheet for that case.

When the sheet stops scaling, PreciseBankFlow reads the statement PDF, tests that it foots and lists unmatched lines with their evidence, then exports the result to Excel or PDF for the workpapers. Details are on the [PreciseBankFlow homepage](/).