How to Reconcile a Bank Statement in Excel

Bank reconciliation is the process of matching your internal financial records against your bank statement to ensure they agree. Whether you're managing personal finances or business books, reconciling in Excel is straightforward once you know the process.

Why Reconcile?

Your bank balance and your records rarely match perfectly at any given moment. The differences are usually due to:

• Outstanding checks — Checks you've written that haven't been cashed yet.
• Deposits in transit — Money you've deposited that hasn't cleared yet.
• Bank fees — Charges you haven't recorded yet.
• Interest earned — Credits you haven't recorded.
• Errors — Mistakes in either your records or the bank's.
• Unauthorized transactions — Fraud or duplicate charges.

Reconciliation catches all of these. Businesses should reconcile monthly; individuals should reconcile at least quarterly.

Step 1: Get Your Data into Excel

You need two sets of data:

1. Your bank statement — If it's a PDF, convert it to Excel using BankParse. If your bank offers CSV download, import that directly.
2. Your internal records — Your checkbook register, accounting software export, or manual transaction log.

Put each dataset on its own worksheet tab in the same Excel workbook. Label them "Bank Statement" and "My Records."

Step 2: Sort Both Datasets

Sort both worksheets by date, then by amount. This makes visual comparison much easier. To sort in Excel:

1. Select all data (Ctrl+A).
2. Go to Data > Sort.
3. Sort by Date (ascending), then add a second level sorting by Amount.

Step 3: Create the Reconciliation Worksheet

Create a third worksheet called "Reconciliation" with this structure:

Bank Statement Ending Balance:         $X,XXX.XX

Add: Deposits in transit
  [Date]  [Description]  [Amount]
  Total deposits in transit:           $XXX.XX

Less: Outstanding checks
  [Date]  [Check #]  [Amount]
  Total outstanding checks:            $XXX.XX

Adjusted Bank Balance:                 $X,XXX.XX

---

Book Balance (Your Records):           $X,XXX.XX

Add: Interest earned (not yet recorded)
  Total interest:                      $XX.XX

Less: Bank fees (not yet recorded)
  Total fees:                          $XX.XX

Less: NSF checks / returned items
  Total returned:                      $XX.XX

Adjusted Book Balance:                 $X,XXX.XX

The adjusted bank balance and adjusted book balance should be equal. If they are, you're reconciled.

Step 4: Match Transactions

Go through each transaction on your bank statement and find its match in your records. A helpful technique:

1. Add a "Matched" column to both worksheets.
2. For each bank transaction, search your records for the same date and amount.
3. When you find a match, mark both with an "X" or the matching row number.
4. After going through everything, unmatched items on either side need investigation.

Excel VLOOKUP for Matching

You can use VLOOKUP to speed up matching. In your bank statement worksheet, add a formula to check if each amount exists in your records:

=IF(ISNUMBER(MATCH(B2,'My Records'!B:B,0)),"Match","No Match")

This checks if the amount in cell B2 exists anywhere in column B of your records sheet. For more precise matching, combine date and amount into a key:

=IF(ISNUMBER(MATCH(A2&B2,'My Records'!A:A&'My Records'!B:B,0)),"Match","No Match")

Note: This array formula needs to be entered with Ctrl+Shift+Enter in older Excel versions.

Step 5: Investigate Unmatched Items

After matching, you'll have items on one side that don't appear on the other:

On the bank statement but not in your records:

• Bank fees — Record them in your books.
• Interest earned — Record it in your books.
• Unauthorized transactions — Report to your bank immediately.
• Auto-pay charges you forgot to record — Record them.

In your records but not on the bank statement:

• Outstanding checks — Normal, they just haven't cleared yet.
• Deposits in transit — Also normal if made near the end of the period.
• Recording errors — Wrong amount, wrong date, or duplicate entry in your records.

Step 6: Adjust and Balance

After investigating all discrepancies:

1. Record any bank fees and interest you hadn't captured.
2. Correct any errors in your records.
3. Report any unauthorized transactions.
4. List outstanding checks and deposits in transit on your reconciliation worksheet.
5. Calculate both adjusted balances — they should now match.

Useful Excel Formulas for Reconciliation

• Sum deposits: =SUMIF(C:C,">0")
• Sum withdrawals: =SUMIF(C:C,"<0")
• Count unmatched: =COUNTIF(D:D,"No Match")
• Find duplicates: =COUNTIF(B:B,B2)>1
• Running balance: =E1+C2 (where E1 is the previous balance and C2 is the transaction amount)

Tips for Easier Monthly Reconciliation

• Reconcile every month — The longer you wait, the harder it gets. Small discrepancies are easy to track down when they're fresh.
• Keep a template — Build your reconciliation workbook once and reuse it monthly. Just update the data.
• Record transactions daily — If you record purchases as they happen, matching at month-end is fast.
• Use conditional formatting — Highlight unmatched items in red so they stand out.
• Save completed reconciliations — Keep each month's reconciliation file for your records. Name them consistently: "Reconciliation-2026-01.xlsx"

Bank reconciliation doesn't have to be painful. With your statement data in Excel (convert PDFs with BankParse if needed) and a systematic matching process, you can reconcile most accounts in 15-30 minutes per month.