A purchase order tracker is the sheet that tells you, at a glance, what you have ordered, what has arrived, and what is still open. Done well it replaces a pile of PDFs and a few anxious emails to suppliers. Done badly it becomes a graveyard of half-filled rows nobody trusts. This walks through a tracker that stays useful: the columns that matter, the formulas that flag problems, and the fastest way to get order data into it without retyping every line.
What columns does a purchase order tracker need?
A good PO tracker has one row per order (or one row per line item if you track receipts at the item level) and these core columns: PO number, order date, supplier, item or description, quantity ordered, unit price, line total, expected delivery date, quantity received, status, and a notes field. The header fields (PO number, supplier, dates) give you something to filter and sort on; the numeric fields (quantity, price, total) are what your formulas work with. Keep every amount as a real number, not text, or the totals and the status checks will not calculate.
Resist the urge to add twenty columns on day one. Start with those, use the tracker for a few weeks, and add a column only when you find yourself repeatedly writing the same note. A lean tracker people actually update beats a detailed one they abandon.
How do I set up the tracker structure?
Put your headers in row 1, then select the whole range and turn it into an Excel Table with Ctrl+T. A Table is worth the two seconds it takes: formulas auto-fill down to new rows, the headers stay visible when you scroll, and you can reference columns by name instead of by letter. Name it something obvious like PO_Tracker so formulas read clearly.
Set the data types while the sheet is empty. Format the date columns as dates, the price and total columns as currency or number, and leave PO number as text if your numbers have leading zeros (otherwise Excel strips them). Add a data-validation dropdown on the Status column with a short fixed list: Open, Partial, Received, Cancelled. A dropdown keeps the status values consistent, which is what lets you filter and count them later without chasing typos like "recieved".
How do I calculate line totals and order status with formulas?
The line total is the simplest formula in the sheet: quantity times unit price. In a Table you can write it once as =[@Quantity]*[@[Unit price]] and it fills the whole column. For a grand total across the tracker, =SUM(PO_Tracker[Line total]) stays correct as rows are added.
Order status is where a tracker earns its keep. If you record quantity received, you can derive status instead of updating it by hand: =IF([@[Qty received]]=0,"Open",IF([@[Qty received]]<[@Quantity],"Partial","Received")). To flag overdue orders, compare the expected date to today: =IF(AND([@Status]<>"Received",[@[Expected date]] Conditional formatting turns the status logic into something you can see. Select the data rows, open Conditional Formatting, New Rule, and use a formula rule. A rule of =$K2="LATE" (pointing at your late-flag column) with a red fill makes overdue orders jump out. Add a second rule, =$J2="Partial", with an amber fill for partially received orders, and a green fill for Received. Order the rules so the most urgent one wins. Color coding is only as good as the data behind it, so make sure the formulas in the previous section are driving the cells the rules read. When the received quantity changes, the status recalculates and the row recolors on its own. That feedback loop is what keeps people entering receipts: they can see the board change. Once orders are flowing in, you will want roll-ups. SUMIF answers most of them. Spend with one supplier: =SUMIF(PO_Tracker[Supplier],"Acme",PO_Tracker[Line total]). Open commitment still outstanding: =SUMIF(PO_Tracker[Status],"Open",PO_Tracker[Line total]). Count of late orders: =COUNTIF(PO_Tracker[Late flag],"LATE"). For a fuller view, drop a PivotTable on the Table and put Supplier in rows, Status in columns, and Line total in values. You get a clean matrix of committed spend by supplier and stage, and it refreshes with one click as the tracker grows. This is the report you bring to a supplier review or a month-end spend check. Here is the part that decides whether the tracker survives. If every incoming PO arrives as a PDF and someone has to retype the PO number, supplier, and every line item, the tracker falls behind within a week. The fix is to convert each order to a spreadsheet first, then paste the rows in. A PDF purchase order to Excel converter pulls the SKU, description, quantity, unit price, and line total into clean columns with the figures kept numeric, so they drop straight under your headers. For a stack of orders at once, the batch converter gives every file the same layout so you can append them in one go. If a price or quantity ever lands as text and your totals stop calculating, the cause and the fix are in why numbers come in as text. Keeping the inbound data numeric from the start is what lets all the formulas above just work. Teams that manage a high volume of orders eventually outgrow a spreadsheet and move to dedicated purchase order management software, but a well-built Excel tracker carries you a long way first. Track at the order level when you mostly care about whether an order is open, late, or closed, and one row per PO keeps the sheet readable. Switch to one row per line item when you receive orders in parts, need to reconcile partial deliveries, or run a three-way match against receipts and invoices before paying. Line-item tracking is more rows to maintain, but it is the only way to catch a short shipment or a price that does not match the order. That match is where the tracker connects to the rest of the buying cycle. The PO sets what you agreed to pay; the receipt confirms what arrived; the invoice is what the supplier billed. When all three line up, payment is safe. Teams that run that check at volume often hand the invoice side to accounts payable automation software so the matching happens automatically against the PO data you have already captured. A purchase order tracker works when the data going in is clean and the status updates itself. Build the columns once, let formulas derive status and flag late orders, total spend with SUMIF or a PivotTable, and convert incoming PDFs to numeric rows instead of retyping them. Do that and the sheet stays trustworthy, which is the whole point: a tracker you believe is a tracker you use. When you are ready to bring the next batch of orders in, start at the purchase order PDF to Excel converter.How do I highlight late or open orders automatically?
How do I total spend by supplier or status?
How do I get purchase order data into the tracker without retyping it?
When should I track at the line-item level instead of the order level?
Keep the tracker honest