How to Merge Multiple Bank Statements into One Spreadsheet

Combining multiple bank statements into one spreadsheet gives you a complete financial picture. Here's how to do it properly.

---

Why Merge Bank Statements

Complete Financial View

See all accounts together:

• Total income across accounts
• Combined expenses
• Cash flow across all sources

Simplified Analysis

One spreadsheet to:

• Run reports
• Create pivot tables
• Track trends over time

Better Tax Preparation

Consolidate for:

• Annual expense summaries
• Business vs personal separation
• Documentation for accountant

---

Before You Start

Gather All Statements

Collect statements for:

• All bank accounts (checking, savings)
• All credit cards
• All time periods needed

Convert to Spreadsheet Format

Each statement needs to be in CSV or Excel format before merging. Use a bank statement converter for PDFs.

Note Column Differences

Different banks use different columns:

Bank A:

Date, Description, Amount, Balance

Bank B:

Trans Date, Details, Debit, Credit, Running Balance

You'll need to standardize these.

---

Step 1: Standardize Column Structure

Choose a Master Format

Decide on consistent columns:

Modify Each File

Transform each statement to match:

Original (separate debit/credit):

Date,Description,Debit,Credit
01/15,PAYROLL,,2500.00
01/16,AMAZON,47.99,

Transformed (single amount):

Date,Account,Description,Amount
2026-01-15,Chase Checking,PAYROLL,2500.00
2026-01-16,Chase Checking,AMAZON,-47.99

Date Standardization

Use consistent date format:

=TEXT(A2,"YYYY-MM-DD")

Or in Google Sheets:

=TEXT(A2,"yyyy-mm-dd")

---

Step 2: Add Source Identifiers

Account Column

Add column identifying the source:

Period Column (Optional)

Add statement period:

Month,Date,Account,Description,Amount
2026-01,2026-01-15,Chase Checking,PAYROLL,2500.00
2026-01,2026-01-16,Chase Checking,AMAZON,-47.99
2026-02,2026-02-01,Chase Checking,RENT,-1500.00

This helps filter by period later.

---

Step 3: Combine the Data

Method 1: Copy and Paste

Simple but manual:

1. Open master spreadsheet
2. Create header row
3. Open each converted statement
4. Copy data (without headers)
5. Paste below existing data
6. Repeat for all statements

Method 2: Power Query (Excel)

More automated:

1. Data > Get Data > From File > From Folder
2. Select folder with all CSVs
3. Combine and transform
4. Load to worksheet

Method 3: Google Sheets IMPORTRANGE

For cloud-based:

=IMPORTRANGE("spreadsheet_url","Sheet1!A:E")

Combine multiple sheets:

={IMPORTRANGE("url1","Sheet1!A2:E");
  IMPORTRANGE("url2","Sheet1!A2:E")}

Method 4: Python Script

For technical users:

import pandas as pd
import glob

files = glob.glob("statements/*.csv")
combined = pd.concat([pd.read_csv(f) for f in files])
combined.to_csv("combined_statements.csv", index=False)

---

Step 4: Clean Up Combined Data

Remove Duplicates

Check for duplicate transactions:

=COUNTIF(A:A,A2)>1

Flag and review rows where this is TRUE.

Handle Missing Values

Fill in missing account names:

=IF(B2="",B1,B2)

Standardize Descriptions

Trim whitespace:

=TRIM(C2)

Convert to consistent case:

=UPPER(C2)

---

Step 5: Sort and Organize

Sort by Date

Primary sort: Date (ascending) Secondary sort: Account

Group by Account

Alternative organization: Primary sort: Account Secondary sort: Date

Create Summary Sections

Add subtotals:

=SUMIF(B:B,"Chase Checking",D:D)

---

Step 6: Verify Accuracy

Check Transaction Counts

Verify Totals

Sum amounts per account and compare to original statement totals:

=SUMIF(B:B,"Chase Checking",D:D)

Balance Reconciliation

For each account, verify:

• Opening balance (from statement)
• Sum of transactions
• Should equal closing balance

---

Working with the Combined Data

Pivot Tables

Create summary by:

• Month
• Account
• Category
• Any combination

Example pivot:

• Rows: Month
• Columns: Account
• Values: Sum of Amount

Filtering

Filter by:

• Account: See just one account
• Date range: Specific period
• Amount: Large transactions only
• Category: Specific expense types

Charts

Visualize:

• Spending trends over time
• Account balance changes
• Category breakdowns

---

Handling Credit Cards Differently

Sign Convention

Credit cards may use opposite signs:

• Purchase: Positive (you owe)
• Payment: Negative (you paid)

You may want to flip signs for combined view:

=D2*-1

Due Dates vs Transaction Dates

Credit card statements show:

• Transaction date
• Posting date
• Statement close date

Use transaction date for consistency with bank accounts.

---

Multi-Year Merging

Annual Consolidation

For tax preparation, merge full year:

Master_2026.xlsx
├── Jan (all accounts)
├── Feb (all accounts)
├── ...
└── Dec (all accounts)

Year-Over-Year Comparison

Add year column:

Year,Month,Account,Description,Amount
2025,01,Chase,PAYROLL,2400.00
2026,01,Chase,PAYROLL,2500.00

Compare same periods across years.

---

Common Issues

Issue: Different Date Formats

Problem: MM/DD/YY vs DD/MM/YY vs YYYY-MM-DD

Solution: Convert all to standard format before merging:

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

Issue: Amount Sign Confusion

Problem: Some accounts use negative for debits, others for credits

Solution: Establish convention and transform:

• Income: Positive
• Expenses: Negative

Issue: Duplicates from Overlapping Periods

Problem: Same transaction appears in multiple statement downloads

Solution: Remove duplicates using date + amount + description matching.

---

Summary

Merging multiple bank statements requires converting all statements to spreadsheet format, standardizing column structure, adding source identifiers, and combining into a master file. Verify by checking transaction counts and totals against original statements. The combined spreadsheet enables comprehensive analysis through pivot tables, filtering, and charting—providing a complete financial picture across all accounts and time periods.