How to Extract Data From PDF to Excel Automatically

How-to — 2026-10-31 — by Mahmoud Zalt

Get tables and fields out of PDFs and into Excel without copy and paste, using Power Query, a converter, or an AI Employee that does it on arrival.

There are two completely different problems hiding behind this one search. One is a PDF that was exported from software and still contains selectable text. The other is a PDF that is really a picture of a page. They look identical in a viewer and they behave nothing alike.

There is a five second test. Open the PDF and try to select a line of text with your cursor. If the text highlights, you have a text PDF and the easy path is open to you. If your cursor draws a box over an image instead, you have a scan, and everything below about Power Query will return an empty result.

Which method should you use to get PDF data into Excel?

Choose by two things: whether the PDF has real text, and whether the layout is the same every time. Consistent text PDFs go to Power Query, which is free and already installed. Varying layouts or scans need a reading tool. Everything else is a trade between setup time and running cost.

Method	Best for	Where it falls down
Power Query PDF connector	Text PDFs with clean tables, any volume, already paid for	Finds nothing in scans, and struggles when a row wraps across lines
A dedicated PDF converter	One-off jobs and simple table layouts	Manual, per-file, and usually a per-page cost at volume
Template rules in a document tool	A high volume of one identical layout	Every new supplier layout means a new template to build and maintain
AI reading the document	Mixed layouts, scans, many senders, fields rather than tables	Costs a little per page and is never guaranteed correct, so it needs a review step
A script with a PDF library	Repeatable jobs where you want full control and no per-page fee	You own the maintenance, and table detection is genuinely hard to get right

How do you import a PDF into Excel with Power Query?

Excel has a PDF connector built in, and it needs nothing installed. It reads the file, finds anything that looks like a table or a page of text, and shows you the candidates in a Navigator window so you can pick. Microsoft lists no prerequisites for it at all.

Importing a PDF into Excel

Open a blank workbook and go to the Data tab — Then Get Data, then From File, then From PDF. Browse to your file and choose Open. If From PDF is not in that menu on your machine, see the note under these steps.
Pick your tables in the Navigator window — Excel lists each detected table and each page separately. Click through them and watch the preview on the right. Tables are usually cleaner than pages, but on a badly structured PDF the page view sometimes wins.
Choose Transform Data rather than Load — Load drops the raw result straight into a sheet. Transform Data opens the Power Query Editor, which is where you will actually fix the mess. Get into the habit of always transforming first.
Promote headers and set column types — The first row is usually your header and Excel will not assume it. Promote it, then set each column to text, number or date explicitly so that invoice numbers with leading zeros survive.
Fix rows that wrapped onto two lines — A description that ran long in the PDF becomes two rows with blanks in the other columns. Microsoft's own guidance points at filling down from the row above, or grouping and combining adjacent rows, to repair this.
Close and load, then refresh instead of rebuilding — Close and Load writes the cleaned table into your sheet and remembers every step. Next month, point the same query at the new file and hit Refresh. That is the part that turns this from a task into an automation.

Two practical notes. To do a whole folder of PDFs at once rather than one file, Microsoft points you at the Folder connector instead of the PDF one. And on very large PDFs the connector can be slow or time out, which their documentation suggests fixing by pulling a page range at a time.

One more thing worth knowing before you plan around it. The connector is documented for Excel, but users have reported it missing on some builds, particularly on Mac and on non-subscription licences. That is a community report rather than a documented limit, so check your own Data menu first before you design a process around it.

Why does the extracted table come out wrong?

Because a PDF does not contain a table. It contains text placed at coordinates on a page, and every tool has to guess where the rows and columns were. When the guess fails you get the same handful of symptoms, and each one has a standard fix.

Columns merged into one cell usually means the PDF used spaces rather than real column separation, and splitting by a delimiter or by a fixed number of characters gets you back. Numbers arriving as text usually means a currency symbol or a thousands separator came along for the ride, so strip those before you set the type.

The worst case is a table that spans several pages with a repeated header on each one. You will get the header appearing as a data row every few dozen rows. Filter those out in the query rather than deleting them in the sheet, or they will come straight back on the next refresh.

How do you make it truly automatic, with no clicking?

Power Query still requires you to be there, opening a workbook and pressing Refresh. Fully automatic means something is watching for the file arriving and doing the work before you know it exists. That is a different kind of tool, and it is where a reading approach earns its cost.

This is the job we built Sistava around. You hire an AI Employee, tell it in plain English which folder or inbox to watch, which fields matter, and which sheet to write them into. It handles the layout differences that break a template, and it comes back to you about the ones it cannot resolve.

Whichever route you take, the spreadsheet work does not end at extraction. Once the rows land you still need to validate them: totals that should reconcile, dates that should fall in range, duplicate invoice numbers that should never appear twice. That is formula work, and it is worth writing the checks before you trust the import.

A handful of checks catches most of what goes wrong. Sum the line items and compare against the stated total. Flag any date outside the period you expected. Count duplicates on the identifier column. If any of those fire, the row goes to a human before it goes anywhere else.

What about scanned PDFs, photos and handwriting?

This is where honesty matters more than enthusiasm. Text recognition on a scan is a prediction, not a read, and its accuracy moves with things you often cannot control. Microsoft's own documentation on the limits of text recognition names scan quality, resolution, contrast, lighting, rotation and text size and density as factors that all affect the result.

Handwriting is the hard case. Cursive, overlapping characters and unusual letterforms produce errors even in the best systems. If your source documents are handwritten, plan for a human on every one rather than on the exceptions.
The errors look like real data. A misread digit does not arrive flagged as suspicious. It arrives as a number, in the right column, formatted correctly, and it will sit in your spreadsheet looking exactly as trustworthy as the correct ones.
Use a confidence threshold, and set it on your own documents. Good extraction tools return a confidence value per field. Microsoft's guidance is to sample your own content, see the range of scores you get, and pick the cut-off from that rather than accepting a default.
Anything feeding accounts or a contract gets checked. Amounts, dates, tax numbers and party names in anything that touches your books or a legal record need a person to confirm them. The check takes seconds. Unwinding a wrong number three months later does not.

None of this means avoid automation. It means put the review in the right place. Auto-accept the high confidence fields, hold the rest in one queue, and glance at the queue daily. You keep almost all of the time saving and you lose the failure mode that actually costs money.

A word on cost, since it decides most of these choices. Power Query is free with Excel and stays free at any volume, which makes it the right answer for a lot of people no matter what a vendor tells you. Converters and document platforms usually charge per page or per document, so their cost grows exactly as fast as your success does.

A reading approach sits in the middle: a small cost per page, no template building, and no rebuild when a supplier changes their layout. If you want to try the reading half of this without paying for anything, several of our free tools from Sistava run in the browser with no account, which is enough to see how a model handles your particular documents before you commit to a process.

Whatever you build, keep the original PDFs. Extraction gets better over time, your requirements change, and the day someone questions a figure you will want the source document rather than the row you derived from it. Storage is cheap and re-scanning a filing cabinet is not. Whether you build this yourself or hand it to Sistava, make keeping the source file part of the process rather than an afterthought.

Frequently asked questions

FAQ

Can Excel extract data from a PDF by itself?

Yes, for PDFs that contain real text. Go to Data, then Get Data, then From File, then From PDF, and Excel opens a Navigator window listing every table and page it detected. Choose Transform Data to clean the result in the Power Query Editor before loading it. Microsoft lists no prerequisites for the connector. It will find nothing in a scanned PDF, because there is no text in the file to find.

How do I convert many PDFs to Excel at once?

Do not use the PDF connector one file at a time. Microsoft points you at a multi-file connector such as the Folder connector, which reads every file in a folder and combines them into one query. Build and clean the query on a single representative file first, then switch the source to the folder so the transformation applies to all of them.

Why is my PDF import empty or missing rows?

Usually one of three causes. The PDF is a scan with no text layer, so there is nothing to read. The table has no real structure, so nothing was detected as a table and you need the page view instead. Or rows wrapped across lines and appear blank in some columns, which is repaired by filling down from the row above or by grouping and combining adjacent rows.

How accurate is automatic PDF to Excel extraction?

On a text PDF with a well-formed table, the text itself is exact because it is being copied rather than recognised. What goes wrong is structure: columns merged, rows split, headers repeated. On scanned documents the text itself becomes a prediction and accuracy depends on scan quality, contrast, rotation and whether anything is handwritten. Measure it on your own documents rather than trusting a quoted figure.

Do I need to check extracted data before using it?

For anything that touches money, tax or a contract, yes. Set a confidence threshold, auto-accept fields above it, and hold the rest for a human glance. Add arithmetic checks in the sheet too: line items should sum to the stated total, dates should fall inside the expected period, and identifiers should not repeat. Those catch the errors that look completely valid.

Is Power Query or an AI tool better for this?

They answer different questions. Power Query is free, exact on text PDFs, and repeatable with a refresh, but it needs a consistent layout and cannot read scans. An AI reading tool handles varying layouts, many senders and scanned pages, but costs a little per page and needs a review step. Plenty of teams use Power Query for the sources they control and a reading tool for everything else.