Purchase Order Template in Excel: Layout, Formulas, PO Log and Three-Way Match

Quick answer: a purchase order (PO) template in Excel has a header (your company, the vendor, PO number, PO date, requested delivery date), ship-to and bill-to addresses, line items (item number, description, quantity, unit, unit price, line total), a totals block (subtotal, shipping, tax if applicable, total) and terms (payment terms such as Net 30, shipping terms, quote reference). A separate PO log tracks every order from approval to delivery, and a three-way match checks the PO, the receiving record and the vendor invoice before you pay.

Once the vendor accepts a PO, it documents what was agreed: items, quantities, prices and dates. The layout below works in Excel and Google Sheets; the figures in the example are illustrative.

What goes on a purchase order

Sales tax treatment depends on what you buy and where: goods bought for resale or used in production are often handled differently from supplies you consume. Check the rules in your state, and keep any resale or exemption certificates on file with the vendor.

Step 1: Vendor and item lists

On a Vendors sheet, store each vendor’s details and default payment terms. On an Items sheet, store SKU, description, unit, last price and preferred vendor. On the PO, pick the vendor and SKUs from dropdown lists (Data > Data Validation) and fill the rest with XLOOKUP. If you already keep an inventory management template, reuse its SKU list so purchases and stock use the same codes.

Step 2: Line items and totals

Line items start in row 18: A SKU, B description, C quantity, D unit, E unit price, F line total.

• Description (B18): =IF(A18="","",XLOOKUP(A18,Items[SKU],Items[Description],"Check SKU"))
• Unit price (E18): =IF(A18="","",XLOOKUP(A18,Items[SKU],Items[Last price])), overwritten if the quote gives a different price.
• Line total (F18): =IF(A18="","",ROUND(C18*E18,2))
• Subtotal: =SUM(F18:F37)
• Total: subtotal − discount + shipping + tax.

Example: packaging order for an online shop

Check: 500 × 0.62 = 310; 1,000 × 0.11 = 110; 8 × 14.50 = 116; 310 + 110 + 116 = 536; 536 + 45 = 581. Sales tax is not shown because, in this example, the shop’s packaging is assumed to be covered by an exemption in its state; enter it as a separate line if it applies to you.

Step 3: PO numbers and approval

• Numbering: a fixed format such as PO-2611-007 (year, month, sequence): ="PO-"&TEXT(H3,"yymm")&"-"&TEXT(H2,"000"), with the PO date in H3 and the sequence in H2. Get the next sequence from the log: =COUNTIFS(Log[Month],EOMONTH(H3,-1)+1)+1.
• Approval limits: write a simple rule into the template, for example: up to $500 approved by the purchaser, above $500 by the owner. A status cell can flag it: =IF(Total>500,"Owner approval required","").
• Changes: if quantities or prices change after sending, issue a revised PO (PO-2611-007 Rev 1) rather than editing the original file.

Save each PO as a PDF named with the PO number and vendor before sending, and ask the vendor to confirm by email.

Step 4: The PO log

A Log table with one row per PO line: PO number, month, date, vendor, SKU, quantity ordered, unit price, expected delivery, quantity received, received date, invoice number, invoice amount, status.

• Open quantity: =[@[Qty ordered]]-[@[Qty received]]
• Status: =IF([@[Qty received]]=0,IF(TODAY()>[@[Expected delivery]],"Late","Open"),IF([@[Qty received]]<[@[Qty ordered]],"Partial","Received"))
• Open commitments (money you have promised but not yet been invoiced for): =SUMPRODUCT(Log[Open qty],Log[Unit price])
• Late lines by vendor: =COUNTIFS(Log[Vendor],A2,Log[Status],"Late")

Open commitments are easy to forget in cash planning: they are not bills yet, but they will be.

Step 5: Three-way match before paying

When the vendor invoice arrives, compare three documents:

1. Purchase order: what you ordered and the agreed price.
2. Receiving record or packing slip: what actually arrived, in what condition.
3. Vendor invoice: what you are being charged.

In the log, a match check per line: =IF(AND([@[Qty received]]=[@[Qty ordered]],ROUND([@[Invoice amount]],2)=ROUND([@[Qty received]]*[@[Unit price]],2)),"Match","Check"). Pay lines marked “Match”; resolve the rest with the vendor first (short shipment, price change, damaged goods). If you invoice your own customers in Excel, the invoice template uses the same numbering and tracking ideas.

Common mistakes

• Vague descriptions (“boxes”): the vendor ships a different size.
• No requested delivery date: you cannot hold a vendor to a date you never stated.
• Orders by text message with no PO: hard to match against the invoice later.
• Paying before receiving is checked.
• Reusing PO numbers when copying the template.

FAQ

Is a purchase order a contract?

A PO is an offer to buy; once the vendor accepts it (in writing or by shipping), it generally documents a binding agreement on its terms. For large or ongoing purchases, businesses often add a master agreement and use POs for individual orders.

Do small businesses need purchase orders?

Not for every purchase. They are most useful for inventory, recurring supplies and anything above an amount you set, because they create a clear record to check deliveries and invoices against.

Does this work in Google Sheets?

Yes. XLOOKUP, SUMPRODUCT, COUNTIFS and TEXT are all available; download each PO as a PDF from File > Download.

More guides: Online Selling & Inventory.