How to combine monthly bank statement PDFs in Excel

You'll need Excel with From PDF

This walkthrough is for Excel for Microsoft 365 on Windows, using local PDFs with usable transaction tables. Mac and browser menus are different. The example follows one USD checking account, where money in is positive and money out is negative; credit cards need their own sign interpretation.

Power Query alone doesn't tell you which connectors you have. Microsoft's Excel connector matrix, updated June 25, 2026, lists PDF for its Windows Microsoft 365 plans, but not its Office 2016 or 2019 editions. The Mac table doesn't list PDF. Excel 2021 and 2024 appear in the page's general applicability, but have no separate PDF entries in the Windows matrix. Since that page doesn't establish their PDF support, check your installation for From PDF.

The same page lists SharePoint-hosted PDFs for Excel for the web, not this local-folder route. Microsoft's PDF import instructions, updated August 12, 2026 say to install .NET Framework 4.5 or later if Excel reports missing connector components. Although the general PDF connector page lists no prerequisites, the Excel-specific guidance covers that error. We didn't run Excel or test any installation for this article.

You'll need untouched originals for comparison, with working copies for one account, currency and layout in a dedicated folder. In a separate statement index, record SourceFile, AccountAlias, Currency, StatementStart, StatementEnd, page count, expected transaction count, Opening and PrintedClosing. Take dates and balances from the statements themselves: twelve files don't necessarily cover twelve different months.

The second table isn't always the transaction table

A table number tells you where something appeared in the extraction, not what it contains. A longer month can add a continuation table, or another month can put a summary first. The second table could therefore be the wrong object or only part of the list.

That happened in Daniel Sanderson's August 5, 2024 monthly-statement question: selecting individual PDFs worked, but varying transaction volumes moved the tables and broke the folder workflow. It's a practitioner's report, not a measured failure rate.

Microsoft explains the risk: the Folder connector processes files using the selected example (updated October 9, 2026), and the combine-files workflow applies a linked transformation function (updated August 29, 2025). Extra rows are fine when the schema stays consistent. But if objects differ, columns shift or continuation tables are missing, you need to change the selection or transformation. Clicking Combine again won't fix that.

Compare the PDF preview with every transaction page

A single-file preview lets you see what Excel finds. Follow Microsoft's PDF import procedure: choose Data → Get Data → From File → From PDF, select the file and choose Open. After inspecting the tables in Navigator, choose Transform Data so you can check the rows before loading them.

The preview needs to account for every transaction page: dates, descriptions, money out, money in and any running balance. Wrapped descriptions and fees in separate sections need attention too. Comparing the longest statement and any visibly different month tells you whether the same approach fits those files.

The PDF connector documentation, updated October 9, 2026 says MultiPageTables defaults to true, combining similar tables on consecutive pages. That doesn't guarantee every continuation is included, and multiline rows can also cause problems. If you use StartPage or EndPage to reduce a large import, the ranges must cover the whole document. January's range may cut off February.

See which tables each file contains

Once you know what the transaction tables look like, you can list what's in the folder without assuming they all occupy the same position.

1. Choose Data → Get Data → From File → From Folder, select the folder, then Transform Data, as shown in Microsoft's folder-import guide, updated June 25, 2026. Filter Extension to the intended PDFs and check Folder Path. Subfolders are included, so an archive subfolder can bring in duplicates.
2. Rename the file-name column SourceFile, keeping Folder Path too if filenames repeat. You'll still need Content: Microsoft's combine-files documentation identifies it as the file's binary content.
3. If the tables move between files, use Add Column → Custom Column (documentation updated August 15, 2026) to create PDF with the formula Pdf.Tables([Content]). This uses the documented Pdf.Tables function (updated April 2, 2026) on every file rather than selecting a numbered table from a sample.
4. Expand the PDF column to its Name, Kind and Data fields, keeping the prefix to avoid a clash with the source filename. You now have extracted objects and their nested data, not finished transactions.

Microsoft's table-expansion reference, updated September 11, 2026, shows how inner tables expand into rows while keeping outer fields. Leaving SourceFile outside the nested table lets each row keep its filename. Compare this inventory with your original file list so you can investigate any file with no useful objects.

If every file has the same suitable object structure, you can instead use Home → Combine Files on Content. The folder guide above documents this route and its generated sample query. Leave Skip files with errorsoff, because you still need to account for statements that produce errors.

Keep transactions, not headers and summaries

An object whose Kind is Table can still be a summary. Inspect each file's nested Data for transaction and continuation tables, taking care not to keep both page-level and table-level extractions of the same transactions.

For a one-off year where the tables move around, a selection list is more useful than a fixed index. Create a worksheet table with SourceFile, TableName and TableOrder, using one row per inspected transaction table. Copy TableName from PDF.Name, making each file/name pair unique, and set TableOrder from the printed sequence. Continuation tables need separate entries. This selection covers only the files you reviewed.

Import that list with Data → From Table/Range, documented in Microsoft's import guide. Use Home → Merge Queries (documentation updated May 7, 2026) with the inventory on the left and selection list on the right. With matching keys set to Text, Ctrl-select SourceFile and PDF.Name on the left, then SourceFile and TableName on the right, in that order. Choose Inner, with fuzzy matching off, and include Folder Path if filenames repeat. Check the retained objects against every selection-list entry before expanding only TableOrder from the merged column and clearing the prefix.

Before expanding transaction rows, add a custom column named Rows with Table.AddIndexColumn([PDF.Data], "ExtractedRow", 1, 1). Microsoft's index-column reference, updated March 31, 2026, explains the table, column name, starting value and increment arguments. The index is a row's extracted position within its table, not a verified PDF line number.

When you expand Rows with the column-header control, keep ExtractedRow and the inspected transaction fields, and clear the prefix. Don't rename raw fields until you know what they mean: Column1, Column2 and Column3 can vary between layouts. Incompatible layouts need separate handling and normalized columns before you combine checked results. You can then remove Content and leftover nested tables, while keeping SourceFile and PDF.Name.

TableOrder and ExtractedRow are worth keeping because Microsoft warns that merges and grouping do not guarantee row order (updated January 9, 2026). You'll need them to restore the inspected table/row sequence before checking running balances. If extraction reordered a table, correct it against the PDF first.

• Identify repeated headers by their values, because deleting every twentieth row could catch a transaction. Check that the first transaction isn't treated as a header.
• Opening/closing labels, page subtotals and carry-forward rows don't belong among transactions, but their printed figures still belong in the statement index.
• Actual fees, interest, refunds and adjustments stay in the data. Total in a description doesn't automatically make that row a subtotal.
• Blank-date rows may be wrapped descriptions belonging to a transaction, so repair them against the PDF before removing any. Never fill amounts down to make rows look complete.

Once an exclusion is confirmed, the column filter controls in Microsoft's filtering guide, updated November 26, 2025, let you remove it. Record what you removed and why, rather than deleting error rows just to make the query finish.

Attach statement periods and set the data types

Each transaction needs a statement period, which comes from the index you prepared. Import it through From Table/Range and leave that source query unchanged. This single-account, single-statement-per-PDF workflow needs one unique index row per source file.

To attach those fields, merge from the transactions to the index by SourceFile using Left outer, then expand AccountAlias, Currency, StatementStart and StatementEnd without the prefix. Include Folder Path in this and later joins and groupings if filenames repeat. Investigate unmatched values, since this join keeps transactions with missing metadata. Check that the row count hasn't changed, because duplicate index keys can multiply rows.

A PDF with multiple accounts or periods needs a more specific statement key before joining. One period won't describe the whole file, and the first and last transaction dates aren't period boundaries either. Quiet days still count.

Both queries need identifiers, references, descriptions and SourceFile set to Text, dates to Date, and amounts and balances to Fixed decimal number for this cents-based workflow. To set these, right-click a column heading and choose Change Type → Using Locale (documentation updated August 14, 2026), then the type and source locale. Fixed decimal number has four decimal places. It doesn't convert exchange rates or choose signs.

Date order and decimal separators must match the source. Where years are missing, use the statement period, watching for December-to-January crossings. Review automatic Changed Type steps and replace incorrect conversions, because changing the result to Text won't recover lost values. Keep original amount columns beside a normalized Amount column: positive for checking-account money in and negative for money out. See dates and number formats and debit/credit columns versus a signed amount for those decisions.

Check each statement before totaling the year

A separate summary query lets you check each statement without changing the detailed transaction query. Before summing, resolve missing or error Amount values rather than turning unexplained blanks into zero. Microsoft documents Home → Group By, Advanced, Count rows and Sum (updated May 7, 2026). Group by SourceFile, AccountAlias, Currency, StatementStart and StatementEnd, plus Folder Path if needed, creating RowCount with Count rows and NetTransactions with Sum of Amount. These statement results stay separate from the detail and the folder total.

1. Compare counts with transaction lines across every PDF page, leaving out wrapped description lines. A printed count is useful only if it covers the same transactions.
2. Use Home → Merge Queries → Merge Queries as New to create Checks, with the original index on the left and summary on the right. Match SourceFile (and Folder Path if needed) using Left outer, then expand RowCount and NetTransactions without the prefix. Don't merge the summary back into the source index because transactions already depend on it. Keep missing statements visible rather than assuming zero activity, which you can accept only after checking the PDF.
3. In Checks, add a custom Difference column with [Opening] + [NetTransactions] - [PrintedClosing]. The target is zero at the statement's printed precision. Missing balances or summaries remain unresolved, not zero-filled, and an opening balance repeated on each transaction must never be summed.
4. Where the statement prints money-in and money-out totals, compare those too. Running balances need checking in printed order; if none are printed, mark that check unavailable. Dates, descriptions and amounts still need comparison with the source.

A zero difference tells you the arithmetic works, not that you've reconciled against your ledger or authenticated the document. Offsetting errors can cancel, as shown in why a balanced statement can still be wrong.

February's missing page throws off the total

To see why the page check matters, consider these three fictional statements. They aren't customer records or Excel output. They cover January 1-31, February 1-28 and March 1-31, 2026, for one USD checking account. January and March have transactions on page 2, but February continues onto page 3. No printed running balances are supplied, so this shows closing-balance checks, not a completed row-by-row running-balance check.

Here are all the signed transaction amounts:

• January: +800.00 - 200.00 - 75.25 - 4.75 = +520.00.
• February: +950.00 - 400.00 - 110.20 + 25.00 - 64.80 - 5.00 = +395.00. The last two debits are on the continuation page.
• March: +1,200.00 - 750.00 - 92.35 - 7.65 = +350.00.

Without February's continuation page, you get 4 transactions instead of 6. Missing debits of -69.80 leave a calculated closing of 1,984.80, or 69.80 too high. The fix is to find that page and those two rows, not insert a balancing adjustment.

With all rows present, the combined net is 1,265.00 and 1,000.00 + 1,265.00 = 2,265.00. January's printed closing equals February's opening, and February's equals March's. Those checks still pass when February's last page is missing because the printed balances haven't changed. That's why continuity can't replace per-statement row and amount checks.

Make sure the workbook covers the dates you need

Matching balances don't prove you have the whole year: a missing no-activity period could leave them unchanged. Sort the index by account and printed period, then compare each closing with the next opening and look for date gaps and overlaps. You need to account for the whole requested year, including non-calendar statement cycles.

Twelve complete statements don't necessarily equal one calendar year. For a calendar-year total, check full statements first, including those crossing the year boundary. Then create a separate reporting view filtered to the requested dates using the agreed posting or transaction date field, leaving full rows available for statement checks.

Identical dates and amounts can be genuine repeated purchases, so check duplicate statement copies and possible overlapping transactions against the PDFs before excluding them. See merging overlapping statement data.

Once the checks are complete, use Home → Close & Load, as documented in Microsoft's folder guide. Keep Transactions, StatementIndex and Checks separate, with filenames and inspected table references in the review copy. Adding or replacing a PDF means selecting its tables and checking the statement again, not just refreshing the query.

If you're preparing US taxes, include receipt and invoice references alongside any unresolved business-purpose questions. The IRS records guidance, last reviewed or updated August 3, 2026, says you may need a combination of documents to establish all elements of an expense. The table organizes transactions, but it doesn't establish deductibility or replace supporting records.

When another route makes more sense

Without a stable transaction structure, a reusable folder query is the wrong approach. Image-only scans without usable tables need a text-original version, an approved OCR route or manual transcription first. This walkthrough doesn't provide OCR. If you have many banks or widely different layouts, maintaining separate mappings may be harder than using another extraction route.

If you're heading for QuickBooks Online rather than an Excel workpaper, check its own statement upload first. Intuit's US guide, updated August 24, 2026, documents English PDF/image uploads and side-by-side review, though you'll need to check availability in your account and region. This isn't a QuickBooks Desktop claim, or a route we tested for an annual Excel table.

A small outlier may not justify a special parser. Typing its rows and independently checking them against the PDF may be easier, as long as that exception stays identifiable in the table.

The bank's native CSV is worth checking before you buy anything, provided it covers the history and fields you need. It avoids PDF table selection, but not account, date, sign, duplicate or balance checks, so keep the PDFs for comparison.

If you still need PDF extraction and source-linked review, AnyStatement converts PDFs to Excel or CSV and lets you review transactions against source PDF lines. The batch workspace's Export all merges completed, unlocked files. It excludes locked previews and unfinished files, and excludes identified duplicate copies by default. Completed doesn't mean every check passed, so compare the preview and every exclusion with your index before downloading. Native exports and manual Power Query remain alternatives. Whichever you use, keep the originals and account for every period.

The next person needs more than a total

Whoever receives the workbook needs to trace its rows back to the statements. Leave filenames and periods attached, and send the originals, detailed table, checks and unresolved issues together, including your decisions on gaps, overlaps and possible duplicates. Before sending, confirm that dates and amounts have the right types, with no unexplained errors or missing values.

What we checked, and what we didn't

We reopened every external link on October 17, 2026: the Microsoft menu and M-function references, Stack Overflow question, Intuit US upload guide and IRS records page. The Microsoft update dates come from updated_at metadata, not Excel release dates, and the menus and connector behavior described here come from documentation rather than Excel tests.

We independently recomputed all three fictional transaction counts, net amounts, closing balances, carry-forwards, combined total and missing-page difference using Python Decimal. We checked batch-workspace.tsx, export-sheet.tsx and docs/architecture.md for product behavior and export exclusions. We didn't run the custom Power Query formulas, import real bank PDFs in Excel, test OCR, upload to QuickBooks, execute a batch export or benchmark conversion accuracy. Those limits matter: your PDFs still need row-by-row comparison and statement checks.