Build a bank rec template in Excel that actually balances. Convert the statement PDF into clean rows first, then use the two column format and the matching formulas below. Start free, no credit card.
Last updated July 2026
Upload your bank statement
Drop file here or click to upload
PDF, JPG, PNG, BMP, HEIC, TIFF, MT940
Uploading...
A bank reconciliation statement has two columns that are calculated independently and must end at the same number. On the bank side you start with the balance per the bank statement, add deposits in transit, subtract outstanding checks, and correct any bank errors. On the book side you start with the balance per your books, add interest earned and notes the bank collected, subtract service charges and NSF checks, and correct any book errors. When the adjusted bank balance equals the adjusted book balance, the account is reconciled. One rule catches most mistakes: only the book side items become journal entries. Deposits in transit and outstanding checks never do, because they clear on their own.
Most reconciliation templates are fine. The data going into them is the problem. A bank statement PDF cannot be sorted, filtered, or matched, so the numbers get retyped, and retyped numbers are where the difference comes from.
A template needs one transaction per row. A PDF gives you a page image. Copying and pasting from it collapses columns, merges descriptions into amounts, and silently drops the running balance.
Gartner surveyed 497 controllership professionals in 2023 and found 18 percent make financial errors at least daily and 59 percent make several a month. A single transposed digit is enough to break a reconciliation.
The bank labels columns from its own point of view, so a deposit shows as a credit. Paste that straight into a book side column and every sign is backwards.
Check number is the natural key for matching outstanding checks. Most quick copy and paste jobs lose it, and then outstanding checks have to be matched by amount, which is ambiguous.
That is the signature of a transposition error, two adjacent digits reversed. It tells you a number was mistyped somewhere in hundreds of rows, and nothing more than that.
An auditor asks how you arrived at an adjusted balance. Hand keyed cells with no source rows behind them are hard to defend and slow to re-perform.
Convert the statement PDF into a spreadsheet, paste the rows into the bank tab of your template, and let formulas do the matching. The reconciliation then tests your books, not your typing.
Date, description, debit, credit, and running balance land in their own columns, in statement order, ready to paste into the bank tab of any bank rec template.
Where the statement prints a check number the converter keeps it as its own field, so outstanding checks match on reference rather than on amount.
The balance column is captured rather than recalculated, which gives you an independent check that no row was dropped in the middle of the statement.
Convert a year of statements in one pass when you are catching up several unreconciled months, and reconcile them in order.
Download XLSX for the template directly, CSV to import into accounting software, or JSON when a system consumes the data.
256-bit encryption on every upload, and you can delete your files whenever you want.
The same four steps every US accounting textbook teaches, with the data entry removed.
Upload the PDF statement above and download XLSX. You get dated rows with amounts and the running balance already separated into columns.
Tip: One account per file.
Put statement rows on one tab and your ledger rows on another. Keep a third tab for the two column reconciliation summary.
Tip: Three tabs beats one crowded sheet.
Use MATCH or XLOOKUP to mark each row Matched or Unmatched, then conditional formatting to color the unmatched ones. What stays unmatched is your reconciling items.
Tip: Match on check number first.
Total the unmatched deposits and checks onto the bank side, post fees, interest, and NSF items to the book side, and confirm the two adjusted balances agree.
Tip: Only book side items get journal entries.
Anyone who has to prove that the cash number on the balance sheet is the cash number at the bank, without paying for a reconciliation platform to do it.
Reconciling a dozen client accounts a month, often from PDFs the client forwarded rather than a live bank feed.
Doing the monthly rec themselves in Excel because the business is too small to justify reconciliation software.
Reconciling operating, payroll, and merchant accounts on the way to a month end close, and needing a reviewable working paper.
Re-performing a client reconciliation and needing the underlying statement rows in a sortable form.
This is the standard two column adjusted balance form used in US practice. Both columns are computed independently. If they do not end on the same number, the account is not reconciled.
| Bank side | Book side |
|---|---|
| Balance per bank statement | Balance per books (general ledger cash) |
| Add: deposits in transit | Add: interest earned |
| Deduct: outstanding checks | Add: notes or receivables collected by the bank |
| Add or deduct: bank errors | Deduct: bank service charges and fees |
| Deduct: NSF (returned) checks | |
| Add or deduct: book errors | |
| = Adjusted balance per bank | = Adjusted balance per books |
The placement rule that keeps people out of trouble is to put the item where it is not yet recorded. A deposit you have booked but the bank has not seen goes on the bank side. A fee the bank charged but you have not booked goes on the book side.
Only the book side items become journal entries. Service charges, interest, NSF checks, and book errors get posted. Deposits in transit and outstanding checks never do, because they correct themselves the moment they clear. Competitor templates get this wrong constantly, and it is the fastest way to double count cash.
| Column | What it holds | Why it matters |
|---|---|---|
| Date | Transaction date | Separates timing differences from real errors |
| Description | Payee or memo | Identifies fees and NSF items |
| Check or reference number | The natural matching key | Matches outstanding checks unambiguously |
| Deposits (money in) | Positive receipts | Keep separate from withdrawals to avoid sign errors |
| Withdrawals (money out) | Payments and fees | Same reason |
| Cleared | Y or N flag | Drives the SUMIF totals |
| Amount per bank / per books | The two sources | Their difference is the item to explain |
One caution that costs people an afternoon: the debit and credit columns on a bank statement are written from the bank point of view, which is the opposite of yours. Your deposit is the bank liability increasing, so it prints as a credit. Label your template columns money in and money out and the confusion goes away.
Total only the cleared items:
=SUMIF(StatusColumn, "Cleared", AmountColumn)
Flag each row as matched or unmatched against the bank tab:
=IF(ISNUMBER(MATCH(A2, Bank_Ref, 0)), "Matched", "Unmatched")
Pull the bank amount next to the book amount:
=XLOOKUP(A2, Bank_Ref, Bank_Amount, "Not found")
Test whether the reconciliation balances, rounded so stray cents do not raise a false alarm:
=IF(ROUND(AdjustedBank - AdjustedBooks, 2) = 0, "BALANCED", "OUT OF BALANCE")
Highlight unmatched rows with conditional formatting by selecting the range, choosing a formula rule, and entering =ISNA(MATCH($C2, Bank_Ref, 0)) with a red fill.
| Symptom | Likely cause | What to do |
|---|---|---|
| Difference divides evenly by 9 | Transposition error, two adjacent digits reversed | Scan amounts for reversed digits. It is a strong indicator, not proof |
| Difference is exactly twice an item | A deposit entered as a withdrawal, or a sign flip | Halve the difference and search for that amount |
| Difference equals a round fee | Service charge or NSF fee never posted to the books | Post it on the book side and journalize it |
| Bank balance higher than books | Outstanding checks not yet cleared | Deduct them on the bank side, no journal entry |
| Books higher than bank | Deposits in transit near month end | Add them on the bank side, no journal entry |
| An amount appears twice | Duplicate entry, often a CSV imported twice | Remove one and check the import log |
Reconcile at least monthly, on the bank statement cycle. High volume accounts and accounts with fraud exposure are reconciled weekly or daily. Monthly is the baseline internal control, and it is also the point at which a bank statement reconciliation is still small enough to investigate.
On retention, be careful with what you read online. The IRS does not set a retention period for bank reconciliations as a document type. It sets a period of limitations: keep records 3 years as the general rule, 6 years if you did not report income that you should have and it is more than 25 percent of the gross income shown on the return, 7 years if you file a claim for a loss from worthless securities or a bad debt deduction, and indefinitely if no return was filed. Employment tax records run at least 4 years. Many CPA firms recommend holding statements and reconciliations for 7 years as a conservative practice, which is a professional recommendation rather than an IRS requirement.
The template is the easy half. Getting trustworthy rows into it is the work. Upload the statement above and the converter returns the transactions as a spreadsheet, keeping the running balance and check numbers that copy and paste destroys. From there the running balance extraction gives you the independent check that no row went missing, and transaction categorization speeds up the coding that follows the rec.
If you would rather the software track the reconciliation for you, compare the options honestly on our bank reconciliation software page, which is candid about what BankXLSX does not do. Reconciling more than one account this month? See how to reconcile multiple bank accounts. When the reconciled data has to land in your books, the QuickBooks bank statement converter and the Xero bank statement converter write the import file. And once the cash accounts tie out, the rest of the month end close checklist can actually start.
Last updated July 2026
Two columns. The bank column starts with the balance per the bank statement, adds deposits in transit, subtracts outstanding checks, and corrects bank errors. The book column starts with the ledger cash balance, adds interest earned and notes collected, subtracts service charges and NSF checks, and corrects book errors. Both adjusted balances must be equal.
Compare the bank statement to your ledger and mark every transaction that appears on both. Adjust the bank balance for deposits in transit and outstanding checks. Adjust the book balance for fees, interest, and NSF items. Confirm the two adjusted balances agree, then journalize only the book side adjustments.
Use three tabs: bank statement transactions, ledger transactions, and a summary. Give each transaction tab columns for date, description, check number, money in, money out, and a cleared flag. On the summary tab build the two column adjusted balance form and add a cell that tests whether the two adjusted balances agree.
Convert or export the statement into rows, paste them beside your ledger rows, and match on check number and then amount. Whatever stays unmatched becomes a reconciling item. Put timing items on the bank side and bank originated items on the book side, then check that both adjusted balances land on the same number.
Most often a mistyped amount, a missing bank fee or NSF charge, a duplicate entry, or a deposit recorded as a withdrawal. If the difference divides evenly by 9, suspect a transposition error where two adjacent digits were reversed. If the difference is exactly twice an amount on the statement, look for a sign flip.
A deposit you have recorded in your books that the bank has not yet posted to your statement, usually because it was made near the end of the period. It is added to the bank balance on the reconciliation and it never needs a journal entry, because it corrects itself once the bank posts it.
A check you wrote and recorded in your books that the payee has not yet cashed, so it has not cleared the bank. It is deducted from the bank balance on the reconciliation. No journal entry is required. Checks outstanding for many months should be investigated and possibly voided.
At least monthly, aligned to the bank statement cycle, because monthly reconciliation is the baseline internal control most auditors expect. Accounts with high transaction volume or elevated fraud risk are reconciled weekly or even daily, since finding an unauthorized transaction sooner limits the loss.
The IRS sets no retention period for reconciliations specifically. It sets a period of limitations: generally 3 years, 6 years if you underreported income by more than 25 percent of gross income, and 7 years for a worthless securities or bad debt claim. Many CPA firms recommend 7 years as a conservative practice.
When a template is not enough, compare the software honestly.
What happens after the bank accounts tie out.
Keep the running balance the copy and paste drops.
Turn the statement PDF into spreadsheet rows.
Get started converting bank statements to spreadsheets.
USD
per month
billed as
$288 yearly
Choose speed vs accuracy when extracting
| Base AI Faster | 2,500 pages |
| Pro AI Best accuracy | 500 pages |
Scale statement conversion across your team with automation.
USD
per month
billed as
$888 yearly
Choose speed vs accuracy when extracting
| Base AI Faster | 10,000 pages |
| Pro AI Best accuracy | 2,000 pages |
Enterprise-grade bank statement conversion and controls.
USD
per month
billed as
$ yearly
Choose speed vs accuracy when extracting
| Base AI Faster | pages |
| Pro AI Best accuracy | pages |
