ε€€VALUES

πŸ“Š Getting a table out of a PDF into Excel

If you are retyping tables out of PDFs by hand β€” you don't have to. Here are three ways that cost nothing. One of them usually works.

First β€” which kind of PDF is it?

Ten seconds saves an hour. Open the PDF and try to drag-select the text in the table.

Method 1 β€” let Excel read it (the cleanest)

Excel β†’ Data β†’ Get Data β†’ From File β†’ From PDF

Pick the PDF and Excel lists the tables it found on each page. Choose one and press Load. The columns are split for you.

Don't see that menu?

It arrived with Microsoft 365 and Excel 2021. Excel 2016 and 2019 do not have it β€” use method 2.

Method 2 β€” copy, paste, then split the columns

  1. Select the table in the PDF and copy (Ctrl+C).
  2. Paste into Excel (Ctrl+V). Often each row lands in a single cell.
  3. Select that column β†’ Data β†’ Text to Columns β†’ Delimited β†’ tick Space or Comma β†’ Finish.

If leading zeros vanish or numbers turn into dates, set that column to Text on the last step.

Method 3 β€” go through Word first

Word can open a PDF and often rebuilds the tables as real tables. Copy the table from Word and paste it into Excel β€” the columns come across intact.

Right-click the PDF β†’ Open with β†’ Word

Tables with many merged cells often come out twisted this way. Look it over after pasting.

Where all three usually go wrong

This kind of tableWhat happens
Merged cellsThe merge comes apart and every row below shifts by one
A long table over several pagesThe header row repeats on every page and has to be deleted
Numbers with commas1,234 arrives as text, so it won't add up
Cells with two linesOne cell splits into two rows

Two or three tables, you can fix by eye. Thirty or a hundred, and it becomes a job.

When it is a scan (image PDF)

You need text recognition (OCR) first. It is never 100% β€” it mixes up 0 and O, 1 and l. For money or head counts, always check against the original.

If you do this every week

We sell a small Windows program that takes a whole folder of PDFs and pulls just the tables into one Excel file. We worked hardest on the places listed above: repeated header rows are merged into one, and numbers with commas come back as numbers you can add up.

No internet needed, nothing leaves your computer. A monthly subscription you can cancel any time.

πŸ“Š Table Grabber β€” $3.49 / month

Where this comes from

Menu names are from current Microsoft 365 Excel and Word. Older versions may differ.
Everyone’s situation is different. The numbers here are examples β€” swap in your own.

VALUES is a small shop that makes tools to take work off your desk Β· More guides Β· The tool shelf Β· Bookshop Β· Free downloads Β· Seller information