How to Reconcile Bank Statements in Excel

Sandra Vu

By Sandra Vu

Bank reconciliation compares your internal records against bank statement transactions to ensure they match. Excel provides flexible tools for this process. First, you'll need to understand what a bank statement is and how to convert it to Excel.

What Is Bank Reconciliation?

Reconciliation identifies differences between:

  • Bank statement - What the bank shows
  • Your records - What you recorded (checkbook, accounting system)

Differences may include:

  • Outstanding checks not yet cleared
  • Deposits in transit
  • Bank fees you haven't recorded
  • Errors (yours or the bank's)

Step 1: Prepare Your Data

Bank Statement Data

First, get your bank statement into Excel. See how to convert bank statements to Excel step by step.

  1. Convert PDF bank statement to Excel/CSV
  2. Clean up formatting—watch for common errors in bank statement conversion
  3. Ensure columns: Date, Description, Amount, Balance

Your Internal Records

Export or enter your records with matching columns:

  • Date
  • Description/Payee
  • Amount
  • Reference/Check number

Step 2: Set Up Your Reconciliation Worksheet

Create a workbook with these sheets:

  1. Bank Statement - Imported bank data
  2. Your Records - Your internal transactions
  3. Reconciliation - Matching and analysis

Reconciliation Sheet Structure

| Bank Balance (per statement)     | $10,000.00 |
| Add: Deposits in Transit         |    $500.00 |
| Less: Outstanding Checks         |   -$750.00 |
| Adjusted Bank Balance            |  $9,750.00 |
|                                  |            |
| Book Balance (your records)      |  $9,700.00 |
| Add: Interest Earned             |     $50.00 |
| Less: Bank Fees                  |    -$25.00 |
| Add/Less: Errors                 |     $25.00 |
| Adjusted Book Balance            |  $9,750.00 |

Step 3: Match Transactions Using VLOOKUP

Use VLOOKUP to find matching transactions.

Formula Example

In your bank statement sheet, add a column to check if the transaction exists in your records:

=IF(ISNA(VLOOKUP(A2,YourRecords!A:D,1,FALSE)),"NOT MATCHED","MATCHED")

Where:

  • A2 is the bank transaction amount or reference
  • YourRecords!A:D is your records range

Better: Match on Multiple Criteria

Use INDEX/MATCH with multiple criteria:

=IF(COUNTIFS(YourRecords!A:A,A2,YourRecords!C:C,C2)>0,"MATCHED","NOT MATCHED")

This checks if both date (A) and amount (C) match.

Step 4: Use Conditional Formatting

Highlight unmatched transactions automatically:

  1. Select your transaction range
  2. Home > Conditional Formatting > New Rule
  3. Use formula: =E2="NOT MATCHED" (assuming Match column is E)
  4. Set fill color to yellow or red

Now unmatched items stand out visually.

Step 5: Identify Outstanding Items

Outstanding Checks

Checks you wrote that haven't cleared:

  • In your records but not on bank statement
  • Reduce your available balance

Deposits in Transit

Deposits you made that aren't on the statement yet:

  • In your records but not on bank statement
  • Will increase bank balance when posted

Create Lists

Add separate sections listing:

Outstanding Checks:
| Check # | Date    | Payee        | Amount   |
| 1234    | 01/28   | Office Depot | $125.00  |
| 1235    | 01/29   | Utilities    | $200.00  |
| Total   |         |              | $325.00  |

Deposits in Transit:
| Date    | Description      | Amount   |
| 01/31   | Client Payment   | $500.00  |
| Total   |                  | $500.00  |

Step 6: Identify Bank Adjustments

Find items on bank statement not in your records. Understanding transaction categorization helps with this step.

  • Bank fees - Monthly fees, wire fees, NSF charges
  • Interest earned - Interest credited to account
  • Automatic payments - Subscriptions you forgot to record
  • Errors - Bank mistakes (rare but possible)

These need to be recorded in your books. Consider categorizing bank transactions automatically to streamline this.

Step 7: Complete the Reconciliation

Fill in your reconciliation summary:

Bank Balance (01/31/2026):              $10,000.00
Add: Deposits in Transit:                  $500.00
Less: Outstanding Checks:                 -$325.00
Adjusted Bank Balance:                   $10,175.00

Book Balance (01/31/2026):               $9,950.00
Add: Interest Earned:                       $25.00
Add: Error Correction:                     $200.00
Adjusted Book Balance:                   $10,175.00

Difference:                                  $0.00

If the difference is $0, you're reconciled.

Useful Excel Formulas for Reconciliation

Sum Unmatched Bank Items

=SUMIF(MatchColumn,"NOT MATCHED",AmountColumn)

Count Outstanding Checks

=COUNTIF(MatchColumn,"NOT MATCHED")

Find Duplicates

=IF(COUNTIF(A:A,A2)>1,"DUPLICATE","")

Common Reconciliation Issues

IssueCauseSolution
Small differenceRoundingCheck decimal places
Exact amount offMissed transactionLook for that exact amount
Large differenceTimingCheck outstanding items
Can't find matchDifferent descriptionsMatch on amount + date

Best Practices

  1. Reconcile monthly - Don't let it pile up. See how to automate monthly bank statement processing
  2. Save each reconciliation - Keep for audit trail—essential for preparing bank statements for audit
  3. Document discrepancies - Note why items don't match
  4. Use consistent naming - Same file structure each month
  5. Verify totals - Check statement totals match your sum

For professional workflows, see how bookkeepers automate bank reconciliation.

Summary

Excel bank reconciliation involves importing both bank and internal data, matching transactions using formulas, identifying outstanding items, and ensuring adjusted balances agree. With proper setup and formulas, reconciliation becomes systematic and auditable.

Related Guides

Getting Started

Conversion Tutorials

Data Analysis

For Professionals

Sandra Vu

About Sandra Vu

Sandra Vu is the founder of Data River and a financial software engineer with experience building document processing systems for accounting platforms. After spending years helping accountants and bookkeepers at enterprise fintech companies, she built Data River to solve the recurring problem of converting bank statement PDFs to usable data—a task she saw teams struggle with monthly.

Sandra's background in financial software engineering gives her deep insight into how bank statements are structured, why they're difficult to parse programmatically, and what accuracy really means for financial reconciliation. She's particularly focused on the unique challenges of processing statements from different banks, each with their own formatting quirks and layouts.

At Data River, Sandra leads the technical development of AI-powered document processing specifically optimized for financial documents. Her experience spans building parsers for thousands of bank formats, working directly with accounting teams to understand their workflows, and designing systems that prioritize accuracy and data security in financial automation.