Getting PDF tables into Excel

Retyping a table from a PDF is slow and error-prone. How to convert a PDF to a spreadsheet, and the checks that turn the result into figures you can rely on.

A price list, a bank statement, a supplier report: the figures are there, in a PDF, and you need them in a spreadsheet to sort, add up or compare. Copying and pasting usually dumps everything into a single column. Retyping invites mistakes. Conversion gets you most of the way; a few checks do the rest.

What converts well

Real tables in a PDF made by software. If the PDF was exported from an accounting program, a spreadsheet or a web page, its figures are real characters and the columns are regular. These convert cleanly.

Regular structures. One header row, then lines that all have the same columns.

What converts less well: tables whose cells span several lines, tables split in the middle of a row over two pages, and scanned documents, where the figures are part of an image.

Converting

Open PDF to Excel, add the PDF and download the .xlsx file. It opens in Excel, LibreOffice Calc, Numbers or Google Sheets.

If the tables you need are on a few pages of a long document, extract those pages first. The spreadsheet will be shorter and easier to check.

Five checks before you trust the figures

  1. Numbers stored as text. If a column does not add up, its cells are probably text. In Excel they are aligned to the left by default. Select the column and convert it to numbers.
  2. Decimal and thousands separators. "1.250,00" and "1,250.00" are not read the same way depending on the language settings of your spreadsheet. Check one large amount.
  3. Dates. Check that 03/04 is the day and month you expect.
  4. Merged or shifted cells. A long label can push the values of a line one column to the right. Sort or filter the column: the intruders appear immediately.
  5. Totals. Recalculate a total that is printed in the PDF. If your sum matches, the column is sound.

Cleaning up

  • Delete the header rows that were repeated at the top of each page.
  • Remove empty rows and columns.
  • Freeze the header row and turn the range into a table to sort and filter comfortably.

When you only need to read or send the table

If the aim is not to calculate but to reuse the content in a report, PDF to Word keeps the table inside its page, with the text around it. And to send a finished spreadsheet to someone who should not modify it, Excel to PDF does the reverse trip.