Bank Reconciliation Template for Excel

Download the Excel bank reconciliation template

Download the reusable XLSX template below. It includes an Instructions sheet, a blank Reconciliation sheet, and a completed Sample sheet. The blank sheet calculates bank-side adjustments, book-side adjustments, adjusted balances, the remaining difference, and a status message.

You do not need to enter minus signs. Enter each source amount as a positive number and choose its adjustment type from the dropdown. The signed-adjustment formula uses the amount's absolute value and applies the correct direction for that type.

Download the bank reconciliation Excel template

Three sheets keep the blank template separate from the example

Read Instructions first, then work in Reconciliation. The Sample sheet uses synthetic August figures and stays available as a reference. Keeping the sample out of the blank sheet reduces the chance that an example amount survives in a live reconciliation.

Save an untouched copy before entering company records. Use a new copy for each bank account and statement period so outstanding items, cutoff dates, and evidence references do not mix across accounts.

Enter the account and two starting balances

Enter the account name, statement period end, currency, and preparer at the top of the Reconciliation sheet. Use the ending balance printed on the issued bank statement for the bank side. Use the cash balance in your ledger before statement-only items and book corrections for the book side.

The status remains 'Enter starting balances' until both balance cells contain values. This prevents an empty sheet from presenting a reconciled result. Check that both balances use the same account, currency, and cutoff date before you add adjustments.

Classify bank-side adjustments

Use the bank-side table for timing items already recorded in the books and for documented bank errors. Enter one item per row with an evidence ID, date, description, source amount, and source reference. The workbook turns the selected type into a signed adjustment.

OpenStax identifies deposits in transit and outstanding checks as common timing differences. Those items have already reached the books, so recording them again would duplicate the activity. Bank errors remain documented on the reconciliation while the institution investigates them.

Classify book-side adjustments

Use the book-side table for activity the bank recorded before the books and for errors in the ledger. Bank interest, service fees, returned customer payments, automatic debits, and recording mistakes belong here when they have not reached the books.

The workbook calculates the amount needed to reconcile, but it does not create journal entries. Record each supported book adjustment in the accounting system using your chart of accounts and approval process. Keep the ledger entry reference beside the reconciliation evidence.

The formulas stay visible and auditable

Excel's SUM function totals the signed-adjustment ranges. Microsoft recommends range-based SUM formulas instead of typing a long series of cell additions because the range is easier to read and less prone to typing errors.

The template uses separate calculations for the bank and book sides. It then subtracts the adjusted book balance from the adjusted bank balance. The status reports Reconciled within one cent and Investigate difference outside that tolerance.

Use the sample sheet to test the logic

The Sample sheet starts with a $12,480 statement balance and a $13,665 book balance. Its bank-side rows produce a net $570 increase. Its book-side rows produce a net $615 decrease. Both adjusted balances equal $13,050, and the displayed difference is $0.00.

Change one blue source amount in a copy of the sample. The adjusted balance, difference, and status should respond. Restore the amount before using the sheet as a reference. The separate worked-example guide explains the supporting facts and journal entries behind these numbers.

A zero difference still needs a completeness review

The template totals the rows you provide. Two missing items of equal and opposite value can cancel each other while the difference remains $0.00. Compare transaction counts, trace each adjustment to a source record, and inspect prior-period outstanding items before sign-off.

The workbook provides 15 bank-side rows and 15 book-side rows. If you need more, insert rows inside the relevant adjustment block, copy the signed-adjustment formula, and confirm that the summary range includes the new rows. Recheck the result after any structural edit.

• Confirm the statement and ledger cover the same account and cutoff.
• Give each adjustment a source reference that another reviewer can locate.
• Record book-side entries before accepting the adjusted book balance.
• Carry unresolved timing items into the next period with their evidence IDs.
• Investigate stale outstanding items instead of rolling them forward without review.

Populate the template from reviewed transaction rows

StatementMint converts entitled bank-statement pages into CSV and formatted Excel with Date, Description, and signed Amount. Review the export against the issued PDF, then use the transaction rows to match your ledger and identify supported reconciliation items.

StatementMint does not classify reconciliation adjustments, post entries, or approve the finished workpaper. The downloadable template provides the calculation structure after you determine which source-backed items belong on each side.

Frequently asked questions