Purchase Order Tracking in Excel: Build an Open PO Tracker

A purchase order tracker in Excel should record ordered, received, invoiced and paid amounts as separate facts. It also needs separate delivery and invoice statuses, followed by a next action, owner and follow-up date. That structure shows what is outstanding and who must deal with it.

This is the foundation of useful purchase order tracking in Excel. A single status such as "Open" or "Complete" hides too much. An order can be fully received but not yet invoiced. An invoice can be due for payment while part of the order is still on back order. A cancelled remainder can leave no more goods expected even though the original ordered quantity was never received.

For open purchase order tracking, treat the workbook as an open-order control register. Each row should answer four different questions:

1. Delivery: What quantity or value remains to arrive?
2. Invoicing: Do recorded invoices cover what has been received?
3. Payment: What invoiced balance remains unpaid, and is it overdue?
4. Follow-up: What should happen next, who owns it and when will they follow up?

The monetary fields must preserve the same distinctions. PO Line Value is an approved commitment. Invoiced Amount is a recorded supplier liability. Unpaid Invoice Balance is the invoiced amount that has not been paid. The full value of an open PO is not automatically an overdue invoice balance because some or all of it may not yet have been invoiced.

This workbook starts after approval and focuses on monitoring open commitments. If you need the wider purchase order process from approval to close-out, keep that process guidance separate from the day-to-day register.

Choose one row per order or one row per order line

One row per PO works when an order is genuinely handled as one unit: everything is expected together, received together and invoiced together. It keeps the sheet compact, but the row stops being dependable as soon as different items follow different paths.

Use one row per order line when any of these conditions apply:

• A supplier makes partial deliveries or leaves items on back order.
• Expected dates differ by item.
• One invoice covers only some lines.
• Quantities, prices or discrepancies must be checked at line level.
• A buyer may cancel an unreceived remainder without closing the whole PO.

Line-level tracking repeats PO Number, Supplier, Buyer and Order Date across several rows. That repetition is useful. Every line remains understandable when filtered, exported or assigned for follow-up, without depending on adjacent rows for context.

Give each row a PO Line Key that cannot identify two different lines. If the PO number is PO-1048 and the line identifier is 003, the key might be PO-1048|003. Store it as a value, or calculate it with this Excel Table formula:

=[@[PO Number]]&"|"&TEXT([@[Line ID]],"000")

Do not use the item description as the key. Descriptions change, and the same item can appear twice on one order. The PO number alone is also insufficient once an order has more than one line.

A line-level register does not need to become a full matching engine. If one line has several receipt records or invoices that must be allocated by FIFO, pro rata or another rule, keep those events in separate tables keyed to the PO Line Key. The detailed mechanics belong in the guide to reconcile partial deliveries across multiple goods receipts.

Copy this column structure into an Excel Table

Paste this tab-separated header row into cell A1 of a blank sheet, select the populated range, create an Excel Table and name it Tracker:

PO Line Key	PO Number	Line ID	Supplier	Buyer	Order Date	Expected Delivery	Item	Ordered Qty	Unit Price	PO Line Value	Received Qty	Remaining Qty	Last Receipt Date	Delivery Status	Invoiced Amount	Invoice Number	Invoice Date	Due Date	Paid Amount	Unpaid Invoice Balance	Invoice Coverage Confirmed	Invoice Status	Next Action	Action Owner	Follow-up Date	Notes

The following dictionary explains the working purchase order tracking template. The sequence keeps identifying information on the left, calculated control fields in the middle and follow-up ownership on the right.

Group	Column	What it records	Entry type
Identity and ownership	PO Line Key	Unique link between the PO line and later receipt or invoice records	Formula or controlled input
Identity and ownership	PO Number	Supplier-facing order reference	Input
Identity and ownership	Line ID	Stable line number within the PO	Input
Identity and ownership	Supplier	Consistent supplier name	Input
Identity and ownership	Buyer	Person responsible for the order	Input
Identity and ownership	Order Date	Date the order was placed	Input
Identity and ownership	Expected Delivery	Current committed delivery date	Input
Ordered commitment	Item	Product, service or line description	Input
Ordered commitment	Ordered Qty	Quantity authorized on the PO line	Input
Ordered commitment	Unit Price	Agreed price per unit	Input
Ordered commitment	PO Line Value	Ordered Qty multiplied by Unit Price	Formula
Receipt progress	Received Qty	Quantity received against this line	Input or event-table total
Receipt progress	Remaining Qty	Ordered Qty less Received Qty	Formula
Receipt progress	Last Receipt Date	Most recent receipt date for the line	Input or event-table result
Receipt progress	Delivery Status	Date missing, Not received, Late, Part received or Received	Formula
Invoice and payment	Invoiced Amount	Supplier invoice value recorded against the line	Input or event-table total
Invoice and payment	Invoice Number	Supplier invoice reference	Input
Invoice and payment	Invoice Date	Date on the supplier invoice	Input
Invoice and payment	Due Date	Payment due date for that invoice	Input
Invoice and payment	Paid Amount	Amount paid against the invoice	Input or event-table total
Invoice and payment	Unpaid Invoice Balance	Invoiced Amount less Paid Amount	Formula
Invoice and payment	Invoice Coverage Confirmed	Yes after the recorded invoices have been checked against receipts to date	Controlled input
Invoice and payment	Invoice Status	Not invoiced, Unpaid, Overdue or Paid	Formula
Follow-up control	Next Action	Specific step needed to clear the exception	Controlled input
Follow-up control	Action Owner	Person responsible for that step	Controlled input
Follow-up control	Follow-up Date	Date the action should be checked again	Input
Follow-up control	Notes	Evidence or context the owner needs	Input

PO Line Value is the approved commitment. It does not become a liability merely because the PO is open. Invoiced Amount records the supplier liability entered against the line, while Unpaid Invoice Balance shows the portion of that liability that has not been paid.

Table rows carry formulas and validation into newly added records, while stable column names make the logic readable. Freeze the columns through Item so the PO, supplier and line remain visible when you scroll across the status and action fields.

Protect the formula columns after testing them: PO Line Key, PO Line Value, Remaining Qty, Delivery Status, Unpaid Invoice Balance and Invoice Status. Leave source facts and follow-up fields editable. This prevents a status from being overwritten to make the row appear complete while its quantities or balances still say otherwise.

Calculate values first, then derive delivery and invoice status

A PO status tracking spreadsheet should calculate status from source facts instead of asking users to keep facts and labels in sync. Enter each formula in the first data row of the Tracker table. Excel will fill it down the column and apply it to new rows.

PO Line Value calculates the approved commitment:

=ROUND([@[Ordered Qty]]*[@[Unit Price]],2)

Remaining Qty measures what has not been received:

=MAX(0,[@[Ordered Qty]]-[@[Received Qty]])

Unpaid Invoice Balance measures the recorded liability that has not been paid:

=MAX(0,[@[Invoiced Amount]]-[@[Paid Amount]])

MAX prevents an over-receipt or overpayment from displaying as a negative open balance. It does not make the excess disappear. Add a separate exception check or investigate any row where Received Qty exceeds Ordered Qty or Paid Amount exceeds Invoiced Amount.

Calculate Delivery Status from receipt facts and the expected date:

=IF([@[Remaining Qty]]=0,"Received",IF([@[Expected Delivery]]="","Date missing",IF([@[Expected Delivery]]<TODAY(),"Late",IF([@[Received Qty]]>0,"Part received","Not received"))))

This order of tests matters. A fully received line is Received. An outstanding line with no expected date is Date missing rather than falsely Late. Any outstanding quantity after the expected date is Late, including a partial delivery with an overdue remainder. Before that date, a line with some quantity received is Part received; otherwise it is Not received.

Calculate Invoice Status from the invoiced amount, unpaid balance and invoice due date:

=IF([@[Invoiced Amount]]=0,"Not invoiced",IF([@[Unpaid Invoice Balance]]=0,"Paid",IF([@[Due Date]]<TODAY(),"Overdue","Unpaid")))

Once Invoiced Amount is greater than zero, Due Date must be populated for this formula to be dependable. Overdue means an invoice has an unpaid balance after its due date. It never means that the uninvoiced portion of the PO is overdue for payment.

Protect these formula columns after testing them. Use dropdowns for human decisions such as Next Action, but do not let users type over a delivery or invoice status that the underlying numbers can establish.

Microsoft's guidance on structured references confirms that Excel table references adjust when rows or columns are added or removed. The named references also make a formula easier to audit than cell coordinates such as G2 or N47.

When one line has several receipts or invoices

Keep repeated transactions in separate Excel Tables instead of adding Receipt 2, Receipt 3 or Invoice 2 columns. A Receipts table can contain PO Line Key, Receipt Date and Received Qty. An Invoices table can contain PO Line Key, Invoice Number, Invoice Date, Due Date, Invoice Amount and Paid Amount.

The Tracker table can roll all receipt rows into Received Qty with:

=SUMIFS(Receipts[Received Qty],Receipts[PO Line Key],[@[PO Line Key]])

The same multi-criteria function can total invoice or payment records by PO Line Key. Microsoft's SUMIFS documentation describes the required sum range and criteria pairs. When one PO line has invoices with different due dates, review overdue exposure in the Invoices table rather than collapsing those dates into a single Tracker value.

Turn the register into four follow-up views

A tracker becomes useful when it reduces hundreds of rows to the exceptions that need attention. Save these four filters as separate sheets, Table views or recurring filter combinations.

View	Filter conditions	What the owner does
Late deliveries	Delivery Status equals Late	Confirm the outstanding quantity, check the last supplier commitment and obtain a revised delivery date.
Received, invoice missing or incomplete	Received Qty is greater than zero and either Invoiced Amount equals zero or Invoice Coverage Confirmed is not Yes	Verify that receipts use the correct PO Line Key, then obtain missing invoice records or resolve partial coverage.
Unpaid invoices	Unpaid Invoice Balance is greater than zero; sort Due Date oldest first and prioritize Invoice Status equals Overdue	Confirm approval or payment state using Invoice Number, Invoice Date, Due Date and Paid Amount.
Actions due	Next Action is not blank and Follow-up Date is today or earlier	Contact the named Action Owner, record the result and set the next follow-up date if the issue remains open.

The three follow-up fields work as a unit. Next Action describes one observable step, such as "Supplier to confirm revised delivery date." Action Owner names the person responsible. Follow-up Date says when the register owner will check again. "Waiting for supplier" in Notes does not create accountable work.

Consider one PO line for 100 units at $10 each. The expected delivery date has passed, 60 units have been received, the supplier has invoiced $600 for those units and nothing has yet been paid. The invoice is due in five days.

Fact or control	Value
PO Line Value	$1,000
Remaining Qty	40
Delivery Status	Late
Invoiced Amount	$600
Unpaid Invoice Balance	$600
Invoice Status	Unpaid
Next Action	Supplier to confirm delivery date for remaining 40 units
Action Owner	Buyer
Follow-up Date	Tomorrow

After AP checks that the $600 invoice covers the 60 units received to date, Invoice Coverage Confirmed can be set to Yes. The line belongs in the Late deliveries view and the Unpaid invoices view for different reasons, but not in the incomplete-invoice view. The delivery issue belongs to the buyer even though AP still needs to monitor the invoice due date.

Conditional formatting can make these queues easier to scan. Highlight Late and Overdue statuses, and flag Follow-up Dates that are due. Apply the formatting to calculated results and dates; do not encode the status logic only through fill colors, because filters and formulas cannot reliably act on a visual cue.

---

Load purchase orders, receipts and invoices without rekeying every field

The tracker needs facts from three document streams: what was ordered, what arrived and what the supplier invoiced. Whether those facts are entered manually or imported, they must use the same row grain, PO Line Key, column names and date formats.

For a new PO, capture PO Number, Line ID, Supplier, Buyer, Order Date, Expected Delivery, Item, Ordered Qty and Unit Price. A receipt adds PO Line Key, Receipt Date and Received Qty. A supplier invoice adds PO Line Key, Invoice Number, Invoice Date, Due Date, Invoice Amount and any payment information recorded later. Do not reproduce the visual layout of each document in the workbook. Map each source field to the register concept it represents.

When the source records are PDFs or images, Invoice Data Extraction can convert purchase orders, receipts and supplier invoices into Excel, CSV or JSON data, including one row per line item. If you need to track purchase orders and invoices without rekeying each document, you can extract purchase order and invoice data into Excel with column names and row structure specified for the register, then map the resulting rows by PO Line Key. The extraction step supplies structured source facts; it does not decide whether an order is late, maintain the next action or execute payment.

A practical import sequence is:

1. Extract the identifiers, dates, quantities and amounts needed by the relevant table.
2. Standardize PO Number and Line ID before constructing the PO Line Key.
3. Check for blank keys and duplicate source records.
4. Append approved rows to Tracker, Receipts or Invoices.
5. Refresh formulas and review any new exceptions.

Keep the source filename and page reference with imported rows where available. That gives the buyer or AP reviewer a route back to the document when a quantity, date or amount needs checking.

If you are choosing among capture methods rather than loading this workbook, use the separate guide to evaluate purchase order data extraction software. Tool selection is a different task from operating the register after the data arrives.

Run the tracker as a weekly control routine

Assign one person to maintain the register and use the same update sequence each time:

1. Add approved orders. Create each PO or PO-line row before the expected delivery date becomes relevant.
2. Post receipts. Update Received Qty and Last Receipt Date, or append each event to the Receipts table.
3. Record invoices and payments. Enter the invoice reference, dates and amounts against the correct PO Line Key. Set Invoice Coverage Confirmed after checking invoices against receipts to date, and reset it when another receipt arrives.
4. Refresh calculations. Let the Table formulas recalculate remaining quantities, balances and statuses.
5. Assign exceptions. Give every unresolved issue a Next Action, Action Owner and Follow-up Date.
6. Review due work. Work through the four filtered views, recording evidence and revised dates as facts change.
7. Close or archive resolved lines. Move completed records out of the active review without deleting their history.

A PO line is ready to close only when delivery is complete, invoice work is resolved, Unpaid Invoice Balance is zero and no follow-up action remains. If the unreceived remainder was cancelled, record that decision in Notes and adjust the ordered commitment through the approved process before closure. Do not close a line merely because Delivery Status says Received or Invoice Status says Paid.

Use data validation for fields people control, such as Action Owner and agreed Next Action categories. Keep supplier names on a controlled list so "ABC Supplies" and "ABC Supplies Ltd" do not split the same supplier across reports. Protect formula columns, and flag a repeated PO Line Key with a duplicate check such as:

=COUNTIF(Tracker[PO Line Key],[@[PO Line Key]])>1

A weekly open-order review is a practical default for small teams. Businesses with daily deliveries or tight payment cycles may review more frequently, but Follow-up Date should keep urgent work visible between scheduled reviews.

Archive completed rows in a separate Table or period sheet rather than deleting them. Retaining the PO Line Key, source references, status facts and notes preserves the trail needed when a supplier questions a receipt, invoice or payment months later.