On the first Monday of every month, Rosa’s produce supplier emails her a price list. It’s a 14-page PDF covering about 600 items, sorted A to Z, from “Apples, Fuji” to “Zucchini, baby.” Rosa runs a café in Tucson, so alphabetical order is the one order she has zero use for. She wants to know what got more expensive, by how much, and whether the avocados are still worth it at this price.
All the table data she needs is sitting right there on the screen, and none of it will move.
Why the Table Won’t Behave
Here’s the frustrating bit. A PDF only looks like a table. Open the file’s guts (not that you’d want to) and you’d find something more like a recipe for drawing a page. Put “2.49” about here. Put “Avocados, Hass” a bit further left. Draw a thin gray line under both. The file never actually says the price belongs to the avocados, and it has no clue the third column is money.
So when Rosa does the natural thing (select, copy, paste into a spreadsheet), the results get weird fast. Last month the whole table collapsed into column A. Another time the columns survived but the prices pasted as text, and her SUM formula cheerfully reported a total of $0.00. Page breaks, merged headings and “Item / Unit / Price” repeated at the top of all 14 pages don’t help either.
Scanned or Digital? Check First
Here’s a ten-second test worth doing first: drag your cursor across one word in the PDF. Does just that word light up blue? Good news, the file has real text, probably because the supplier’s accounting software spat it out. Does the entire page turn blue like a picture? Then someone printed it, ran it through a scanner and emailed you the photo, and every tool will need OCR (optical character recognition) to read it before rebuilding the table.
The first kind converts beautifully. The second kind works, but leave time to double-check, because OCR has a soft spot for reading a faded 8 as a 3.
Option One: Excel’s Own Importer
Not many people know this, but the Windows version of Excel in Microsoft 365 can extract tables from PDF files by itself. Rosa found it under Data > Get Data > From File > From PDF. Point it at the price list and a Navigator panel pops up listing each table it recognized, which you can load right away or tidy up in Power Query.
The catch? Mac Excel, older Office installs and phones don’t have it, and the supplier’s email always seems to land when Rosa is on her phone between deliveries.
Option Two: A Browser Converter
That’s when a PDF to Excel converter in the browser earns its place. Rosa uses PDFLeader, where you can convert PDF to Excel from any phone or laptop with nothing installed. She drops in the price list, the tool works out where the table starts and ends and which bits are rows and columns, and back comes an XLSX workbook she can open in Excel.
One tip from their own help page stuck with her: clear table lines and consistent columns in the source make for a cleaner spreadsheet. She’s since asked the supplier for a less fancy export, and they said yes.
Cleaning Up the Result
Whichever route you take from PDF to Excel, the result deserves a quick look before you build anything on it. Rosa’s checklist:
– Numbers sitting on the left side of their cells are usually text in disguise. Excel’s Convert to Number fixes them, or =VALUE() in a spare column.
– If an entire row ended up squashed into one cell, Data > Text to Columns will pull it apart.
– Those “Item / Unit / Price” lines from the top of each page? Delete all thirteen of the extras.
– Merged heading cells get unmerged, so every column has one clean name.
– Finally, she spot-checks a couple of prices against the PDF, because one wrong digit near the top ruins every formula underneath it.
The Step That Makes It Sortable
Now for the part that makes all of this worth it. Rosa clicks anywhere in her data and hits Ctrl+T (Cmd+T if you’re on a Mac), and Excel turns the plain range into a proper Excel table. Filter arrows appear on every heading, any new row automatically picks up her formulas, and there’s a tick box that adds a totals row at the bottom.
Rosa goes one step further. Last month’s PDF to XLSX file lives on a second tab, and an XLOOKUP column drags each item’s old price over next to this month’s. A simple percentage formula beside it, sorted from largest to smallest, shows her the five items that jumped the most. That used to take an afternoon with a highlighter. Now it takes about ten minutes.
Conclusion
A PDF is a great envelope for numbers and a lousy place to work with them. Get the table out, give it a five-minute scrub, press Ctrl+T, and the same monthly report turns into something you can sort, filter and wave at your supplier on the phone. If one PDF keeps arriving every month, set this up once and you’ll never retype a price again.
