How to Extract Data From a PDF Into Excel

Sometimes you do not need to convert an entire PDF. You just need the useful bit: a table, list of transactions, reference numbers or several columns buried inside a document.

That sounds simpler. Depending on the PDF, it can be.

First work out what kind of PDF you have

If you can select the text in the PDF, the document probably contains machine-readable text. Excel and other tools have a better chance of recognising tables directly.

If every page is effectively a photograph, you are dealing with a scanned PDF and will normally need optical character recognition, or OCR, before the data becomes usable.

Try Excel’s PDF import tools

In supported versions of Excel, use the Data tab and the Get Data options to import from a PDF. Excel can identify tables and other structures in the document and let you preview them before loading.

Select only the table or data you actually need where possible. If it needs cleaning first, use the transformation options before loading it into the worksheet.

Expect imperfect tables

A PDF stores presentation, not necessarily tidy rows and columns. A table that looks perfect to a human can contain merged cells, repeated headings, split fields or text positioned individually on the page.

After extraction, check whether columns have shifted and whether every source row made it across.

Scanned PDFs need extra checking

OCR can turn an image of text into editable data, but it can also confuse similar characters. A zero can become the letter O, a 1 can become an I, and decimal points can vanish at exactly the moment you would rather they did not.

For financial, operational or other important data, sample-check the extracted values against the source.

Clean the result in Excel

Once extracted, you may need to remove repeated page headings, convert numbers stored as text, standardise dates, split combined fields and remove blank rows.

See our guide on cleaning up a messy Excel spreadsheet for a sensible order of attack.

Extracting data versus converting the whole PDF

If you want the entire document converted, start with our broader guide on how to convert a PDF to Excel. If you only need particular tables or fields, extraction can save a lot of unnecessary clean-up.

Can’t be faffed?

If the PDF is huge, scanned, inconsistent or there are dozens of files to process, Faff It Off.

Send us the documents, tell us exactly which information you need in Excel, and we’ll quote for extracting and cleaning it.