Most Excel trainers say, “Just copy-paste from the PDF!” That’s not reading—it’s guessing. And when your CFO sends a 47-page supplier invoice as a scanned PDF, guessing gets you fired. Excel has zero built-in PDF parsing capability. Not in 2024. Not in 365. Not even with Power Query’s latest update. If someone tells you otherwise, they’ve never tried extracting line-item totals from a rotated, OCR-broken invoice from Jiangsu Precision Tools.
The Setup
You’re auditing Q1 procurement for three vendors. Finance sent you three files: Invoice_Jiangsu_2024-03-12.pdf, Acme_Corp_Q1_Summary.pdf, and GlobalLogistics_DeliveryNotes.pdf. None are editable. You need the line items—Item Code, Description, Qty, Unit Price, and Date Shipped—into Excel for reconciliation against SAP.
| Item Code | Description | Qty | Unit Price | Date Shipped |
|---|---|---|---|---|
| JP-8842 | CNC Bearing Assembly (Grade A) | 12 | $142.50 | 2024-03-12 |
| AC-7719 | Aluminum Chassis Kit – 2U Rack | 8 | $89.95 | 2024-03-14 |
| GL-5503 | Heavy-Duty Shipping Crate (Custom) | 3 | $217.00 | 2024-03-15 |
| JP-8843 | Sealed Gearbox Housing (Stainless) | 5 | $329.40 | 2024-03-16 |
| AC-7720 | Front Panel Mounting Bracket Set | 24 | $12.80 | 2024-03-17 |
| GL-5504 | Temperature-Controlled Pallet Wrap | 15 | $44.25 | 2024-03-18 |
| JP-8844 | Hydraulic Coupling Adapter (ISO 4400) | 2 | $187.60 | 2024-03-19 |
| AC-7721 | Rack Rail Extension Kit (200mm) | 10 | $36.50 | 2024-03-20 |
The Challenge
You open the Jiangsu invoice PDF. It looks clean—but it’s a scanned image. No selectable text. OCR hasn’t run. You try Ctrl+C → Ctrl+V into Excel. You get one giant blob in cell A1: INVOICE# JP-2024-03-12 JIANGSU PRECISION TOOLS CO., LTD... [17 lines of mashed text]. Even if OCR worked, Excel would paste it as plain text—not structured rows and columns. The core issue isn’t formatting. It’s that Excel doesn’t have a PDF parser engine. It reads .xlsx, .csv, .txt, .xml, and .xls. That’s it. No exceptions. No hidden ribbon tab called “PDF Import.”
What makes this especially tricky? Three things: (1) Scanned vs. text-based PDFs behave completely differently in extraction tools; (2) Column alignment in invoices rarely matches Excel’s auto-detect logic—especially when units or decimals shift position; (3) Dates like “Mar 12, 2024” or “12/03/2024” won’t auto-convert unless you force locale-aware parsing before import.
Walking Through It
We’ll use Power Query—but only after converting the PDF to something Excel *can* read. No macros. No third-party add-ins. Just built-in Microsoft tools.
Step 1: Convert PDF to Excel using Word (yes, really). Open the PDF in Microsoft Word (File → Open → select PDF). Word auto-runs OCR on scanned pages and converts layout to editable tables. Save as .docx, then copy-paste the table into Excel. Or better: File → Save As → Excel Workbook (*.xlsx). This gives you raw, unformatted cells—but now it’s Excel-native.
Step 2: Clean in Power Query. Select any cell in your pasted data → Data tab → From Table/Range (Alt+A, T). In Power Query Editor:
- Rename columns to match your target schema (right-click column header → Rename)
- Select Qty and Unit Price → Transform tab → Data Type → Decimal Number
- Select Date Shipped → Transform tab → Data Type → Date. If dates fail, use
Transform → Format → Date → Short Date, thenTransform → Data Type → Date. - Remove extra rows: Click the row number on the left (e.g., row 1), hold Shift, click row 5 → right-click → Remove Rows → Remove Top Rows
Before (raw Word-converted):
| Column1 | Column2 | Column3 | Column4 | Column5 |
|---|---|---|---|---|
| JP-8842 | CNC Bearing Assembly (Grade A) | 12 | $142.50 | Mar 12, 2024 |
| AC-7719 | Aluminum Chassis Kit – 2U Rack | 8 | $89.95 | Mar 14, 2024 |
| GL-5503 | Heavy-Duty Shipping Crate (Custom) | 3 | $217.00 | Mar 15, 2024 |
After (cleaned in Power Query):
| Item Code | Description | Qty | Unit Price | Date Shipped |
|---|---|---|---|---|
| JP-8842 | CNC Bearing Assembly (Grade A) | 12 | 142.5 | 2024-03-12 |
| AC-7719 | Aluminum Chassis Kit – 2U Rack | 8 | 89.95 | 2024-03-14 |
| GL-5503 | Heavy-Duty Shipping Crate (Custom) | 3 | 217 | 2024-03-15 |
The Result
This is what your final worksheet looks like after closing and loading the Power Query result into Excel. All fields are typed correctly. Dates are sortable. Numbers calculate. And you didn’t install a single add-in.
| Item Code | Description | Qty | Unit Price | Date Shipped |
|---|---|---|---|---|
| JP-8842 | CNC Bearing Assembly (Grade A) | 12 | 142.5 | 2024-03-12 |
| AC-7719 | Aluminum Chassis Kit – 2U Rack | 8 | 89.95 | 2024-03-14 |
| GL-5503 | Heavy-Duty Shipping Crate (Custom) | 3 | 217 | 2024-03-15 |
| JP-8843 | Sealed Gearbox Housing (Stainless) | 5 | 329.4 | 2024-03-16 |
| AC-7720 | Front Panel Mounting Bracket Set | 24 | 12.8 | 2024-03-17 |
| GL-5504 | Temperature-Controlled Pallet Wrap | 15 | 44.25 | 2024-03-18 |
| JP-8844 | Hydraulic Coupling Adapter (ISO 4400) | 2 | 187.6 | 2024-03-19 |
| AC-7721 | Rack Rail Extension Kit (200mm) | 10 | 36.5 | 2024-03-20 |
What Could Go Wrong
Mistake #1: Using Adobe Acrobat’s “Export to Excel” without verifying column breaks. Acrobat often splits multi-word descriptions across two columns if hyphens or slashes appear. You’ll end up with “CNC Bearing” in Column B and “Assembly (Grade A)” in Column C—and no way to merge them cleanly in Power Query without adding custom logic.
Mistake #2: Skipping locale settings before date conversion. If your PDF uses “12/03/2024” but your Windows region is set to US, Power Query reads it as December 3rd—not March 12th. Fix it by selecting the column → Transform tab → Locale → English (United Kingdom) → then change data type.
Mistake #3: Assuming all PDFs convert equally well in Word. Word fails silently on password-protected, encrypted, or digitally signed PDFs. You’ll get blank tables or garbled characters. Test first: open in Word, press Ctrl+A, then Ctrl+C. If nothing copies—or you get boxes instead of letters—you need an external OCR tool like Adobe Acrobat Pro or ABBYY FineReader.
Next step: Try this on one real invoice today. Use Word’s PDF import (Alt+F, O), save as .xlsx, then load into Power Query. Don’t clean manually—use the steps above. Then compare your result to the final table above. If your Unit Price column shows “$142.50” instead of “142.5”, select it → Home tab → Number group → click the Decrease Decimal button twice. That’s it.
