Purchase Order Tracker: Build One in Sheets or Excel

TL;DR: A purchase order tracker is a sheet with one row per PO line that records who you ordered from, what, when it is due, what has arrived, what was invoiced and what was paid. Build it with 14 columns, a 7-value status dropdown, two helper formulas (open value and days late) and one conditional-formatting rule that turns overdue rows red. It works well up to a few dozen open POs; past that, the manual updates are what break.

A purchase order tracker is a log of every purchase order your business has issued, kept current as each order moves from approved to issued, received, invoiced and paid. The tracker answers three questions on any given day: which orders are late, how much money is committed but not yet received, and which supplier invoices can be paid because the goods actually arrived. You can build a working purchase order tracker in Google Sheets or Excel in about 60 minutes using the steps below: set up the columns, lock the status values with a dropdown, add formulas for open value and days late, flag overdue deliveries with conditional formatting, and add a summary block. Everything here works in both tools; where Excel and Sheets differ (FILTER, error values, menu paths), the difference is called out.

What you need before you start

You need a spreadsheet tool and your current list of open orders. Specifically:

• Google Sheets (free) or Excel. Excel 2016 and 2019 can do everything except the FILTER-based "open orders" view in step 6, because Microsoft lists FILTER only for Microsoft 365, Excel 2024 and Excel 2021 (Microsoft Support).
• Your PO numbering scheme. If you don't have one yet, pick a format such as PO-2026-0147 before you start; our guide to the PO number covers formats that sort cleanly.
• Open POs from the last 90 days, from email, your accounting system or the PDFs you sent. If you still write POs by hand, the free purchase order generator gives you a numbered PDF you can log here.

What columns should a purchase order tracker have?

A purchase order tracker should have one column for each fact you check later: identity (PO number, date, supplier), the item (description, quantity, unit price), the dates (expected and received), the money (invoice number, invoiced amount, paid amount) and a status. Use one row per PO line, not per PO: if PO-2026-0147 has two items, it gets two rows with the same PO number, so a partial delivery of one item doesn't hide behind the other.

Two optional columns pay for themselves once you have more than one buyer: Requester (who asked for it) and Approver (who signed off). Amazon Business's October 2026 guide also suggests a cost center or budget code at the request stage, so anyone reviewing an order can see which budget it was charged to (Amazon Business).

How do I track purchase orders in Excel or Google Sheets, step by step?

You track purchase orders in Excel or Google Sheets by entering every PO line as a row, updating its status and received quantity each time something happens, and letting formulas compute what is open and what is late. The build takes seven steps.

1. Create the header row and freeze it. Type the 14 headers from the table above into row 1 of a sheet named PO Log. In Sheets, use View > Freeze > 1 row; in Excel, View > Freeze Panes > Freeze Top Row. In Excel, also press Ctrl+T to turn the range into a Table so formulas and formatting extend to new rows automatically.
2. Add the line value formula. In G2 enter =E2*F2 and fill it down. This is the committed value of the line; format G, F, L and M as currency.
3. Lock the status column with a dropdown. Create a second sheet named Lists and type the 7 statuses from the next section into A1:A7. In Google Sheets, select column N, then Data > Data validation > Add rule > Dropdown from a range, and point it at Lists!A1:A7 (Google Docs Editors Help). In Excel, select column N, then Data > Data Validation > Settings > Allow: List, and set Source to the same range (Microsoft Support). Leave the default "reject input" behaviour on: typos like "Recieved" are what break status counts.
4. Add two helper columns. In O1 type Open value and in O2 enter =(E2-I2)*F2, which is the value of goods ordered but not yet received. In P1 type Days late and in P2 enter =IF(AND(I2<E2, H2<TODAY()), TODAY()-H2, 0). The result is the number of days past the expected date for any line that hasn't fully arrived, and 0 otherwise: a line expected on November 10 and still short on November 14 shows 4 days. Fill both down.
5. Flag overdue rows with conditional formatting. Select A2:P500. In Sheets: Format > Conditional formatting > Format cells if > Custom formula is, then enter =$P2>0 and pick a red fill (Google Docs Editors Help). In Excel: Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, then enter the same formula. Add a second rule, =$N2="Closed", with grey text so finished lines fade out, the same trick the Vertex42 template uses for completed orders (Vertex42).
6. Build a summary block. On a third sheet named Summary, add the formulas from the "open and overdue" section below: total open value, count of late lines, open value by supplier, and (in Sheets, Excel 2021, Excel 2024 or Microsoft 365) a live list of late lines.
7. Set an update routine. Decide who updates the sheet and when: receiving updates columns I, J and N within 24 hours of goods arriving; whoever handles invoices updates K, L and M the day an invoice lands. At 20 to 40 open lines this is about 10 minutes per day. A tracker that is updated weekly is a report, not a tracker.

Which status values should a PO tracker use?

A PO tracker should use a short, fixed list of statuses that match the stages a purchase order actually passes through. Amazon Business describes the cycle as requested, approved, issued, fulfilled, invoiced and paid (Amazon Business); Smartsheet uses pending, approved, sent, received and invoiced (Smartsheet). For a small business tracking lines rather than whole POs, these 7 values cover every case:

Keep "Acknowledged" even if suppliers rarely confirm in writing. A line stuck at "Issued" for more than 2 days (48 hours) is the earliest warning you get that a delivery date is at risk, and it is the moment to chase. Our guide to order acknowledgement controls covers what to check in a confirmation.

How do you track open and overdue purchase orders?

You track open and overdue purchase orders with three summary formulas that read the PO Log: one sums the value still to be received, one counts late lines, and one lists them. Put these on the Summary sheet.

In the example below, PO 0139 was due on November 4, so on November 14 it shows 10 days late, and the not-acknowledged count picks up any line issued more than 2 days ago that is still at "Issued". For a live list of late lines, use FILTER. In Google Sheets: =FILTER('PO Log'!A2:P500, 'PO Log'!P2:P500>0). In Excel 2021, 2024 or Microsoft 365, add the third argument so an empty result shows text instead of an error: =FILTER('PO Log'!A2:P500, 'PO Log'!P2:P500>0, "Nothing late").

Three details trip people up. First, SUMIFS takes the range to add as its first argument, while the older SUMIF takes it third; Microsoft calls this "a common source of problems" (Microsoft Support). Second, every range in SUMIFS and COUNTIFS must be the same size, so keep all of them at rows 2 to 500 (Google Docs Editors Help). Third, TODAY recalculates, so the days-late column changes every day without anyone touching the sheet; Google notes it is a volatile function that can slow large sheets (Google Docs Editors Help).

How do you match invoices to POs in a tracker?

You match an invoice to a PO in a tracker by finding the PO line, checking that the invoiced quantity and price match what was ordered and what was received, and only then marking it Invoiced. That is a manual version of three-way matching: PO, receipt and invoice must agree. Add one check column, Q, with =IF(L2>I2*F2, "Over-billed", "OK"), which flags any invoice that charges for more than the received quantity at the PO price. The Amazon Business guide makes the same point: the match is where you catch a price that changed after approval or a duplicate bill before payment goes out. Logging the invoice number in column K and using COUNTIF('PO Log'!K2:K500, K2)>1 as a second conditional-formatting rule highlights a duplicate invoice the moment it is typed.

A video walkthrough helps if you want to see a finished tracker before building your own. The segment below shows a spreadsheet tracker with one row per item, payment status per line, and supplier and outstanding-payment views built from the log.

Common errors and how to fix them

These are the errors you will see in a PO tracker, with the cause and the fix.

• #N/A in the late-lines list (Google Sheets). FILTER returns #N/A when no rows meet the condition (Google Docs Editors Help). It means nothing is late. Wrap it: =IFERROR(FILTER(...), "Nothing late").
• #CALC! in the late-lines list (Excel). Excel's FILTER can't return an empty array; add the third argument, "Nothing late".
• #VALUE! in SUMIFS or COUNTIFS. The ranges are different sizes, for example O2:O500 against C2:C400. Make every range the same rows.
• Days late shows a huge number. The expected date in column H is blank or text, so TODAY minus it counts from the spreadsheet's zero date at the end of 1899. Make column H a date column and add AND(H2<>"", ...) to the formula.
• "This value doesn't match the data validation restrictions defined for this cell" (Excel) or a rejected entry in Sheets. Someone typed a status not on the list. Pick from the dropdown or add the value to Lists!A1:A7.
• Can't change the dropdown in Excel. Data validation can't be edited while the sheet is protected or the workbook is shared (Microsoft Support). Unprotect first.

What types of purchase order trackers are there?

There are four types of purchase order tracker, and they differ mainly in how much of the updating they do for you:

Smartsheet's guide describes the same split: spreadsheets for small businesses that don't have much to track, accounting software for predictable month-to-month buying, and ERP systems for higher volume and multiple offices (Smartsheet). If you buy through QuickBooks, our guide to QuickBooks Online purchase orders shows what its PO feature tracks and what it leaves out.

When should a business move from a spreadsheet to PO software?

A business should move from a spreadsheet PO tracker to PO software when keeping the sheet current costs more time than the tracker saves, which usually shows up as one of four signs: more than one person edits the sheet and rows get overwritten, POs need approval before they go out, invoices regularly arrive for orders nobody logged, or you spend time each week chasing suppliers for confirmations the sheet can't send. Size is rarely the limit. Excel holds 1,048,576 rows per worksheet (Microsoft Support) and Google Sheets allows up to 20 million cells or 100 MB per file (Google Drive Help). Smartsheet's guide puts the line at roughly "more than 10 purchase orders a month" for ERP-style systems (Smartsheet); in practice, the trigger is usually the number of people involved, not the number of rows: two buyers editing the same sheet 5 days per week will overwrite each other long before 10 POs per month becomes a burden. Our comparison of Google Sheets vs RFQ software walks through where each one stops.

The step a tracker can't help with is the one before the PO: getting comparable quotes so the price in column F is the right one. AuraVMS is RFQ and purchase-order software for small and mid-sized businesses: you send one RFQ to several suppliers, they respond without creating an account, quotes line up side by side with automatic L1/L2/L3 price ranking, and the winning quote becomes the purchase order. AuraVMS starts at $4.99/month, billed annually.

FAQ

What is a purchase order tracker?

A purchase order tracker is a log, usually a spreadsheet or a module in PO software, with one row per purchase order line that records the supplier, item, quantity, price, expected and received dates, invoice and payment, plus a status. Its job is to show at any moment what is late, what is committed but not received, and what can be paid.

Is there a free purchase order tracker template?

Yes. Vertex42 offers a free PO tracker for Excel 2010 or later and Google Sheets, with a status dropdown and an aging summary (Vertex42), and Smartsheet links a free Excel tracking template in its guide. Building your own with the 14 columns above takes about an hour and gives you the days-late and open-value formulas that most templates leave out.

How do you track a partial delivery on a purchase order?

Track a partial delivery by recording the quantity received in its own column (I) next to the quantity ordered (E), setting the status to Partially received, and letting the open-value formula, =(E2-I2)*F2, show what is still due. With 300 of 500 cartons in, the line is 60% received and the open value is 200 x 1.12 = 224.00. Because each PO line is its own row, a shortfall on one item stays visible even when the rest of the PO has arrived.

Should a PO tracker be one row per PO or one row per line?

One row per line. A row per PO can't show that 300 of 500 cartons arrived while the tape arrived in full, and it makes invoice matching harder because invoices are checked line by line. Repeat the PO number on each line and use the supplier and PO number columns to group them.

How often should a purchase order tracker be updated?

Update it the same day anything happens to an order: the PO is issued, the supplier confirms, goods arrive, an invoice comes in or a payment goes out. The days-late column only works if received quantities are current; a tracker updated once a week will flag orders as late that arrived days ago.

Can Google Sheets send alerts for overdue purchase orders?

Not from formulas alone. Conditional formatting turns overdue rows red, but nobody sees it unless they open the sheet. Google Sheets notification rules (Tools > Notification settings) email you when the sheet changes, not when a date passes; date-based alerts need Apps Script or PO software.