Invoice headers vs line items: build the right spreadsheet

An invoice can be perfectly readable and still produce the wrong spreadsheet. If your task is to understand purchases by item, a row containing only supplier, invoice number and total does not contain the detail you need.

Start by deciding what one spreadsheet row represents. For purchase analysis, it usually represents one invoiced item or service. That decision controls the columns, the checks and the way document-level totals are handled.

Separate document data from line data

Invoice number, supplier and document currency describe the invoice as a whole. Description, reference, quantity, unit and line amount describe a particular item. Keep both levels, but do not pretend they are interchangeable.

In the synthetic example below, the same invoice number is repeated deliberately. If someone sorts the table by description or copies a subset into another workbook, each line still carries its origin.

All figures are invented. The labels have a 10% line discount: 54.00 minus 5.40 gives 48.60. The delivery quantity is not printed in this scenario, so it remains empty. Filling it with 1 would turn an assumption into apparently extracted data.

A useful export has more than numeric columns

Include a stable source-document identifier and PDF page beside the commercial fields. Preserve product references as text, particularly when they have leading zeros. Keep the original unit rather than turning a carton into a piece without knowing its contents.

A practical review column can distinguish “printed value”, “missing” and “needs review”. It should explain the problem instead of replacing uncertainty with a mysterious confidence percentage. Do not label an invoice compliant merely because its fields fit a table.

Compare the correct totals

The sum of line net amounts is not automatically the invoice’s amount payable. Invoice-level allowances, charges, tax, prepayments and rounding can sit at a different level. Peppol’s vocabulary distinguishes these concepts; it is a useful reference for naming columns, not a certificate for a PDF extraction.

Keep document totals outside the line sum, or in a separate summary. Repeating the total invoice amount on every item and summing that column later creates a predictable overcount. A matching sum is one check, not proof that descriptions, references or units are correct.

Review before using the table

Check the first line, the last line and every page boundary. Then inspect missing quantities, negative amounts and discounts. Verify that a repeated header or subtotal did not become a purchase row. Keep credits distinguishable from new purchases.

If the supplier already provides a structured export with the necessary detail, use it. There is no value in recreating a table through OCR when a better source is available.

Explore the synthetic invoice example to see line notes and source columns. InvoiceLineLab is in development; it does not process your invoices yet. For the next practical problem, read how to handle wrapped descriptions.