How to Convert a PDF Bank Statement to Excel (And Actually Use the Data)

How-to · Updated July 2026 · ~11 min read

The bank gives you a PDF. Your reconciliation needs rows. You paste the transactions into Excel and get 400 lines of mush in column A — dates glued to descriptions, amounts in the wrong row, page footers wedged between transactions. Then you find a free online converter and pause, because that file is a client's account history.

This is the extraction half of the job, done with Excel alone, and it ends with the arithmetic proof most guides skip. The matching method is in reconciling a bank statement in Excel with AI; the worksheet is the bank reconciliation template for Excel.

Why PDF statements resist extraction

A PDF describes where marks go on a page, not what they mean. There is no row, column or cell, so a table has to be inferred from coordinates — and two things break the inference.

A PDF is either text or a picture of text. A bank-generated statement carries a text layer: real characters at real positions. A printed-and-scanned statement is pixels only, and every tool that reads a text layer gets nothing. To tell which you have, drag-select across a transaction line. Characters highlight: there is a text layer. A blue rectangle drags with nothing highlighting: it is an image, so skip to scanned statements.

Columns are not really columns. Your eye reads Date, Description, Money out, Money in, Balance because the numbers line up, but the file often has no vertical rules and no table metadata — the alignment is whitespace. Extractors guess the boundaries from gaps, and guess wrong in two places:

• Multi-line rows. "CARD PAYMENT TO WWW.SUPPLIER-PORTAL / REF 88214" wraps onto three lines, but the date, amount and balance sit only on the first.
• Page breaks. Every page repeats a header block, and often a "Balance brought forward" line, at the same coordinates as your data.

Extracting a text PDF with Excel's own PDF connector

Excel for Windows includes a PDF connector in Power Query: no extra cost, and nothing leaves your machine.

1. Select Data > Get Data > From File > From PDF — the Get & Transform group on the Data tab.
2. Select your PDF file, and then click Open.
3. The Navigator dialog lists each table the connector detected and each page it found; click through the previews to find your transactions.
4. Choose Transform Data, not Load — a statement always needs cleaning, and the editor makes it repeatable next month.

Two constraints. Microsoft's Excel documentation states that "the PDF connector requires .NET Framework 4.5 or higher" — already present on any current Windows machine. And it is not available in Excel for Mac, whose source list is only Excel Workbook, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online List, OData, Blank Table and Blank Query. If From PDF is missing from your Windows Excel, that build does not include it.

What it does well, and where it fails

Microsoft documents the problem directly: "In cases where multi-line rows aren't properly identified, you might be able to clean up the data using UI operations or custom M code. For example, you could copy misaligned data to adjacent rows using Table.FillDown, or group and combine adjacent rows using Table.Group."

Slow extractions are tuned in the underlying Pdf.Tables step, in the formula bar or Advanced Editor. For a heavy file, Microsoft's advice is to "try selecting pages one at a time or one small range at a time using the StartPage or EndPage options."

Cleaning the extracted table

The statement-specific subset of cleaning messy data in Excel; if the editor is new, start with Power Query for beginners.

1. Promote headers, delete the page furniture

Use Home > Use First Row as Headers if the real titles came through as row 1. Then click the filter arrow on the date column and uncheck everything that is not a date — Date, Page, Balance brought forward. That one step clears every page. Keep the opening-balance line, but note which row it is: it must be excluded from your totals.

2. Split a column holding several fields

Select the column — it must be a Text data type — then Home > Split Column > By Delimiter. Pick Space in the Select or enter a delimiter drop-down, and under Split at choose Left-most delimiter, Right-most delimiter or Each occurrence of the delimiter.

The statement trick is that the date is a fixed token at the front and the balance a fixed token at the back. Split by Space / Left-most delimiter to peel off the date, then split the remainder by Space / Right-most delimiter to peel off the balance; whatever survives is the description. New columns take the original name plus a period and a number — Description.1, Description.2.

3. Repair the dates with a locale

Do not trust automatic detection. 03/04/2026 is 3 April in the UK and UAE and 4 March in a US-locale workbook, and both parse without error — silent and plausible, the worst kind of bug.

Right-click the column header, select Change Type > Using Locale, and in the Change Type with Locale dialog box pick Date plus the bank's locale. Microsoft is explicit that "this locale overrides the Power Query locale setting".

4. Type the money columns

Select the column, then Home > Data Type and pick Fixed decimal number for money — four digits to the right, 19 to the left — which avoids the floating-point dust that leaves a rec 0.0000001 short.

A value that refuses to convert is not a number yet. Parenthesised and trailing-minus negatives normally arrive as text; strip the characters with Home > Replace Values first. To repair them on the sheet instead, this handles all three sign conventions, ignores blanks, and flags what it cannot parse:

=IF(TRIM(A2)="","",IFERROR(IF(OR(LEFT(TRIM(A2),1)="(",LEFT(TRIM(A2),1)="-",RIGHT(TRIM(A2),1)="-"),-1,1)*VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"(",""),")",""),"-",""),",","")),"CHECK"))

5. Collapse the multi-line rows

Where a description wrapped, the numeric columns are blank on the continuation rows. Select the date column and use Transform > Fill > Down so those rows inherit their parent's date, then Home > Group By on the date plus a stable key, with Sum on the amounts and All Rows as a second aggregation. Group By has no text-join operation, so rebuild the wrapped description in a custom column off that grouped table — if the All Rows column is Details, that is Text.Combine([Details][Description], " "). Do not delete blank-amount rows before reading them: a reference number on a continuation line is often the only thing that will match the row to an invoice.

6. Two columns, or one signed amount

Most statements print Money out and Money in separately; reconciliation is easier against one signed amount. With Money out in C and Money in in D:

=IFERROR(VALUE(D2),0)-IFERROR(VALUE(C2),0)

Positive is money in, and blanks resolve to 0. Note the trade IFERROR makes: text becomes 0 instead of an error — which is exactly why the balance check below is not optional. Keep the original pair of columns for review.

Validate the extraction before you use it

Most guides stop at "your data is in Excel". That is where the risk starts: a mis-extracted statement looks entirely normal. No product feature required, just arithmetic.

Lay the sheet out so it does the bookkeeping: row 2 is the opening-balance row, carrying a balance in F2 but no amount in E2, with transactions from row 3. Signed amounts in E, the printed balance in F, the statement's closing balance in H2.

Whole statement. Opening balance, plus money in, minus money out, equals the printed closing balance. Starting the sum at E3 is what keeps the opening balance from being counted twice:

=IF(ROUND($F$2+SUM($E$3:$E$501)-$H$2,2)=0,"EXTRACTION OK","OUT BY "&TEXT(ROUND($F$2+SUM($E$3:$E$501)-$H$2,2),"#,##0.00"))

Row by row tells you where it broke: each row's printed balance equals the previous row's balance plus that row's signed amount. In G3, filled down — the F3="" guard stops empty rows below your data reporting a false failure:

=IF(F3="","",IF(ROUND(F3-F2-E3,2)=0,"ok","CHECK"))

Then =COUNTIF($G$3:$G$501,"CHECK"). Zero means every row agrees with the balance the bank printed beside it. Non-zero, and the first CHECK is where extraction went wrong — usually a page break or a wrapped description.

Three caveats, or the check fails on good data:

• Exclude the opening-balance row from the sums. It is usually a row inside the table, not a header, and counting it as both the opening balance and a movement guarantees a false failure.
• Normalise signs first. Separate debit/credit columns, parentheses and trailing minus all mean negative, and text that merely looks negative contributes 0 to SUM.
• Fix misalignment, never plug it. A hardcoded adjustment buries what the check exists to find.

Zoho Books puts the same standard at the other end: "To reconcile your accounts, you need to make sure that the Closing Balance and Cleared Amount are the same and the difference is zero."

Scanned statements: what actually works

There is no OCR in Excel's PDF connector. Its only documented capability is Import, and Pdf.Tables "returns any tables found in pdf" — a scan has no text objects, so there is nothing to find and Navigator offers nothing usable. Microsoft's OCR ships elsewhere: Azure Document Intelligence, and SharePoint's OCR service, whose supported file types include ".pdf (scanned and hybrid)".

Excel's own image-to-table feature is Data > From Picture > Picture From File or Picture From Clipboard (Windows 10 version 1903 or later, Windows 11, macOS and Excel for the web; Windows needs the Edge WebView2 Runtime). Its documented inputs are an image file, a screen clipping or a camera scan — not a PDF. So for a scan, export each page as an image first, or use a real OCR tool — then run the balance validation, because a misread digit is still a plausible number.

Better still, check whether your online banking offers CSV or OFX for the same period. That skips extraction entirely.

Before you upload to a free online converter

A statement holds the account number or IBAN, the holder's name and address, and a record of who they pay and when. If you are an accountant that file is not yours, and uploading it puts client data on third-party infrastructure under retention terms nobody reads. Keep extraction local.

Doing it inside Excel with HISAB 360

Convert statements monthly for several clients and the Power Query route becomes a set of queries you maintain by hand. HISAB 360 is one option — a paid Excel add-in with an AI panel docked in the sheet. Its document extraction pulls tables out of PDFs, bank statements and invoices included, into a structured range; you can then ask in plain English for the merged column split, the dates re-typed for the bank's locale or the signed-amount column, and it writes and applies the Power Query or formulas to the live sheet.

It earns its place after extraction: the AI reconciliation matches statement lines to ledger rows on amount and date tolerance, handles one deposit covering several invoices, matches descriptions that do not agree word-for-word, flags duplicates and produces a report. Ledgers in QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks or Sage read into the sheet, and post back, over an OAuth connection you authorise.

Honest limits: Windows desktop Excel only, no Mac and no Excel for the web. It is paid after a 15-day free trial (every feature plus 50 HISAB AI credits, no credit card, once per machine); there is no permanently free tier, and pricing starts at $7/month for bring-your-own-AI-key. Every suggested match, like every ledger write, should be reviewed before you approve it.

Then categorise and reconcile

1. Categorise from the description with a keyword lookup table, not 400 typed labels. Where descriptions vary month to month, fuzzy matching in Excel groups "AMZN MKTPLACE" and "AMAZON.CO.UK".
2. Reconcile in the free bank reconciliation template: a bank side, a book side, a DIFFERENCE row printing RECONCILED or OUT BY <amount>, and a Matched? flag on each transaction sheet built from COUNTIFS against the other side for 200 rows. Its stated limit: amount-only matching cannot handle one deposit covering several invoices.
3. Slot it into the close — see building a month-end close checklist in Excel.

Never reconcile against an extraction you have not proved. Opening balance plus movements equals closing balance — and if it does not, the problem is your data, not your bank.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.