Convert PDF Invoice to Excel: Header Fields and Line Items

Quick Answer: To convert a PDF invoice to Excel, first decide whether you need the header fields (vendor, invoice number, date, total) or every line item. For text-based invoices from one vendor, Excel's own PDF import or Adobe Acrobat's export gets the line table out for free. For many vendors' layouts, an invoice-capture tool that reads fields by meaning is the better fit, feeding a one-row-per-invoice log.

An invoice PDF holds two kinds of data: a handful of header fields printed once, and a line-item table that repeats. Excel's Get Data from PDF and Acrobat's Excel export both read tables well, so they handle line items from text-based invoices. Header fields scattered around the page usually need a formula step, manual entry or a dedicated invoice-capture tool.

What exactly do you need out of the invoice?

Most people who search for this want one of two outputs, and the right tool depends on which.

Header data is small but awkward: the invoice number might sit top right on one vendor's layout and under the logo on another's, and "Total" might be labeled "Amount due," "Balance" or "Please pay." Line items are a real table, which is what PDF extractors are built for, but they break in their own ways (more on that below).

If you only need ten invoices a month logged, typing the seven header fields is honestly faster than any tool. Automation starts to pay when the volume or the line count grows.

How do you pull an invoice into Excel for free?

Two free routes work well for invoices that were generated by software (you can select the text in your PDF reader), rather than scanned on paper.

Excel's Get Data from PDF

Microsoft documents this under Import data from data sources (Power Query). The path is Data > Get Data > From File > From PDF. Choose the file, and the Navigator lists every table and page Power Query found. Pick the one holding the line items, then choose Load to drop it straight on a sheet or Transform Data to clean it first. Microsoft notes the PDF connector needs .NET Framework 4.5 or later on the PC.

What makes this method good for invoices in particular:

• Repeatable. Save the workbook, and next month point the query at the new invoice from the same vendor. The cleanup steps you recorded (removing the header rows, renaming columns, changing the price column to a number) run again.
• Page ranges. The Power Query PDF connector reference lists StartPage and EndPage options on the underlying Pdf.Tables function, which helps when terms and conditions fill the last pages.
• Multi-page line tables. The same reference says similar tables on consecutive pages are combined by default (the MultiPageTables option), so a three-page parts list arrives as one table.

The header fields sometimes come through as a small "table" of their own, and sometimes only in the Page item in Navigator, as loose text. In the second case, load the page and use a formula to grab the value next to its label (see the log section below).

Adobe Acrobat's export

Adobe's PDF to Excel how-to gives the desktop steps: open the PDF in Acrobat, pick the Export PDF tool, choose Spreadsheet and then Microsoft Excel Workbook, and click Export. Adobe says Acrobat runs text recognition automatically when the PDF contains scanned text, which makes it the more forgiving free-trial option for a scanned paper invoice. Adobe also offers a free online PDF to Excel converter, where you select a file and download the XLSX.

Acrobat converts the whole page layout, so expect the logo area, the address blocks and the totals box to land in cells around the line table. That is fine for a one-off; it is tedious to clean on a hundred invoices. Our Acrobat PDF to Excel walkthrough covers the export settings in more depth.

Why do invoice layouts break table extraction?

Invoices are designed for a person reading them, not for a parser, and a few habits cause most of the mess:

1. Wrapped descriptions. A long item description spills onto a second line, and the extractor treats it as a new row with empty quantity and price cells. Fix it in Power Query by filling the invoice line key down, or by joining the orphan text to the row above.
2. Merged label cells. "Subtotal," "Tax" and "Total" are often printed in the description column under the last line, so they get read as line items. Filter out rows whose quantity is blank and whose description matches those labels.
3. Numbers as text. Currency symbols, thousands separators and trailing minus signs ("45.00-") arrive as text. Convert the column type in Power Query, or use VALUE after SUBSTITUTE in the sheet.
4. Different layouts per vendor. A query built for one vendor's invoice will not find the right table on another vendor's. You end up with one query per vendor, which is manageable for five vendors and not for fifty.
5. Scans and photos. A scanned invoice has no text layer. Excel's PDF import cannot read an image-only page; you need OCR first (Acrobat, or a capture tool).

A quick test before you build anything: open the PDF, press Ctrl+F and search for the invoice number. If the reader finds it, the PDF has a text layer and the free methods will work.

When is an invoice-capture tool worth it?

Invoice-capture (sometimes called invoice OCR or AP automation) software is built to find header fields by meaning rather than by position: it recognizes that "Inv #," "Invoice No." and "Reference" are the same field across vendors. Many accounting systems and bill-pay services include some capture of their own, where you forward a bill by email and the fields are filled in for review.

That is the better choice when:

• you receive invoices from dozens of vendors with different layouts,
• you need header fields more than line tables,
• invoices arrive as scans or phone photos, or
• someone has to approve each bill before it is paid.

Compare tools on how they handle line items (some only capture header totals), whether they export to Excel or CSV or only push to an accounting system, and how corrections are reviewed. Run a sample of your own invoices through a trial before paying; vendors' accuracy claims are measured on their test sets, not your suppliers.

A note on our own product: QuickBankConvert is built for bank and credit card statements, not invoices. It reads statement layouts (dates, descriptions, signed amounts and running balances) and is the wrong tool for pulling invoice numbers and line items. Where it does fit is the other side of the payables process, described next.

Building a one-row-per-invoice log

Whatever extraction method you choose, land the results in one table with one row per invoice. A practical column set:

Two formulas earn their place. A duplicate check, =COUNTIFS($A:$A,A2,$B:$B,B2)>1, flags the same vendor and invoice number entered twice, which is the classic way bills get paid twice. And when header fields landed as loose page text, a lookup next to the label pulls them out: if the Power Query page output has labels in column A and values in column B, =XLOOKUP("Invoice Number*",A:A,B:B,"",2) returns the value beside the first label that starts with "Invoice Number" (the 2 turns on wildcard matching).

If you need line items too, keep them in a second table with Invoice No. as the first column, so a PivotTable or SUMIFS can roll lines up to the invoice and confirm they add to its subtotal.

For a fuller tracker with aging buckets and a due-this-week view, see the accounts payable tracking spreadsheet guide.

Closing the loop with the bank statement

An invoice log is only trustworthy if the Paid Date column reflects money that actually left the account. The cleanest way to fill it is from the bank statement: sort the statement's outgoing payments by amount and match each to an open invoice total for that vendor.

If your bank only gives you PDF statements, that is where QuickBankConvert helps: it turns the statement into rows of date, description and signed amount, and a free account downloads them as CSV or Excel. Then an XLOOKUP on amount and vendor name fills in the paid dates, and anything left unmatched is either still open or paid in a way you have not recorded.

For pulling fields out of other kinds of PDF, our guide on extracting data from PDF to Excel covers formulas for semi-structured documents, and extracting data from multiple PDF files shows how to point one Power Query at a whole folder of invoices from the same vendor.

Related guides

• Accounts payable tracking spreadsheet
• Extract data from multiple PDF files to Excel
• Adobe Acrobat PDF to Excel
• Extract data from PDF to Excel