Excel workbook guide

Bank Reconciliation Template for Excel

Download a generic Excel workbook to compare adjusted statement and book balances. Replace the invented inputs, review the formulas, and investigate any difference against your source records.

Download and prepare the workbook

Download bank-reconciliation-template.xlsx. The file contains one worksheet, Reconciliation, with a summary, two balance bridges, and four adjustment schedules. It is a standalone generic workbook; it does not import or extract statement transactions.

  1. Save a working copy and keep your source statement and book records available. Use one account, one currency, and the same period for both sides. The workbook assumes no currency; amounts display two decimal places without a currency symbol.
  2. Replace every seeded synthetic value with reviewed inputs, or clear unused example amounts. Blue text on pale amber cells identifies editable amounts; dark text identifies formulas. Keep the formulas when replacing the example.
  3. Enter the statement ending balance in B14 and the book balance for the same period in E14. A numeric zero is a valid balance. Missing or nonnumeric starting balances show Enter balances instead of Matched.
  4. Complete the adjustment schedules from source evidence, review the adjusted balances and difference, and investigate every unresolved item. Do not add an invented adjustment to force a match.

Statement-side and book-side adjustments

On the statement side, deposits in transit are recorded deposits that are not yet reflected in the statement balance. Outstanding payments are recorded payments that have not yet appeared on the statement. The workbook adds deposits in transit and subtracts outstanding payments from the statement ending balance.

On the book side, start with the book balance and enter source-confirmed credits and fees/debits that are not yet reflected there. The workbook adds book credits and subtracts book fees/debits. These fields describe the worksheet arithmetic; they do not determine which items belong in your records.

Enter deposits, payments, credits, and fees as positive amounts in their respective schedules; the bridge supplies the addition or subtraction. B17 and E17 are signed correction inputs: a positive correction increases its side and a negative correction decreases it. Verify the sign and supporting source for every correction, and avoid counting an item twice.

Synthetic worked example

All seeded values are invented and replaceable. They do not represent a real account or a bank-specific format. No currency is assumed.

Invented, currency-neutral workbook values
Input or resultStatement sideBook side
Starting balance1,112.501,167.50
Add deposits in transit / book credits100.000.00
Subtract outstanding payments / book fees/debits50.005.00
Signed correction0.000.00
Adjusted balance1,162.501,162.50

Statement side: 1,112.50 + 100.00 − 50.00 + 0.00 = 1,162.50. Book side: 1,167.50 + 0.00 − 5.00 + 0.00 = 1,162.50. The difference is 0.00 and the status is Matched. Clear or replace these example values before checking your own records.

Formulas and the six-row schedule limit

Each adjustment schedule reserves six rows. Deposits in transit total into B15 with =SUM(B24:B29); outstanding payments total into B16 with =SUM(E24:E29). Book credits total into E15 with =SUM(B35:B40); book fees/debits total into E16 with =SUM(E35:E40).

The adjusted statement balance in B19 is =IF(ISNUMBER(B14),B14+B15-B16+SUM(B17),"Enter balance"). The adjusted book balance in E19 is =IF(ISNUMBER(E14),E14+E15-E16+SUM(E17),"Enter balance").

With numeric starting balances, B10 calculates =ROUND(B19-E19,2). The full difference formula is =IF(OR(NOT(ISNUMBER(B14)),NOT(ISNUMBER(E14))),"Enter balances",ROUND(B19-E19,2)). E10 shows Matched when this rounded difference is zero, otherwise Unmatched; missing starting balances show Enter balances. Matching is evaluated at two-decimal precision.

For more than six items in a schedule, insert rows within that schedule and extend its SUM range to include every added amount. Check the formulas after inserting rows; do not assume that typing below the original range will be included. Confirm that the final row changes the schedule total and adjusted balance, and recheck the difference after editing. Cell addresses above describe the unmodified workbook.

Review the result against your sources

A Matched result only validates the entered arithmetic at the workbook’s two-decimal precision. It does not prove completeness, correct dates or descriptions, or that adjustments are appropriate. Missing and duplicate items can offset one another. Compare each input with its source and retain unresolved differences for review.

If the result is Unmatched, confirm account, currency, period, starting balances, amount signs, duplicate items, omitted items, and formula ranges. Inspect underlying precision as well as displayed values. Use the balance-check guide for statement arithmetic and discrepancy checks.

No bank-specific format has been independently validated against real statements. This generic workbook does not establish coverage of any bank’s layout. These instructions explain the file’s operation and are not financial or accounting advice.

Related guides and privacy

If you need transaction rows from a PDF, open the converter, review its output against the original, and use confirmed values in your workbook. Review the storage and sharing settings of any spreadsheet application you use with sensitive records.