How to Extract a Table From a PDF Bank Statement Into Excel

Last updated July 2026.

To extract a table from a PDF bank statement into Excel, use Data, Get Data, From File, From PDF in Excel and let Power Query detect the table, or upload the PDF to a statement converter that rebuilds the transaction rows directly. Whichever method you use, verify the result by checking that the opening balance plus the sum of all transactions equals the closing balance printed on the last page.

Why the extraction is harder than it looks

A PDF does not contain a table. It contains characters placed at coordinates, and the columns you see are an illusion created by consistent horizontal positions. Every extraction method has to reverse engineer the structure, and bank statements make that unusually difficult: no gridlines to mark column edges, merchant descriptions that wrap onto a second line, debits and credits printed three different ways depending on the bank, and the account header repeated on every page.

That matters because the output is graded. A statement ends with a closing balance. If your extracted rows do not add up to it, the extraction failed, and the failure is invisible in the spreadsheet. This is why the checking step below is more important than the method you pick.

Method 1: Power Query, built into Excel

This is the best free option and the right first attempt for a digital PDF.

1. In Excel, go to Data, then Get Data, then From File, then From PDF.
2. Select the statement. The Navigator pane lists every table and page Excel detected, named Table001, Page001 and so on.
3. Click through the candidates and preview them. The transaction table is usually a Table entry rather than a Page entry, and on a multi-page statement there will be one per page.
4. Select the relevant tables and choose Transform Data rather than Load, so the editor opens.
5. Use Append Queries to stack the per-page tables into one, then promote the first row to headers and delete the repeated header rows that came from later pages.
6. Set types explicitly. For the date column use Change Type, then Using Locale, and pick English (United States) so a US MM/DD/YYYY file is not read as DD/MM/YYYY.
7. Close and Load.

What breaks: wrapped descriptions still arrive as extra rows, section headings can be detected as their own tables, and columns drift on statements with inconsistent spacing. The date locale step is the one people skip and then quietly get wrong, because a March 4 transaction read as April 3 does not look like an error.

Our step-by-step walkthrough of importing a bank statement PDF into Excel with Power Query covers the append and cleanup steps in more detail, including the refresh setup for a statement you receive monthly.

Method 2: copy and paste from the PDF

Open the PDF, select the transaction area, copy, and paste into Excel. On a short, simple statement with generous column spacing this occasionally works.

Usually it does not. Text pasted from a PDF arrives as lines, not cells, so everything lands in one column. You then run Data, Text to Columns and split on fixed width, which works only if the statement used a monospaced layout, or on a delimiter, which works only if there is a consistent one. Variable-length merchant descriptions defeat both.

Adobe Acrobat has a slightly better version of this: right-click inside a detected table region and choose to copy as a spreadsheet. It preserves more structure than plain copy, and it still carries the repeated headers and split rows into Excel with it.

Worth trying for one page. Not worth trying for twelve.

Method 3: a generic PDF to Excel exporter

Acrobat's Export PDF, and most online PDF to XLSX tools, aim to reproduce how the page looked. On a well-ruled financial report that works well. On a statement you typically get merged cells where headings span the page, blank spacer rows, the account header repeated once per page, wrapped descriptions as extra rows, and amounts stored as text because of parentheses or CR and DR markers.

The output is salvageable with fifteen to forty minutes of work per statement: unmerge everything, filter on the amount column to strip the furniture, rejoin the orphaned description rows, and force the amount column numeric through Text to Columns. We wrote up the full cleanup sequence in our guide on whether Adobe Acrobat converts bank statements to Excel.

One warning about free online converters: you are uploading a document with an account number, a balance, and often a home address to a service whose retention policy you have not read. For a client's statement that is a decision to make deliberately rather than by default.

Method 4: a converter built for statements

A statement converter treats the quirks above as known rules rather than things to infer. It strips repeated headers and summary blocks because it knows they are not transactions, rejoins a wrapped description to the row above it, normalizes signs whichever way the bank printed debits and credits, and keeps the running balance as its own column so the footing check is available to you.

The practical difference is not that it is smarter. It is that it has already been told what a bank statement looks like at ninety-odd US institutions, so it is not guessing. Upload the PDF, download the XLSX or CSV, and the first thing you do is verify the footing rather than begin a cleanup pass. Ours also exports QBO, OFX, QFX, and QIF, so the file imports straight into QuickBooks, Xero, or Quicken without a mapping step.

For scanned statements this is usually the only method that works at all, because Methods 1 to 3 need a text layer that a scan does not have.

Which method to use

The three checks, whichever method you used

Do these before you build anything on the data. They take under two minutes and they catch the errors that otherwise surface weeks later during reconciliation.

Row count. Count the transaction rows in the spreadsheet and compare with the statement. A mismatch means rows were split by wrapping or dropped at a page break.

Footing. Opening balance plus the sum of all transaction amounts should equal the closing balance printed on the last page. This is the definitive test. If it ties, the extraction is complete. If it does not, something is missing or duplicated and no visual scan of the sheet will find it faster.

Sign consistency. Compare how debits and credits are signed on the first page and the last page. Converters sometimes handle a bank's separate-column layout correctly at the top of a statement and switch convention after a section heading, which quietly inverts a run of transactions.

Cleaning up the columns you end with

Even a good extraction benefits from a short pass. Split the amount into separate debit and credit columns if you want to total them independently, or keep one signed column if you are heading for a pivot table. Add a month column with =TEXT(A2,"YYYY-MM") so you can group by period. Trim the merchant strings with =TRIM(CLEAN(B2)) to remove the padding spaces and stray control characters that survive most conversions.

Then decide what the sheet is for. If it is bookkeeping, categorize the transactions into expense categories before anything else, because everything downstream depends on the tagging. If it is reporting, build the profit and loss report from the categorized rows. If it is month-end, go straight to reconciling against your books. And if the statement is the input to a loan file, the lender-ready output keeps the balance column that underwriters look for.

Frequently asked questions

How do I extract a table from a PDF into Excel?

In Excel use Data, Get Data, From File, From PDF, then pick the detected table in the Navigator pane and choose Transform Data to clean it before loading. For documents Excel cannot detect a table in, such as scans, use a converter that runs OCR and rebuilds the rows.

Can Excel open a PDF directly?

Excel cannot open a PDF as a workbook, but it can import from one. The From PDF connector in Get Data reads the file, lists the tables it detects, and loads the one you choose. It requires a text layer, so scanned statements return nothing usable.

Why does my extracted table have extra rows?

Long merchant descriptions wrap onto a second printed line, and most extractors treat that second line as its own row. Filter for rows with no amount to find them. They are continuation text belonging to the row above and should be rejoined, carefully.

How do I extract a table from a scanned bank statement?

OCR has to run first, because a scan is an image with no text layer. Use a converter with built-in OCR rather than OCRing and then exporting, since a general OCR export reproduces the page layout instead of the transaction table and needs cleaning afterward.

Is Power Query good enough for bank statements?

It is genuinely good for occasional digital statements and it is free, particularly with the Using Locale option that stops dates swapping. It struggles with wrapped descriptions, repeated page headers, and scans, so it gets expensive in time once you are doing several statements a month.

How do I know all the transactions were extracted?

Check the footing. Take the opening balance, add the sum of every transaction amount, and compare with the closing balance printed on the last page. If they match, no rows are missing. This is faster and far more reliable than reading the spreadsheet against the PDF.

What is the fastest way to get a bank statement into QuickBooks?

Convert the PDF to a QBO or OFX file rather than to a spreadsheet, then import it directly. Going through Excel means building a CSV that matches QuickBooks' expected columns and date format, which is an extra mapping step per file. There is more detail in our guide to converting bank statements to QuickBooks QBO, and if QuickBooks is your only destination a dedicated statement to QuickBooks converter skips the spreadsheet entirely.