Managing purchase orders in spreadsheets sounds simple — until you have 50 open POs across 10 vendors and you can't remember which ones have arrived, which are overdue, and how much is still outstanding. A proper PO tracker in Excel solves all of this.
What Your PO Tracker Needs
| Column | Purpose |
|---|---|
| PO Number | Unique identifier for each order |
| PO Date | When the order was placed |
| Vendor | Supplier name |
| Item / SKU | What was ordered |
| Qty Ordered | How many units |
| Unit Cost | Price per unit |
| Total Cost | Qty × Unit Cost (formula) |
| Expected Date | When delivery is due |
| Received Date | When it actually arrived |
| Qty Received | How many units arrived |
| Status | Pending / In Transit / Partial / Received |
| Outstanding ($) | Amount not yet received (formula) |
Key Formulas
Total Cost
Outstanding Amount
Days Overdue
Vendor Lookup (from Vendors sheet)
Set up your PO Log sheet
Create columns for all the fields above. Format as an Excel Table (Insert → Table) so formulas auto-fill when you add new rows.
Create a Vendors reference sheet
A separate sheet with vendor name, contact, payment terms, and lead time. Use VLOOKUP to pull vendor details into your PO log automatically.
Add Status dropdown
Select the Status column → Data → Data Validation → List → enter: Pending,In Transit,Partial,Received. Now you pick from a dropdown instead of typing.
Color-code the Status column
Conditional formatting: Received = green, Partial = amber, Pending = blue, In Transit = purple. You can see the status of every PO at a glance.
Add a Summary section
On a separate Dashboard sheet: total POs, total value, outstanding amount, count by status. Update automatically using SUMIF and COUNTIF.
Common Mistakes to Avoid
FAQ
🛒 Get the Pre-Built Purchase Order Tracker
Free download — no sign-up required.
⬇ Download Free Template