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:
- Delivery: What quantity or value remains to arrive?
- Invoicing: Do recorded invoices cover what has been received?
- Payment: What invoiced balance remains unpaid, and is it overdue?
- 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:
- Extract the identifiers, dates, quantities and amounts needed by the relevant table.
- Standardize PO Number and Line ID before constructing the PO Line Key.
- Check for blank keys and duplicate source records.
- Append approved rows to Tracker, Receipts or Invoices.
- 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:
- Add approved orders. Create each PO or PO-line row before the expected delivery date becomes relevant.
- Post receipts. Update Received Qty and Last Receipt Date, or append each event to the Receipts table.
- 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.
- Refresh calculations. Let the Table formulas recalculate remaining quantities, balances and statuses.
- Assign exceptions. Give every unresolved issue a Next Action, Action Owner and Follow-up Date.
- Review due work. Work through the four filtered views, recording evidence and revised dates as facts change.
- 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.