π 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.
- The words highlight in blue β it is a text PDF. All three methods below work.
- Nothing highlights, or a whole rectangle is selected like a picture β it is an image PDF (a scan). To the computer it is a photo, so copying gives you nothing. Jump to βWhen it is a scanβ.
Method 1 β let Excel read it (the cleanest)
Pick the PDF and Excel lists the tables it found on each page. Choose one and press Load. The columns are split for you.
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
- Select the table in the PDF and copy (Ctrl+C).
- Paste into Excel (Ctrl+V). Often each row lands in a single cell.
- 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.
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 table | What happens |
|---|---|
| Merged cells | The merge comes apart and every row below shifts by one |
| A long table over several pages | The header row repeats on every page and has to be deleted |
| Numbers with commas | 1,234 arrives as text, so it won't add up |
| Cells with two lines | One 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.
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 / monthWhere 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