Invoices in, clean Excel out

What kind of invoice file are you actually holding?

Open one invoice and try to select the text with your cursor. That two second test splits your inbox into three tiers, and the tiers need completely different tools. Buying one OCR subscription for all three is the most common and most expensive mistake here.

The top tier is the hybrid e-invoice, and most people do not know they are already receiving them. A Factur-X or ZUGFeRD invoice looks like an ordinary PDF, but it is a PDF/A-3 file with a structured XML invoice attached inside it, named factur-x.xml in current versions and built on the European e-invoicing standard EN 16931. The structure is documented in PDFlib's technical reference on ZUGFeRD and Factur-X. If a vendor sends one of these, running OCR on it is like photographing a screen to read a text file. The exact numbers are already in the envelope.

The middle tier is the ordinary text based PDF that a billing system printed. Excel already ships a connector for these: Power Query has a native PDF connector that pulls tables straight out of the file, and it costs nothing because you own it already. Microsoft is honest about the limits in that same document, including that multi-line rows are often not identified properly, which is exactly what a long item description does to an invoice table.

The bottom tier is the scan or the phone photo, a picture of a page with no text layer at all. This tier genuinely needs OCR, and it is the only tier where the machine is guessing rather than reading. Keep that distinction in mind, because it decides how much checking each row deserves later.

Sort one month of real invoices into those three buckets before you spend anything. In the small business inboxes I have looked through, the scanned tier is usually the minority, and most of the pile was solvable with a tool the owner had already paid Microsoft for.

Why does the ledger have to exist before the extraction?

Decide the shape of the output table first, on paper, before any file is read. This sounds like bureaucracy, and it is the step that decides whether the whole thing survives month three. An extraction tool will happily give you one worksheet per invoice, and a hundred worksheets is a hundred problems.

One row per invoice line. Never one row per invoice. The moment you collapse an invoice into a single row you have thrown away the line detail, and line detail is the only thing that lets you check anything or answer what you actually bought. It is the same discipline that makes comparing ten vendor quotes possible: one shared spine, every document forced onto it.

1. 01
   The identity columns

   vendor_id, invoice_no, invoice_date, currency and doc_type. Document type matters more than people expect, because a credit note is not a small invoice, it is a negative one.
2. 02
   The line columns

   line_desc, qty, unit_price, line_total and tax_rate. Keep quantity and unit price separate even when the invoice only prints a line total, because those two multiplied are a free correctness check.
3. 03
   The trail columns

   source_file, page_no, method and extracted_on. These are the five seconds of work that let you jump from a suspicious number straight to the page it was read from.
4. 04
   The judgment column

   check_status, holding one of pass, flagged or fixed by hand. This is the column that turns a sheet from a claim into something a finance person can sign.

The trail columns are the part the vendor guides skip, and they are the reason a ledger survives being questioned. A number with no provenance is a rumour. When somebody queries a figure eight months later, the difference between a five second answer and an afternoon in a filing cabinet is a column holding a file name.

Which invoice fields does OCR get confidently wrong?

Extraction rarely fails loudly. It fails by returning a plausible value, which is the same failure mode I wrote about in the silent error that reached the client. A handful of fields cause almost all of it, and knowing them tells you what to check.

Dates are first and worst. A date printed 03/04/2026 is two different dates depending on which side of the Atlantic wrote it, and a machine reading a European invoice with an American assumption produces a real date in the wrong month, silently. Currency separators share the trap: 1.234,56 and 1,234.56 are the same amount written by two continents, and a careless parser turns one of them into something a hundred times too small.

Then the totals. Tax lines, shipping lines and discount lines sit in the same visual column as item lines, so they get pulled in as items and the ledger sums to more than the invoice. Credit notes arrive looking exactly like invoices apart from a word and a minus sign, and if the sign is dropped a refund is booked as a purchase. And on a two page invoice the second page usually repeats the header, so the same invoice number gets recorded twice and the same amount can be paid twice.

Every one of these produces a number rather than an error. That is the whole reason the next section exists, and it is the same lesson as cleaning messy Excel data: the values that look fine are the ones that cost money, and the dedupe itself is one of them, because which duplicate invoice row survives is not guaranteed to be the one you sorted to the top.

How do you check a thousand rows without reading a thousand invoices?

Here is the part the tool vendors leave out, and it is the reason invoice data is worth automating at all. An invoice is not ordinary data. It carries its own proof inside itself, because the lines are supposed to add up to the subtotal, and the subtotal plus tax is supposed to equal the total.

That means you never have to trust the extraction. You can test it. A few grouped formulas over the ledger will find nearly everything the previous section describes, and they cost almost nothing to run because the arithmetic is already printed on the document.

1. 01
   Lines against subtotal

   Group the ledger by invoice and sum line_total, then compare it to the subtotal the tool read. A mismatch means a line was missed, a tax line was counted as an item, or a decimal moved.
2. 02
   Subtotal plus tax against total

   This second, independent test catches what the first one cannot, particularly a misread tax rate and a total picked up from the wrong page of a multi page invoice.
3. 03
   Vendor and number seen once

   Count rows per vendor_id and invoice_no together. Repeats are either a duplicated header from page two or a genuine duplicate payment waiting to happen, and both need eyes.
4. 04
   Quantity times price against line total

   Where the invoice prints all three, multiply and compare. It is the cheapest check in the set and it catches a misread digit inside a single line, which the invoice level checks miss when two errors cancel out.

Now set a tolerance, and be honest about it. Rounding legitimately produces a difference of a minor unit or two on invoices with many lines, so a check demanding exact equality flags half the file and gets switched off within a week. A tolerance of one or two minor units passes real rounding and still catches a decimal in the wrong place.

Everything that agrees on all four tests can post without a human. Everything that disagrees goes into a short queue with its source_file and page_no beside it, and you open those pages and only those. The size of that queue, measured before and after, is the honest number to judge the build by, not the accuracy percentage on a vendor pricing page.

When is automating invoice entry not worth the build?

If a small number of invoices arrive each month and they all come from the same handful of vendors in the same format, type them. A build will not repay itself at that size, and a well made entry sheet with data validation gets you most of the way. The threshold is not really volume anyway. It is variety.

Ten vendors sending a hundred invoices in ten formats is a much harder problem than one vendor sending a thousand, because every format is its own set of rules. So when you price a build, count the distinct layouts, not the pages. And if most of your inbox turns out to be tier one or tier two, say so out loud, because that is the case where an afternoon with Power Query beats a subscription. The same logic from automating a weekly Excel report applies directly: pick the lowest tier that survives your real files.

Where it does pay is where the invoices feed something else. Once every line sits in one clean ledger, the spend analysis, the vendor comparison and the cash forecast stop being separate projects and become queries. That second order value is usually larger than the typing hours you set out to save.

If your pile is genuinely mixed and the checking matters more than the extraction, this is the work I take on for clients in my evenings. Start a project and bring three real invoices including the ugliest scan you have, and I will tell you which tier you are in, even when the answer is that you do not need me.