How to Reconcile a Bank Statement in Excel: Match Transactions and Prove the Balance

Here is how to reconcile a bank statement in Excel: put the statement transactions on one sheet and your own records on another, match each line by amount and date, then explain every unmatched item until the adjusted bank balance equals the adjusted book balance. The slowest part is usually getting the statement into Excel at all, so start by turning the PDF into rows with our free bank statement to Excel converter, which runs in your browser.

After that, a handful of formulas do most of the matching. This guide walks through the sheet layout, the formulas, the adjustments and the errors we see most often.

One note before we start. We build file tools and lead lists, not accounting software, so this is a general spreadsheet method. Ask your accountant how your business should record any adjustments it uncovers.

What does reconciling a bank statement in Excel mean?

Bank reconciliation is the check that your own record of cash (a cash book, a ledger or a simple spending sheet) agrees with what the bank says happened. The two rarely match on the day the statement closes, because of timing.

• Deposits in transit: money you recorded that the bank had not credited by the statement date.
• Outstanding checks: payments you recorded that had not cleared the bank yet.
• Bank-only items: fees, interest and automatic debits the bank booked that you have not recorded.
• Errors: a typo in your books, a duplicate entry, or occasionally a bank mistake.

Excel suits this job because it can compare hundreds of rows in seconds. The catch is that every number must be a real number, not text, or the formulas silently fail.

How do you get your bank statement into Excel first?

You need the transactions as rows with a date, a description and an amount. There are three common routes, and the right one depends on what your bank gives you.

1. Bank export: many online banking portals offer CSV, Excel, QFX or OFX downloads. If yours does, use CSV or Excel. It is the cleanest source.
2. Our browser converter: if you only have a PDF, drop it into the free PDF statement converter. It reads the PDF on your device, handles password-protected files, and gives you an Excel or CSV download.
3. Excel Get Data from PDF: Excel for Microsoft 365 on Windows can import PDF tables through Power Query’s PDF connector. It works well on simple layouts but often splits one statement across several tables.

Our step-by-step guide on converting a bank statement to Excel compares those routes in depth, including scanned statements.

What does the LeadCanal converter give you for reconciliation?

We built the converter around the columns a reconciliation needs. Here is what the Excel file contains:

• Transactions: one row per transaction with Date, Description and a column for each money column found on the statement, such as Debit, Credit and Balance. Amounts are stored as numbers with a filter on the header row.
• Amount (+/-): when both debits and credits are found, a signed amount column is added. Money in is positive and money out is negative, which makes matching far easier.
• All text: every line of every page split into cells, so nothing is lost if your bank uses an unusual layout.

The results card also shows the transaction count and the money in and money out totals, so you can check them against the statement summary before you open Excel.

How to reconcile a bank statement in Excel, step by step

Set up one workbook with three sheets: Bank, Books and Recon. On both Bank and Books, keep the same columns in the same order: Date in column A, Description in B, Amount in C (positive for money in, negative for money out), and a Match column in D.

1. Load the bank data. Paste the Transactions sheet from your converted statement into Bank. If your file has separate Debit and Credit columns instead of a signed amount, add one with =Credit-Debit for each row.
2. Load your books. Export the same date range from your ledger or cash book, plus a few days either side, and paste it into Books in the same column order.
3. Check the statement adds up. In Recon, enter the opening and closing balances from the statement and test =Opening+SUM(Bank!C:C). It must equal the closing balance. If it does not, a row is missing or doubled, and matching will not help until you fix it.
4. Flag matches on the bank side. In Bank!D2 use =COUNTIFS(Books!$C:$C,C2,Books!$A:$A,">="&A2-3,Books!$A:$A,"<="&A2+3) and fill down. This counts book entries with the same amount within three days. A 0 means the line is only in the bank.
5. Flag matches on the book side. Use the mirror formula in Books!D2, pointing at the Bank sheet. A 0 here means the entry is only in your books.
6. Filter for zeros and counts above 1. Zeros are your reconciling items. A count of 2 or more means two possible matches, such as two identical card payments, so check those by description.
7. Classify each leftover. Label every unmatched row as a deposit in transit, outstanding check, bank fee, interest, or error.
8. Build the summary and prove it. Work out both adjusted balances in Recon, as shown in the next section. The reconciliation is finished only when the difference is exactly zero.

If you prefer a lookup, =XLOOKUP(C2,Books!$C:$C,Books!$B:$B,"Not found") returns the matching description, which helps when you review by eye. XLOOKUP needs Excel 2021, Excel for Microsoft 365 or Excel for the web. COUNTIFS works in every current version, which is why we lead with it.

Which adjustments go on the bank side and which on the book side?

This is the part people mix up most. The rule is simple: an item goes on the side that has not recorded it yet.

In the Recon sheet that becomes two formulas:

• Adjusted bank balance = statement closing balance + deposits in transit – outstanding checks, plus or minus bank errors.
• Adjusted book balance = book balance + interest + unrecorded deposits – fees – returned checks, plus or minus book errors.

Put the difference in its own cell with =AdjustedBank-AdjustedBook and add conditional formatting that turns it red when it is not zero. That one cell tells you whether you are done.

Which Excel tools are best for matching transactions?

A worked example from our own month-end

Here is the way we run it on our own operating account, with simple numbers for illustration.

We download the month’s statement as a PDF, because that is the format our bank emails. We drop it into the browser-based statement converter, and the results card shows the transaction count plus money in and money out. We compare those totals with the summary box on page one of the PDF before doing anything else.

Next we paste the Transactions sheet into Bank and our ledger export into Books. Say the statement closes at 12,400.00 and our books show 11,985.00. COUNTIFS leaves two bank-side zeros and two book-side zeros:

• A client payment of 800.00 we recorded on the 31st that reached the bank on the 2nd (deposit in transit).
• A supplier check for 1,250.00 that had not cleared (outstanding check).
• A monthly service fee of 25.00 and a card fee of 10.00 we had not recorded (book side).

Adjusted bank balance: 12,400.00 + 800.00 – 1,250.00 = 11,950.00. Adjusted book balance: 11,985.00 – 25.00 – 10.00 = 11,950.00. The difference cell shows zero, so we record the two fees and save the workbook with the statement month in its name.

We keep the receipts for that month as phone photos. Turning them into a single file with the JPG to PDF combiner keeps the backup next to the workbook, and the PDF compressor shrinks it if it needs to go by email.

Is it safe to convert bank statements online?

A statement shows your account number, balance, salary and who you pay. Many online converters upload the file to their servers to process it, and you rarely know how long it is kept.

Our converter reads the PDF inside your browser, so the statement is never uploaded. If the PDF has a password, that stays on your device too, and nothing is stored by LeadCanal. For any tool you use, check whether it uploads the file before you drop a statement into it. The FTC’s guide to identity theft warning signs explains why account details deserve that care.

Troubleshooting: why won’t my reconciliation balance?

• Formulas return 0 for everything: dates or amounts are stored as text. Use Data, then Text to Columns, or =VALUE() and =DATEVALUE() in a helper column. Our converter keeps dates exactly as printed, so check the date format matches your system settings.
• The difference is divisible by 9: this often points to transposed digits, such as 54 typed as 45.
• The difference equals one transaction twice: a sign is flipped, so a payment was entered as money in.
• Opening plus transactions does not equal closing: a row is missing from the import. Look at the All text sheet for lines that did not start with a date.
• Multi-line descriptions are split: our converter joins them, but bank CSVs sometimes do not. Merge them before matching.
• A scanned statement shows no text: scans are images. Download a digital PDF or CSV from online banking instead.
• The year is too big for one sheet: keep one sheet per month, or break a long export into monthly files with the CSV splitter.

Frequently asked questions

How to reconcile a bank statement in Excel quickly?

Get the statement into Excel as rows, put your records on a second sheet, flag matches with COUNTIFS, then explain each unmatched row. The free converter at LeadCanal handles the first step in your browser.

What formula is used for bank reconciliation in Excel?

COUNTIFS is the most practical, because it matches on amount and a date window. XLOOKUP or MATCH also work for exact amounts. The final check is simply adjusted bank balance minus adjusted book balance.

Can I reconcile without a bank statement?

You can compare against the transaction list in online banking, but a statement gives fixed opening and closing balances. Without them, the period has no agreed end point.

How often should I reconcile my bank account?

Most people do it monthly, when each statement arrives. Busy accounts are often reconciled weekly so problems surface sooner.

Does Excel have a bank reconciliation template?

Many accounting sites offer free templates. The three-sheet layout in this guide works as your own template, and you can reuse it each month.

Can I use Google Sheets instead of Excel?

Yes. COUNTIFS works the same way in Google Sheets, and XLOOKUP is available there too. Import the CSV download from the converter.

Why are some transactions missing after converting a PDF?

Rows are detected by the date at their start. Anything the converter could not place is still on the All text sheet, so copy it across and recheck the statement math.

Conclusion: key takeaways

Learning how to reconcile a bank statement in Excel comes down to clean data, a matching formula and a clear rule for each adjustment. Get the first part right and the rest takes minutes.

• Convert the PDF statement to rows first, with signed amounts stored as real numbers.
• Prove opening balance plus transactions equals the closing balance before matching.
• Flag matches with COUNTIFS on amount and a short date window on both sheets.
• Adjust the bank side for timing items and the book side for fees, interest and errors.
• Finish only when the difference between both adjusted balances is exactly zero.

Ready to start? Open the free bank statement converter, or browse all our free file tools.

Editorial policy

We write guides about tasks we handle ourselves, and we test the steps before we publish them. Formulas and menu names are checked against Microsoft’s documentation. We update a guide when Excel or our tools change. This article explains general spreadsheet methods and is not accounting, tax or financial advice.

Content transparency

This guide was researched with keyword data on what people ask about reconciling statements and checked against official Microsoft documentation. LeadCanal built and offers the free bank statement converter mentioned here, along with the other file tools linked in this article. We are not paid by any bank or software vendor named.

About the author

This guide comes from the LeadCanal team. We build verified B2B contact lists, enrich CRM data and run cold email infrastructure every day, which means we clean spreadsheets, CSV exports and PDF reports constantly. We built our browser-based file converters to handle those files without uploading them anywhere.