The slow part of a bank reconciliation is rarely the matching. It is getting the bank statement into Excel in the first place. If your statement is a PDF, you cannot reconcile until the dates, descriptions, and amounts sit in their own columns as real numbers. Once both sides are clean, Excel does the matching for you with a lookup and a flag, and the balance ties out in a few minutes.
This guide walks through the whole job the way a working accountant or bookkeeper does it: convert the statement, line it up against the books, match what agrees, and chase what does not. Drop your PDF into the converter at the top of this page first, then follow along. For the firm-side workflow across many clients, see PDF to Excel for accountants.
How do you reconcile a bank statement in Excel?
Put the bank statement on one sheet and your cash ledger on another, each with Date, Description, and Amount columns. Match every transaction between the two with a lookup formula, flag anything that does not match, then adjust for timing items like deposits in transit and outstanding checks. When the adjusted bank balance equals the adjusted book balance, the account is reconciled.
The five steps below cover it in order.
Step 1: Get the statement into clean columns
You need the bank statement as a table, not as text. Convert the PDF to Excel so each transaction lands in its own row with the amount stored as a real number, not a string with a dollar sign attached. That last point matters: if the amounts come in as text, no formula will add or match them, and you will spend the reconciliation cleaning cells instead of clearing items. A converter that keeps numbers numeric, like the accurate PDF to Excel converter, saves that step. For statements specifically, the bank statement to Excel page handles the common layouts, and a dedicated bank statement converter with per-bank templates covers the trickier formats.
Put the converted statement on a sheet named Bank and your accounting records on a sheet named Books. Make both cover the same date range, use one date format on each, and keep deposits positive and payments negative so the signs line up.
What formula is used for bank reconciliation in Excel?
The core tool is a lookup that checks whether each amount on one sheet appears on the other. XLOOKUP or VLOOKUP finds the match, and an IF statement labels the result. A simple version is =IF(ISNUMBER(MATCH(C2,Bank!$C:$C,0)),"Match","Review"), which marks a book transaction Match when its amount exists on the bank sheet and Review when it does not. SUMIF is useful for totaling a batch of deposits or fees to compare against a single statement line.
Matching on amount alone can produce false hits when two different transactions share a value, so for tighter control match on a helper column that joins date and amount, such as =A2&"|"&C2, and look that up instead. It catches the case where a $50 fee and a $50 refund would otherwise both flag as matched.
How do I match transactions between my bank statement and my books?
Add a status column to each sheet and fill it with the lookup above so every line reads Match or Review. Then turn on Conditional Formatting and highlight the Review rows, which makes the unmatched items jump out instead of hiding in a long list. Work down only the highlighted rows. Most will be timing differences or a transaction you have not booked yet, and a handful will be genuine errors worth correcting at the source.
For a recurring client you reconcile every month, a PivotTable that groups the Review rows by type or date gives you a quick read on what is still open without scrolling the whole sheet.
What is the difference between deposits in transit and outstanding checks?
A deposit in transit is money you recorded in the books that has not yet cleared the bank, so it shows on your side but not the statement. An outstanding check is a payment you issued and recorded that the recipient has not cashed, so it also shows in the books but not on the bank. Both are timing differences, not errors. You add deposits in transit to the bank balance and subtract outstanding checks from it when you build the adjusted figure.
How do I tie out the adjusted balance?
Build a short summary block. Start with the ending balance from the bank statement, add deposits in transit, and subtract outstanding checks to get the adjusted bank balance. Separately, start with the book balance and adjust for items the bank recorded that you had not, such as interest earned, bank fees, or a returned check. When the adjusted bank balance equals the adjusted book balance, the account is reconciled and you can lock the period. If they differ, the gap equals the transactions still sitting in your Review column.
How do I find a discrepancy in a bank reconciliation?
Take the difference between the two adjusted balances and search for that exact amount first, since a single missed transaction often explains the whole gap. If no single line matches, halve the difference and look for that figure, because a number entered with the wrong sign creates a gap of twice its value. Then check for transposed digits, which always produce a difference divisible by nine. Working those three checks in order resolves most discrepancies before you have to compare the sheets line by line.
Can Excel reconcile a bank statement automatically?
Excel can automate the matching and the flagging, but it does not decide what an unmatched item means, so it is not fully hands-off. The lookup and conditional formatting clear the agreeing transactions for you, leaving only the exceptions to judge. That is usually enough to cut a manual reconciliation down to a fraction of the time. If you reconcile inside accounting software instead, you can skip the spreadsheet and convert the statement straight to a QBO file for QuickBooks, or turn an exported CSV into a QBO file for import.
Putting it together
Reconciliation in Excel is four moves: get both sides into clean numeric columns, match with a lookup, highlight and clear the exceptions, then adjust for timing items until the balances agree. The piece that trips most people up is the first one, because a PDF statement does not paste into a usable table. Convert it cleanly and the rest is fast. If you do this across several clients, the accountant workflow and the bookkeeper workflow show how to batch the conversions so month-end does not turn into an afternoon of data entry.