Somebody sends you a PDF with a table in it. A supplier price list, a quarterly sales summary, a bank statement, a payroll export from software you don’t have access to. You need the numbers in a spreadsheet so you can sort them, total them or compare them with last month.
So you open the file, drag across the table, copy, and paste into Excel. And you get one long column of mush.
I’ve watched people give up at this point and retype the whole thing. For a ten-row table that’s annoying. For a forty-page statement it’s a lost afternoon, plus the typos you’ll only find when the totals don’t match. There are better ways, and which one you pick depends mostly on what kind of PDF you’re holding.
Why copy and paste falls apart
A PDF doesn’t store a table the way a spreadsheet does. There are no cells inside the file. What’s actually there is a list of text fragments, each with a position on the page. Your PDF reader draws them in the right places, so your eyes see rows and columns, but the file itself has no idea that “Invoice 1042” and “$318.00” belong on the same row.
When you paste, Excel receives the text in whatever order the PDF happens to store it. Sometimes that’s left to right. Often it isn’t. Wrapped descriptions split into two rows, empty cells vanish, and every column after the gap shifts left by one.
First, work out what kind of PDF you have
This takes ten seconds and saves a lot of guessing. Try to select a single word in the table with your cursor.
- If the text highlights, it’s a digital PDF. Some software exported it, and the text is real. Extraction will usually work well.
- If the whole page highlights as one block, or nothing highlights at all, it’s a scan. The page is a picture of text, and you’ll need OCR (optical character recognition) before any tool can read it.
Most bank statements, invoices and reports that come out of accounting systems are digital. Anything that went through a printer and a scanner, or got photographed on a phone, is not.
Option 1: Excel’s own PDF import
Recent versions of Excel on Windows can read PDFs directly. Go to Data, then Get Data, then From File, then From PDF. Excel shows a list of the tables it found, you pick one, and it loads into a sheet.
When it works, it’s the best option, because the data lands in Power Query and you can clean it there. It struggles with tables that span pages (you often get each page as a separate table) and with statements where the layout isn’t a clean grid. It also isn’t available in every edition, and the Mac version doesn’t have it.
Option 2: Open the PDF in Word first
Word can open a PDF and convert it into an editable document. Tables often come through as real Word tables, and those paste into Excel with the columns intact.
It’s clumsy, and I wouldn’t rely on it for anything long. For a two-page report, though, it’s worth a try.
Option 3: A dedicated converter
For anything regular, like monthly statements or a report you get every week, I’d use a tool built for the job. A free PDF to Excel converter will take the whole file and give you back an .xlsx with each table on a sheet, which is far quicker than fighting with Power Query on a 30-page document.
A couple of things to keep in mind with any online converter. Read what it says about file retention, because you’re uploading a document that may hold account numbers or salaries. If the file is sensitive and the tool doesn’t tell you how long it keeps uploads, don’t use it. And check the output before you trust it (more on that below).
Option 4: Scanned PDFs need OCR first
If your PDF is a scan, none of the above will do much until the text has been recognised. Run the file through an OCR tool first, then convert. Adobe Acrobat does this, as do free tools.
Expect to fix things by hand afterwards. OCR commonly confuses 0 and O, 1 and l, and it can drop decimal points on faint print. On a table of money, one missing decimal point turns $41.50 into $4,150, so this step matters.
Check the numbers before you use them
This is the step people skip, and it’s the one that bites. Whatever method you use, do three quick checks:
- Row count. If the statement says 87 transactions, your sheet should have 87 rows.
- Totals. Sum a column and compare it with the total printed on the PDF. If they match, you’re almost certainly fine.
- Dates and numbers stored as text. If a column won’t sum, or dates sort in a strange order, Excel is treating them as text. Select the column and use Data, then Text to Columns, then Finish to force it to convert.
The totals check alone catches nearly every problem I’ve run into.
Which one should you use?
My honest take: try Excel’s import first if you have it, because it keeps everything inside one program. If the table spans pages or the import mangles it, move to a converter and don’t waste time wrestling with it. Keep the Word trick for one-off short files, and save OCR for the times when you truly have no digital copy.
And if the same PDF report lands in your inbox every month, ask whoever sends it whether they can export a CSV instead. Most accounting and payroll systems can. It’s the one fix that makes the whole problem go away, and people rarely think to ask.
