How to Extract Data From PDF Invoices Into Excel
There are four main ways to get data out of PDF invoices into Excel: copy and paste (fine for a few invoices), Excel's Power Query PDF connector (good for PDFs that contain real text tables), OCR (needed for scanned or photographed invoices) and batch extraction tools that read the key fields (date, supplier, invoice number, tax, total) from many invoices at once. Which one fits depends on how many invoices you have, whether they're digital or scanned, and whether you need line items or just header totals.
Below is how each method works, where it breaks down, and how to check the result.
First: what kind of PDF do you have?
This decides almost everything:
- Digital (text) PDFs are generated by accounting software, online stores and billing systems. You can select and copy the text. The data is already there. It just needs to be put into columns.
- Scanned or photographed PDFs are images saved as a PDF. If you can't select individual words, there's no text layer, and you need OCR (optical character recognition) to read it.
Quick test: open the PDF and try to highlight the total with your cursor. If you can, it's a text PDF.
Also decide what you need:
- Header fields (one row per invoice): invoice date, supplier, invoice number, subtotal, tax, total. This is usually enough for bookkeeping, expense tracking and tax.
- Line items (one row per product or service): needed for inventory, job costing or detailed analysis. It's much harder to extract reliably.
Method 1: Copy and paste
For a handful of invoices, manual entry is honestly the fastest.
- Open the invoice PDF.
- Select the fields you need and copy them.
- Paste into your spreadsheet. In Excel, Paste Special > Text (or Keep Text Only) avoids carrying over formatting.
- Fix any values that pasted into one cell, using Data > Text to Columns where needed.
Good for: fewer than about ten invoices, and one-off jobs. Breaks down: at volume. Typos creep in, and it's slow.
Method 2: Excel's Power Query PDF connector
Excel includes a PDF connector in Power Query (the Get Data feature). According to Microsoft's documentation, it uses a function called Pdf.Tables, which returns any tables found in a PDF file, and to import multiple PDF files at once you use a multi-file connector such as the Folder connector.
General steps (menu names vary by Excel version and platform):
- In Excel, open Get Data and choose the PDF option. On recent Microsoft 365 versions for Windows, it's under Data > Get Data > From File.
- Browse to the PDF and select Open.
- In the Navigator, pick the table or page that holds the data you want.
- Select Load to bring it into a sheet, or Transform Data to clean it up in the Power Query Editor first (remove rows, split columns, set data types).
- For many invoices, use the Folder connector to combine every PDF in a folder, then apply the same transformation to all of them.
Good for: digital PDFs from the same supplier or system, where the layout is consistent and the data sits in a real table, such as a line-item table. Breaks down:
- Scanned PDFs: there's no text to detect, so there are no tables.
- Mixed suppliers: every layout is different, so one transformation won't fit all of them.
- Header fields such as invoice number and date often aren't in a table at all, so they're harder to pick out.
- The PDF connector isn't available in every Excel edition or platform, so check yours.
Microsoft's documentation also suggests ways to deal with large PDFs, such as loading a page range at a time.
Method 3: OCR for scanned invoices
If your invoices are scans or photos, the text has to be recognized first. Options include:
- OCR built into PDF software, which adds a text layer to the scan. After that you can copy and paste, or sometimes use Power Query.
- Phone scanner apps that make searchable PDFs. For example, Google Drive's Android scanner describes saving scans as searchable PDFs.
- Cloud document-processing services offered by the major cloud providers, aimed at developers.
Good for: turning image-only PDFs into usable text. Breaks down: OCR gives you text, not a spreadsheet. You still have to find the date, total and tax in that text and put them into columns. Accuracy also drops with faded, crumpled or low-resolution scans, so check the numbers.
Method 4: Batch extraction tools
Dedicated invoice and receipt extraction tools combine reading and structuring. You upload many invoices, digital or scanned, and get a table with one row per invoice and named columns.
ReceiptBatch works this way. You drop in a batch of PDFs or images, and it returns date, vendor, subtotal, tax, tip, total, currency and a suggested category for each document, in a table you can edit before you download Excel or CSV. Its batch PDF invoice to Excel converter page explains the details. Note that it reads header totals, not line items, and it processes up to 10 pages per PDF.
Good for: many invoices from many different suppliers, a mix of scans and digital PDFs, and bookkeeping where header totals are what you need. Breaks down: if you need every line item, or if you can't upload documents to an online service for confidentiality reasons. Check the tool's privacy policy first.
Comparison
| Method | Volume | Scanned PDFs | Mixed layouts | Line items | Effort |
|---|---|---|---|---|---|
| Copy and paste | Low | Only if text is selectable | Yes | Yes, slowly | High per invoice |
| Power Query PDF | Medium to high | No | Poor | Yes, for consistent tables | Setup once, then low |
| OCR | Any | Yes | Yes | Partially | Medium, still needs structuring |
| Batch extraction tool | High | Yes | Yes | Depends on tool | Low, plus review |
Whatever the method: check the output
Extracted data should always be reviewed before it goes into your books or a tax return:
- Count rows against files. Every invoice should appear exactly once.
- Check totals. Subtotal plus tax should equal the total. A quick formula column flags mismatches.
- Check dates. Watch for day/month swaps (03/04 could be March 4 or April 3).
- Check currency if you buy from suppliers abroad.
- Spot-check against the PDFs, especially the largest amounts.
- Keep the original PDFs. The spreadsheet is a summary, and the invoices are your evidence.
Once the data is clean, the receipt spreadsheet guide shows how to total it by category with SUMIFS or a pivot table. If you're handing it to an accountant, see how to prepare your receipts for your accountant.
FAQ
Can Excel read a PDF invoice directly?
Excel's Power Query PDF connector can import tables from PDFs that contain text. It can't read scanned images, and the connector isn't available in every Excel edition.
How do I extract the invoice number and date, not just the table?
These usually sit outside the line-item table, so table-based import often misses them. Copy them manually, use Power Query transformations for a consistent layout, or use an extraction tool that reads header fields.
What's the difference between an invoice and a receipt for this?
The extraction methods are the same. For your records, the difference is what each proves. See invoice vs receipt.
Is it safe to upload invoices to an online tool?
Invoices can contain personal and business details. Read the tool's privacy policy to see how files are processed and stored, and only use services you're comfortable with.