Tracking supplier orders in Excel: a layout that works, until it doesn't.
If you're going to use a spreadsheet, use a good one. Here's the layout — and the signs it's time to stop.
In short. A practical column layout for tracking purchase orders and shipments in Excel or Google Sheets, the formulas worth adding, and the point at which a spreadsheet stops being the right tool.
The columns
One row per order (or per shipment if an order ships in parts). Keep one value per cell and dates as real dates.
- Order name · PO number · Supplier · Product · Quantity · Unit · Price · Currency · Incoterm
- Payment terms · Advance paid (date) · Balance due (formula) · Invoice number · Invoice status · Paid (date, reference)
- Stage (a drop-down: Sample, Quotation, PO, Production, Export Clearance, Departure, Shipping Documents, Port Arrival, Import Clearance, Goods Received, Invoice, Payment, Inspection, Feedback)
- ETD · ETA · Vessel / carrier · Container · B/L number · Tracking link
- Documents: B/L ✓ · CI ✓ · PL ✓ · CO ✓ · COA ✓ · Health ✓ (one column each)
- Free time (days) · Discharge date · Last free day (formula) · Broker
- Need-by date · Late? (formula) · Whose move · Notes · Last updated
Formulas worth adding
- Balance due = trigger date + net days (see due dates).
- Last free day = discharge date + free days.
- Late? = IF(ETA > need-by, “LATE”, “”).
- Days waiting = TODAY() − last updated, conditionally formatted at 5 and 10 days.
- Documents complete = COUNTA of the tick columns against the required count for that destination.
Rules that keep it alive
- One owner per sheet; everyone else comments.
- Update at a fixed time daily, from the inbox, oldest first.
- Never delete rows; mark them closed.
- Keep a second tab for suppliers (contacts, bank details, certificates with expiry).
Where it breaks
At roughly forty open orders, three people, or two languages. The sheet stops being current because updating it means reading everyone's email; the supplier can't see it; the customer asks anyway; and when the owner leaves, the sheet goes with them. The failure is not the spreadsheet — it's that a spreadsheet can't read email.
Pacifica builds the same table from the emails and ERP records themselves, keeps every column above (with a source and time on each value), and exports it as CSV whenever you want the spreadsheet back.
Questions
Is there a free template?
The column list above is the template; paste it into a sheet and add the drop-down for Stage and the three formulas. Keep dates as dates.
Excel or Google Sheets?
Sheets if more than one person updates it; Excel if it must live in a shared drive with the ERP exports. Neither reads your inbox.
When should we move off spreadsheets?
When “where is it?” is asked daily, when suppliers or customers need to see status, or when a colleague leaving would take the knowledge with them.
Related
See it on your own orders.
Launch your workspace in 30 seconds. First month free.