BankStatementDesk

How to combine monthly bank statements in Excel

To combine monthly bank statements in Excel, first turn each statement into a checked transaction table with matching column names. Then append the rows into one table and compare the combined totals with independent monthly controls. Add source period, account and row identifiers before combining the files. These fields make it possible to investigate an overlap or missing month without taking the entire workbook apart. This example follows three fictional statements for one checking account, then explains how the process changes for multiple accounts.

In this guide
  1. Create a statement inventory before opening the files
  2. Standardize column names and meanings
  3. Append tables manually for a small one-time job
  4. Validate the combined result with a three-month example
  5. Keep the statement period separate from the calendar month
  6. Make the workbook easy to refresh and hand over

Create a statement inventory before opening the files

List each source file, account label, currency, printed start and end dates, opening balance, closing balance and expected transaction count if supplied. A folder containing twelve PDFs can still have two copies of March and no April statement. Check the printed periods instead of relying on filenames or download dates. Some banks issue statements on a monthly cycle that begins midmonth. Label each statement with its real boundaries so a February posting on a January-to-February statement remains traceable.

If a statement is a replacement for an earlier issue, keep both originals but choose one active version for the combined table. Record why the newer or older source was selected. A duplicate filename with “copy” appended does not prove the document has identical transactions. Compare the printed period, opening and closing amounts, transaction totals and any revision date. When the versions differ, investigate before appending either one. Keeping both as active sources can double an entire period without making individual transaction rows look unusual.

Check continuity between consecutive statements

For the same account, currency and balance type, the first statement's closing balance should normally match the next statement's opening balance where the periods meet. If one ends at 1,180.00 and the next begins at 1,250.00, look for a missing period, a different account section or a balance field that includes pending items. Do not manufacture a 70.00 adjustment to bridge the gap. Record the discrepancy and find the missing source or a documented explanation before interpreting the annual movements.

Standardize column names and meanings

Choose a master set of columns appropriate to your task. A practical set is Account, Currency, Statement start, Statement end, Source file, Source row, Posting date, Description, Signed amount and Printed balance. Keep source-specific information such as a card purchase date or foreign currency amount in additional columns when useful. Document what positive signed amounts mean. Consistency of meaning matters more than consistent spelling. A column called Amount that contains only debits in one month cannot be appended to a signed net movement field without mapping it first.

Map each source field into the master layout. January might print “Money in” and “Money out,” while March might print “Credit” and “Debit.” Confirm the direction using a deposit and a payment from each statement. Preserve the original columns until the mapping is checked. If an optional reference field appears only in March, leave the earlier rows empty rather than inventing placeholders that look like real identifiers. A blank means the field was unavailable or omitted, which is a fact worth retaining.

Assign a source key that remains stable after sorting

Number transactions within the original statement order before sorting by date. Combine a non-sensitive account label, statement period, version and source position into a provenance key if you need one. This key describes where the record came from. It does not establish whether two transactions in different files represent a duplicate. A corrected statement can give the same transaction a different source position. Keep reference numbers and original descriptions as separate evidence for comparison across versions or periods.

Append tables manually for a small one-time job

For three short statements, a manual append can be straightforward. Create a new worksheet with the master header row, copy only the transaction rows from January, and append February immediately below. Repeat for March. Exclude opening balances, closing balances, column headings and summary totals from the movements. Preserve the Source file and Source row columns when copying. After each paste, compare the number of visible rows for that source label with the independently recorded count in your inventory.

Watch for filtered tables. Depending on what you selected and how you copied, a filtered view can result in an incomplete set of rows or hidden rows you did not expect. Clear filters and check the row count before copying unless you are intentionally copying a documented subset. After each paste, check for dates landing in the description field or an amount shifted under the balance header. Looking at the first and last row of each block takes little time and catches layout mistakes early.

Use Power Query when the same process will recur

Import the monthly Excel tables as queries, standardize their headers and data types, and use Append Queries as New to build a separate combined result. Power Query appends matching columns by header name, even when their order differs. Differently named columns appear separately and may acquire null values where a source lacks the matching header. Read Microsoft's guide to appending queries for the precise behavior. Inspect the combined column list after every new statement template enters the workflow.

If you eventually use a folder-based import, give the input folder a narrow purpose. Keep backup copies, output workbooks and superseded statements elsewhere. A refresh should ideally encounter only input files you deliberately approved for inclusion. Do not rely solely on workbook count as evidence that the correct files were loaded. Build a summary by Source file and Statement period after each refresh. It should match the inventory of active sources, including any intentionally empty month with no transaction rows.

Validate the combined result with a three-month example

January opens at 1,000.00 and contains credits of 500.00 and debits of 320.00. Its closing balance is 1,180.00. February starts there, contains credits of 250.00 and debits of 400.00, and closes at 1,030.00. March adds credits of 600.00 and debits of 110.00, bringing the closing balance to 1,520.00. Across the three statements, credits total 1,350.00 and debits total 830.00. Their difference is 520.00, which bridges the first opening and last closing balance.

Now deliberately omit February from your practice workbook. Adding January and March activity to January's opening produces 1,670.00, which disagrees with March's closing by 150.00. The inventory also reveals a missing source and a break in continuity between January's close and March's opening. If February happened to have zero net movement, the opening-to-closing check could still pass after the omission. That is why the source inventory and per-period counts provide evidence that total arithmetic alone cannot supply.

Check overlapping statements before removing apparent duplicates

A spending export might cover a custom date range that overlaps the monthly PDF. Two rows with the same date, merchant and amount are candidates for comparison, not automatic deletion. A commuter can make two identical purchases on the same day. Look for independent reference numbers, original statement positions, surrounding balances and whether both transactions appear twice because two source periods overlap. If only one statement should be active, resolve that at source level rather than removing individual rows with a blanket duplicate rule.

Keep the statement period separate from the calendar month

For reconciliation, group rows by the statement they came from. For a calendar spending summary, group reviewed posting dates by calendar year and month. These are different grouping fields. A statement ending March 5 might contain February 15 through March 5. Calling the entire statement “March spending” misstates the calendar breakdown even if its total is correctly reconciled. Preserve both fields so the same combined table can support the statement check and the calendar report without conflicting interpretations.

Keep separate reconciliation controls for different accounts and currencies. A transfer of 300.00 between accounts can appear as a payment in one statement and a deposit in another. Both records are valid for their own account balances. Removing one from the master table because it resembles its counterpart would break one account's reconciliation. If the final purpose is to summarize external spending, classify the internal transfer pair in a reporting layer. Preserve both movements in the transaction data and document how the reporting filter handles them.

Make the workbook easy to refresh and hand over

Keep the inventory, mapping instructions, combined transactions and monthly check table on clearly named sheets. In the check table, compare expected and observed row counts, debit totals, credit totals and closing balances for each source period. Use a plain-text status for periods awaiting review. An empty month should have an explicit entry in the inventory even when there are no records in the master transactions. Otherwise a genuine zero-activity month is indistinguishable from a missing file in a summary grouped only from the transaction table.

To prepare each digital PDF, start with the bank statement to Excel converter and review its rows before appending. Download and label one checked workbook per statement. Use the balance troubleshooting guide for discrepancies within an individual period. When a new month arrives, verify its opening, add it to the active-source inventory, rerun the append and compare controls again. Those small checks preserve the usefulness of the annual workbook as its source set grows or a corrected statement replaces an earlier one.

How should you document a month that was replaced?

Record the original statement identifier, replacement identifier, reason for replacement and the date you changed the active-source inventory. Compare the old and new per-period totals and explain any movement in the annual figures. An updated closing balance can also affect the opening check for the following month, so review that boundary once more. Preserve the previous final workbook if colleagues already used it for a report. They can then see which period changed and determine whether their earlier summary needs to be rerun.