Retyping a 12-page price list into Excel is slow, boring and a reliable way to introduce typos. Most PDF tables can be pulled into a spreadsheet in minutes instead. The catch is that the result almost always needs some cleanup: numbers that won't add up, negative signs in the wrong place, and header rows repeated on every page. This guide covers how to pick the right method for your PDF and how to fix the errors each method leaves behind.
Step 1: Check what kind of PDF you have
This 10-second test decides which method will work. Open the PDF and try to highlight a few words in the table with your cursor, or press Ctrl+F (Cmd+F on a Mac) and search for a number you can see on the page.
- The text highlights or the search finds it: it's a text-based PDF, usually exported from Word, Excel or accounting software. You can extract the data directly, and it should be exact.
- Nothing highlights: it's a scanned PDF, which is really a picture of a page. You need OCR (optical character recognition) to turn the picture into text, and OCR makes mistakes you'll have to check for.
Step 2: Four ways to get the table out
Method A: Excel's Get Data From PDF (Windows)
If you have Excel for Microsoft 365 on Windows, this is the best option for text-based PDFs, because it reads the actual table structure.
- Go to Data > Get Data > From File > From PDF and pick your file.
- The Navigator lists what it found: items named Table001, Table002 and so on, plus whole Page items. Click each one to preview it.
- Choose Transform Data rather than Load if anything looks off. This opens Power Query, where you can delete junk rows, promote headers and set column types before the data reaches your sheet.
- For a table that runs across several pages, select all the matching table items (tick Select multiple items) and combine them in Power Query with Home > Append Queries.
Microsoft notes the PDF connector needs .NET Framework 4.5 or later. It is not available in Excel for Mac, and Microsoft says there's no way to switch it on there, so Mac users should use one of the other methods.
Method B: Open the PDF in Word, then copy the table
Word for Windows can open a PDF directly (File > Open) and convert it into an editable document. Tables often come through as real Word tables, which you can copy and paste into Excel with the columns intact. Complex layouts may shift, so compare the result with the original.
Method C: Scanned PDFs with OCR
- Excel's Data From Picture: take a clear screenshot of the table, then use Excel's From Picture option on the Data tab. Excel shows a preview and flags cells it isn't sure about. Review every flagged cell before you insert the data.
- Google Drive: upload the PDF, right-click it and choose Open with > Google Docs. Google runs OCR and gives you editable text. Table structure often doesn't survive, so you may need to paste the text into Google Sheets and use Data > Split text to columns.
Whichever you use, a cleaner scan gives cleaner results: straight pages and 300 dpi or higher make a real difference.
Method D: Copy, paste and split
For a short, simple table, select the text in your PDF reader, paste it into Excel, then split it with Data > Text to Columns. If everything lands in one column, choose Delimited and try Space or Tab as the separator. This breaks down when descriptions contain spaces, so it suits number-heavy tables best.
About free online converters: they can work well, but you're uploading your file to someone else's server. Read their privacy policy first, and don't upload anything confidential to a site you don't know.
Step 3: Fix the seven most common errors
We tested every formula below on a sample sheet. In each one, A2 is the first cell of the messy column. Put the formula in a helper column, fill it down, and then paste the results back as values.
1. Numbers stored as text
The tell-tale signs are small green triangles, numbers aligned to the left, and a SUM that returns 0. Select the column, click the warning icon and choose Convert to Number. For a whole column at once, select it and go to Data > Text to Columns > Finish, which makes Excel re-read every cell.
2. Thousands separators and currency symbols
Values like $1,050 stay as text. Strip the symbols out and convert:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""))3. Negative numbers with a trailing minus
Many reports print 125- instead of -125. This formula moves the minus sign to the front:
=IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2))4. Negatives in parentheses
Accounting-style (89.10) means minus 89.10:
=IF(LEFT(A2,1)="(",-VALUE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")),VALUE(A2))5. Header rows repeated on every page
A multi-page table usually brings its column headings along on each page. Turn on Data > Filter, filter the first column for the heading text, delete those rows, and then clear the filter. Look for page footers such as "Page 3 of 12" at the same time.
6. Descriptions wrapped onto two rows
Long product names often split into a second row with blank number columns. Filter a number column for (Blanks) to find these rows, join each piece of text onto the row above (=B2&" "&B3), and then delete the leftover rows.
7. Dates that aren't real dates
If a date like 31/12/2025 won't sort, it's text, often because it's in day/month order. Rebuild it from its parts:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))Then format the column as a date. Our guide to cleaning messy data in Excel covers more date patterns.
OCR look-alikes
Scanned tables bring their own errors: the letter O instead of a zero, a lowercase l for a 1, S for 5, and a comma read as a period. A quick way to find them is =ISNUMBER(A2) after converting. Any FALSE in a number column hides a character that doesn't belong.
Step 4: Check the result before you trust it
- Count the rows. Compare your row count with the PDF. If the PDF has 21 products on each of 10 pages, you should have 210 rows.
- Match the totals. If the PDF prints a total or subtotal,
=SUM()your column and compare. A mismatch points to a missed row or a misread number. - Spot-check at random. Pick 10 rows with
=RANDBETWEEN(2,211)and compare each one with the PDF, cell by cell. - Look at the extremes. Sort each number column smallest to largest. OCR errors and shifted columns tend to show up at the top or bottom.
These are the same checks we run on client work. Our scanned-PDF sample recovered 21 of 21 rows, with all 126 cells verified against the scan.
FAQ
Why is my PDF not converting to Excel properly?
Usually the PDF is a scan (an image), so there's no real text to extract, or the table uses merged cells and wrapped text that converters split across rows. Scans need OCR, and every converted table needs a check of row counts and totals.
Can I use Get Data From PDF on a Mac?
No. Microsoft says the Power Query PDF connector isn't available in Excel for Mac. Use Word, an OCR tool or Google Drive to convert the PDF first, or do the import on a Windows PC and open the workbook on your Mac.
Why do my converted numbers show as text?
Converters often bring numbers across as text, especially with currency symbols, thousands separators or trailing minus signs. Use Data > Text to Columns > Finish for plain numbers, or a SUBSTITUTE plus VALUE formula when symbols are mixed in.
Is it safe to use free online PDF converters?
It depends on the site. You're uploading the file to someone else's server, so read the privacy policy, and don't upload confidential documents to services you don't know.