How to Extract Purchase Orders to Excel

A purchase order on its own is not very interesting. Its value appears when it sits next to the invoice that claims to fulfil it, in the same sheet, with the figures in the same columns — because that is the moment you can see whether you are being billed for what you ordered.

So this is less about extracting purchase orders than about getting both sides of a comparison into one place, and then letting a formula do the comparing.

What the comparison is for

Three questions, and a matched sheet answers all of them:

Am I being billed the price I agreed? The commonest overbilling is not fraud, it is a supplier's system quoting list price because the discount was agreed verbally or lives in a contract their invoicing never saw.

Am I being billed for what arrived? Short deliveries invoiced in full, and duplicate invoices against a single order, are both ordinary occurrences rather than exceptional ones.

What have I committed to that has not arrived yet? Open orders are a liability that exists nowhere in the ledger until an invoice appears. A sheet of orders with no matching invoice is that list.

The third is the one people do not expect from this exercise and often end up valuing most.

Capture both sides with the same column names

The whole technique rests on one discipline: the PO sheet and the invoice sheet use the same column headings for the same meanings. If one says "Supplier" and the other "Vendor", or one holds gross and the other net, every comparison afterwards is a manual reconciliation rather than a formula.

Purchase order columns:

Source file
PO number
Supplier
Order date
Currency
Net ordered
Tax ordered
Gross ordered
Requisitioner
Expected delivery date

Invoice columns, for the same batch:

Source file
Invoice number
PO number
Supplier
Invoice date
Currency
Net invoiced
Tax invoiced
Gross invoiced

PO number appears in both, and it is the join. Source file appears in both, and it is what makes any disagreement resolvable — when a formula says a row is short by forty pounds, the next thing anyone wants is both documents.

Amounts are split into net, tax and gross on both sides, for two reasons. It gives you the net-plus-tax-equals-gross check on every row, and it means a difference caused purely by tax treatment does not look like a price dispute.

Extracting specific fields from PDFs covers how to name a field so it has exactly one answer, which matters particularly here because "date" and "amount" are both ambiguous on a purchase order.

The PO number is the hard part

The join key is where this process actually fails, and it is worth anticipating.

Suppliers print your PO number in unpredictable places — in a "your reference" field, in the line description, in the email body and not on the document at all. Capture it as its own column and expect it to be blank sometimes. A blank is better than a guess; the blanks are a worklist.

Formats drift. PO-004471, 4471, PO 4471 and po4471 are one order. Normalise on the way into the sheet — strip the prefix, strip spaces, keep the digits as text so leading zeros survive — and keep the value as printed in a second column so you can still find it on the document.

One invoice can cover several orders, and one order can attract several invoices. Both are normal. The consequence is that you are comparing totals per PO number rather than row against row, which is a SUMIF rather than a VLOOKUP. Build it that way from the start and partial deliveries stop being a special case.

Never join on supplier name. "Northgate Freight", "Northgate Freight Ltd" and "NORTHGATE FREIGHT LIMITED" are three strings. Use the PO number, and where you must match on supplier, use a code from your own system.

What a matched sheet looks like

Sum the invoiced amounts per PO number, put them beside the ordered amounts, and subtract:

Four rows, four different things, and none of them required anyone to open a document.

The second is a part delivery or a part invoice — normal, and worth knowing about because it is a commitment still outstanding. The third is the row the exercise exists to find: more invoiced than ordered, which is either a price increase nobody agreed or a quantity nobody checked. The fourth has empty cells rather than zeros, because no invoice exists; zero would claim that something was invoiced at nothing, and would be summed as if it had been.

That distinction between blank and zero is the one to be strict about. A zero in the invoiced column is a statement that an invoice arrived for nil.

Set a tolerance, and write it down

Not every difference is worth a person's time. Rounding, freight, a small currency difference and per-line tax rounding all produce differences of a few units on a perfectly correct invoice.

So define a tolerance explicitly — an absolute amount, a percentage, or whichever is larger — put it in a cell of its own rather than inside the formula, and let the status column classify everything against it. Three outcomes is enough: within tolerance, query, open.

Keeping the tolerance in a cell matters more than it sounds. It gets argued about, it gets changed, and a number buried in fifty copies of a formula cannot be changed honestly.

Checks to run before anyone is emailed

1. Every invoice has a PO number, or is on the exceptions list deliberately. Invoices with no order are either unauthorised spend or an order placed by phone, and both are worth knowing.
2. Every PO number on an invoice exists in the PO sheet. A reference to an order you have no record of is a different and more interesting problem.
3. Net plus tax equals gross on both sheets independently.
4. Duplicate invoices. Supplier plus invoice number should be unique. This is the check that finds actual money.
5. Currency agreement. An order in one currency invoiced in another needs the rate recorded in its own column before any difference means anything.
6. Open orders by age. Sort the open rows by order date. Anything very old is either a cancelled order nobody closed or a delivery nobody chased.

Where the documents come from

Purchase orders you issued are usually available from your own system as a PDF or, better, as a CSV or XLSX export. Check for the export first — it is cleaner than anything extracted from a document and it takes five minutes to look.

Where you are working from PDFs, it is the ordinary batch extraction problem: one folder, one column set, one row per document, exactly as converting multiple PDFs into one spreadsheet describes. Two preliminaries are worth the time:

• Extract Text from PDF tells you, in the browser, whether the documents have a text layer. Orders printed and rescanned by a supplier do not, and they need the scanned route.
• PDF Page Counter counts pages across the folder, which is a quick way to find the multi-page order whose second page has the delivery schedule on it.

For the invoice side, extracting invoice data to Excel covers the field choices, and invoice to Excel walks through the workflow.

When you need the line detail

Everything above matches at document level: total ordered against total invoiced. That catches price and quantity problems in aggregate and it is the right place to start, because it is one formula and it needs nothing per line.

Matching line by line — this item, this quantity, this unit price — catches the compensating error that document-level matching cannot see, where one line is overcharged and another undercharged by the same amount. It needs many rows per document and a reconciliation step of its own; extracting invoice line items sets out the layouts.

Start at document level. Go to line level for the suppliers where document-level matching keeps producing queries.

Common questions

What is three-way matching? Purchase order, goods received note and invoice, all agreeing. This article is the two-way version — order against invoice — which is most of the value for much less work. Adding the GRN is a third sheet with the same join key and the same method.

What if a supplier does not quote my PO number? Capture the blank, and fix it upstream: most suppliers will print it if asked, and the ones who will not need a manual route you have decided on rather than one that happens by accident.

Should over-invoicing and under-invoicing be treated the same? No. Over-invoicing is a query to raise now. Under-invoicing is usually a part delivery and belongs on the open commitments list.

Can I do this in CSV rather than Excel? You can extract to CSV, and the matching wants a spreadsheet because the formulas are the method. PO numbers with leading zeros are also exactly what CSV loses on import — PDF to CSV vs PDF to Excel covers why.

How often should this run? Whenever invoices are processed, not at period end. A query raised in the week the invoice arrives is a conversation; the same query three months later is an argument.

The short version

Use the same column names on both sheets, join on a normalised PO number rather than a supplier name, sum per PO so part deliveries are ordinary, keep blanks blank, and put the tolerance in a cell.

Then the comparison is one formula, and the rows it flags are the only ones anybody has to read. Consolidating invoices into Excel is the month-end process this slots into.