To extract data from multiple PDF files to Excel when they are bank statements, convert them in one run, write every transaction into one table with account, period and source-file columns, then check that the table's totals match each statement. Skipping those columns or checks is how mixed-up months and duplicates reach a workbook.
Should multiple statement PDFs land in one Excel sheet or many?
For most bookkeeping uses, put all transactions in one sheet and add columns that say where each row came from. One sheet is easy to filter, sort and total. Many sheets, one per statement, are easier to read but harder to analyse and to import. Choose by what you will do with the workbook.
One sheet: the default
A single transactions sheet works because Excel is good at filtering. If every row carries its account, period and source file, you can show one account, one month or one bank in a second. A pivot table over the same sheet gives you totals by category or by month without moving data around.
One sheet suits these jobs: reviewing a year of activity, preparing a summary for a client, searching for a payee across accounts, and importing into software that takes one file.
Many sheets: when they earn their place
Separate sheets suit cases where each statement has to be read on its own. A reconciliation by month, a handover to a different person per account, or a client who wants to see each bank account separately are reasonable cases. Be aware of the cost: a formula that covers the year now has to reach across twelve sheets, and a late correction has to be made in the right one.
A hybrid that holds up
Keep a single transactions sheet as the source and add a small summary sheet with one row per statement: account, period, opening balance, closing balance, and the sum of the transactions. The summary is what you check. The transactions sheet is what you filter. Anything else, such as a sheet per account, can be produced from the source later with a filter, so it never needs to be the master copy.
If the mixture of banks in your PDFs is what makes the table awkward, the post on converting bank statements from multiple banks explains how layouts and signs get normalized first.
Account and period columns for a multiple file workbook
Account and period columns tell you which statement every row came from. Without them, a combined workbook is a pile of transactions you cannot trace back. With them, you can filter to one account, prove each month separately, and find the source PDF when a client asks about a line.
Add these columns to every row, even when it feels repetitive. Repetition is the point: it survives sorting.
The workbook layout
The table below is an example layout for the transactions sheet. The rows are made-up values.
| Column | Example value | Purpose |
|---|---|---|
| Client | Example Bakery LLC | Keeps clients apart if you ever combine files |
| Bank | Example Bank | Groups statements by layout |
| Account | Checking ending 4417 | Separates accounts at one bank |
| Period start | 2026-03-01 | Marks the statement the row belongs to |
| Period end | 2026-03-31 | Same, and used to test dates |
| Date | 2026-03-14 | Posting date of the line |
| Description | SQ *FLOUR SUPPLY CO 03/13 | Text as the bank printed it |
| Amount | -412.80 | One signed amount; negative is money out |
| Balance | 9,318.27 | Running balance when printed |
| Source file | bakery-checking-2026-03.pdf | Traces the row back to the PDF |
| Page | 2 | Helps find the row on the statement |
Why one signed amount
Banks print amounts as separate debit and credit columns, as one signed column, or as a positive number with a "CR" marker. In a combined workbook, mixing these makes totals wrong. Pick one convention for the whole sheet. A single signed amount, negative for money out, makes sums and pivots straightforward. If you must keep debit and credit columns, keep them for every statement. The sign conventions that clash between banks are covered in the post on mixed bank statements.
Store dates as dates
Excel will treat "03/04" differently depending on the computer's regional settings, and may turn a text date into a number or flip day and month. Store dates in an unambiguous year-month-day form, and check that the column is formatted as dates, not text. A quick test: sort the Date column. If it does not order chronologically, some entries are text.
Which fields to expect
What comes out of a statement, and which header details feed the account and period columns, is set out in the guide to which fields bank statement data extraction returns. Use it as the checklist of fields to carry into the sheet.
Do multiple file totals match each statement?
They should, and you have to check, because a combined workbook can look complete while missing a line. The test is simple: for each statement, the opening balance plus the sum of its transactions must equal its closing balance. If it does, no line from that file was lost or doubled.
Do the check per statement, not on the whole sheet. Errors of opposite sign can cancel in a grand total, and a missing line in one file can hide behind an extra line in another. Per-statement totals expose both.
A check you can build in Excel
Make a summary sheet with one row per statement and fill it with formulas:
- List each statement once: account, period, source file.
- Enter the printed opening and closing balances from the statement.
- Use a sum-if formula on the transactions sheet to total the amounts where Source file matches the row.
- Compute opening balance plus that total.
- Subtract the printed closing balance. The result should be zero.
Any row that is not zero is a statement to open. The size of the difference points to the cause: a round figure suggests one missing or doubled line, cents suggest a misread digit.
Check the join between months
A second check ties months together. The closing balance of one statement should equal the opening balance of the next for the same account. If March closes at 9,318.27 and April opens at 9,100.00, a statement is missing, or one is for a different account. This is the check that catches an absent PDF, which a per-statement test cannot.
AutoBankConvert runs the per-statement version of this check inside the conversion: opening balance plus transactions must equal the closing balance before a statement is marked done, and a statement that fails is flagged instead of exported. You still want the month-to-month join as your own check on a combined workbook.
Duplicate rows from overlapping PDF files
Duplicate rows appear when two PDF files cover some of the same days, so the same transactions are extracted twice. The totals then overstate the activity by exactly the duplicated amount, and nothing looks wrong until a reconciliation fails. Overlap has several ordinary causes.
Where overlap comes from
- A client sends a quarterly download and the three monthly statements.
- A bank statement period runs from the 15th to the 14th, and the client also sends a calendar-month export.
- The same file is saved twice under different names.
- A statement is re-issued or corrected, and both versions arrive.
How to find duplicates
A duplicate row has the same account, date, amount and description as another. Excel can flag them with a combined key. Join the account, date, amount and description into a helper column and use conditional formatting to highlight repeats. Do not delete on sight, though. Two real transactions can match all four fields: two identical coffee purchases on the same day are not a duplicate.
Better evidence is the period. If two source files have overlapping start and end dates for the same account, you know the overlap is real. Resolve it at the file level: keep one file for the overlapping days, or trim the rows from the later one, then rerun the balance checks.
Prevent it with a period list
Before extracting, list the files by account and period on paper or in the summary sheet. Sort by account, then start date. Any two periods that overlap, or any gap between one period's end and the next one's start, show up immediately. The cost is a few minutes and it prevents the hardest kind of error to find later.
If one PDF contains several accounts rather than one account over overlapping dates, the problem is different, and the guide to multiple accounts on one bank statement covers how to split them.
Multiple file extraction step by step
Extracting many statement PDFs into one workbook takes seven steps, from gathering files to a final tie-out. Doing them in this order means each check has what it needs, and a problem is found at the step that causes it rather than at the end.
- Gather and list the files. Put every PDF in one folder and list account, bank and period for each. Look for gaps and overlaps now.
- Check the source quality. Prefer bank-downloaded PDFs over scans. If a file is a photo, ask the client for a better one before you spend time on it.
- Convert in one run. Convert the whole folder together so the same rules apply to every file, rather than file by file. AutoBankConvert accepts a folder of statements across banks and months, scanned and digital, in a single upload.
- Check the flagged files. Any statement that failed its balance check needs attention before it goes into the workbook.
- Build the transactions sheet. Combine the rows, add client, bank, account, period, source-file and page columns, and use one signed amount and real dates.
- Build the summary and check. Fill the one-row-per-statement sheet, compute each difference, and test the month-to-month join.
- Remove duplicates, then recheck. Resolve overlaps at the file level, then confirm every difference is still zero.
Where a converter saves time
Steps 3 and 4 are where a tool does the heavy work. Doing them by hand, you would copy tables out of PDFs, which breaks on wrapped lines and page edges, and then verify each one yourself. A converter does the reading and the first balance check, so your time goes into the layout and the tie-out, which are judgement work.
For a practice with many clients, the further step is to stop building workbooks per request at all and export per client from the same run. See how AutoBankConvert handles a monthly batch before deciding whether a combined workbook is what you need.
Common mistakes
- Combining files before checking each one's balance.
- Dropping the source-file column to make the sheet narrower.
- Mixing signed amounts and debit-credit columns in one sheet.
- Deleting duplicates by a match on date and amount alone.
- Treating a grand total that looks reasonable as proof.