Bank statement PDF to one sheet: all pages, all months

Before you convert

Test the PDF first. Drag the mouse across one transaction line. If the line highlights, the file holds text and can be read. If the whole page highlights as one block, or nothing happens, it is a picture of a page. Copying and spreadsheet import return nothing useful from a picture. Reading it needs OCR, and OCR can misread digits, so every check below matters more.

Next, copy these numbers from each statement into a small control sheet: first and last date of the period, opening balance, closing balance, and any printed totals such as total money in and total money out. Every later check compares your table with them.

A statement holds your account number and everyone you paid. Before you upload one to a website, find out where the site sends the file.

Get every page into rows

Three routes work for PDFs that contain text.

• Copy and paste from the PDF viewer. Fine for one or two pages with a plain layout. Columns often collapse into one.
• The spreadsheet's own import. In Excel versions that have it, Data > Get Data > From File > From PDF reads the tables in the file. Microsoft's page for this connector says similar tables on consecutive pages are joined by default, and that rows wrapped over several lines are not always identified correctly. Look at the result before you trust it.
• A desktop statement converter reads the text in the PDF, puts all pages in one table and runs the balance check for you. The checks in this guide still apply to what it produces.

For twelve files, give every file the same columns first. Excel can then read a whole folder (Get Data > From File > From Folder, then Combine) and stack the files. Microsoft says this works when the files have the same type and the same columns, so a column that appears in only some months breaks it. You can also merge the PDFs in month order and import once, but then you lose the file boundaries. Keep your control sheet either way.

Delete the lines that are not transactions

Every page repeats text that must not stay in the table. Left in, a brought-forward line adds its balance to your totals as if it were a deposit.

Use the carried-forward lines before you delete them. If the statement prints a balance on every row, the balance on the last row of a page must equal the carried-forward figure, and the next page must start from the same figure. If they differ, a row went missing at that page break.

Join wrapped descriptions

A long description takes two or three lines. The extra lines have no date and no amount, so they arrive as rows with text in the description column only. One transaction must be one row.

1. Filter the date column for blanks.
2. Move the text of each remaining row to the end of the description in the row above, with one space between.
3. Delete the empty rows and clear the filter.

A page break can cut one transaction in two: the first line ends page 1 and the rest starts page 2, after the headers. Delete the headers first, then join. Microsoft's PDF page suggests grouping and combining adjacent rows for the same problem. Test: the number of dates must equal the number of amounts (formulas at the end).

One signed amount column

Statements show money in several ways. Convert them all to one Amount column: money out negative, money in positive.

Do not trust column names. Test the direction with two lines you remember: a payment you received must be positive and a purchase you made must be negative.

The sample card statement from the US Consumer Financial Protection Bureau shows the card pattern: previous balance 482.42, payments and credits -350.42, purchases 1,258.56, cash advances, transfers and fees 0, interest 2.15, new balance 1,392.71. Purchases raise what you owe and payments lower it, so payments are negative there. Some issuers do the opposite, so test with a payment you remember. That sum is also the closing check for a card.

A card payment appears twice: as money out in the bank account and as a payment on the card. In one merged table the two cancel after the flip. If you analyse spending, leave the payment lines out, or you count the spending twice.

Dates without a year

Many statements print 16 Dec or 12/16 on each row and give the year once, in the header. A spreadsheet fills the gap by guessing: Microsoft documents that its DATEVALUE function uses the current year from your computer's clock when the year is missing. A December 2025 statement converted in 2026 can end up as December 2026.

• Take the year from the period printed on the statement, not from the clock.
• If the period crosses New Year, for example 15 Dec to 14 Jan, December rows get the first year and January rows get the next.
• 03/04 can be 3 April or 4 March. Find a date with a day above 12, such as 25/03, and use that order for the whole statement.
• If there are two date columns, such as transaction date and posting date, keep one of them for every row.
• Write the final date as year-month-day, 2025-12-16. It sorts correctly even as plain text and cannot be read two ways.

Check: the first and last date of each statement must fall inside the printed period. A date that jumps back by months means a wrong year.

Worked example: two pages, one clean table

Example only, with invented rows. One statement from 15 Dec 2025 to 14 Jan 2026, opening balance 1,250.00. This is how the rows arrive after a copy:

After clean-up, four rows are left. The year comes from the period, the carried-forward and brought-forward lines are gone, and the continuation line is joined to the fuel line:

Check: 1,250.00 - 64.20 - 112.00 + 850.00 - 48.90 = 1,874.90, the printed closing balance. The figure carried forward from page 1 and the figure brought forward on page 2 are both 1,073.80, so nothing fell out at the page break.

Merge the months and remove true duplicates

1. Name the files 2025-01, 2025-02 and so on. Month names sort as Apr, Aug, Dec, Feb.
2. Before you stack, add a Source column (the file name) and a Row column (the original order). Same-day rows come in an order a date sort can scramble, and a running balance only works in that order.
3. Stack the statements in month order under one header row. Sort by Source, then Row.

Monthly statements cut by the bank do not overlap, so a clean set has no duplicates. They appear when the same file is added twice, when date ranges you chose yourself overlap by a day (1 to 31 Jan, then 31 Jan to 28 Feb), or when a brought-forward line stays in as a transaction.

Do not run Remove Duplicates on date and amount alone. Microsoft says it compares what appears in the cells and deletes the duplicates permanently, so two real 4.50 purchases on one day would lose one. Copy the sheet first, then fix by cause:

• Same file twice: two Source values with the same row count and the same total. Delete one Source completely.
• Overlapping range: compare the repeated day in both files and delete it from the later one.
• Balance column printed: include Balance in the comparison. A true copy repeats date, amount and balance. Two separate purchases have different balances.

Checks that prove nothing was lost

Run these in order. Each takes a minute.

One statement:  =ROUND(opening+SUMIF(B:B,"2025-01",E:E)-closing,2)
Every row:      =ROUND(F2+E3-F3,2)
Counts:         =COUNT(A2:A400)   and   =COUNT(E2:E400)

Here Source is in column B, Amount in E and Balance in F. Round to 2 decimals, or tiny leftovers show up instead of 0. COUNT counts real numbers and dates and skips text, so a low count points to amounts stored as text.

Example only, with invented numbers. Each statement passes alone, but the chain breaks:

March adds up on its own, yet it opens 135.50 above where February closed. Something between them is missing: a statement, or the March file belongs to another account.

These checks prove the table matches the PDF in total. They do not see a wrong description or a wrong category, and the total checks miss two errors that cancel, such as one lost row of 50.00 and one doubled row of 50.00 (the every-row check catches that pair). If no balance is printed per row and a statement fails, count the lines on each PDF page and compare them with your rows for that page. A person still has to read the rows, and your bookkeeper or accountant decides how to record them.

Questions

Keep reading

More guides

About Ledgerpour

Ledgerpour is our Windows app that turns PDF bank and credit card statements that contain text into CSV, XLSX, QBO or CAMT.053 files, and checks that the balances add up before you import. It does not read scanned pages. Review the converted data before you use it in your books. It is not in the Microsoft Store yet.

Sources

• Microsoft Learn: Power Query PDF connector
• Microsoft Learn: Combine files overview (Power Query)
• Microsoft Support: DATEVALUE function
• Microsoft Support: Filter for unique values or remove duplicate values
• Microsoft Support: COUNT function
• US Consumer Financial Protection Bureau: Understanding credit card statements (sample statement)

Checked 2026-11-03.