How to Make a Purchase Order in Excel With Formulas
By ExcelReady TeamUpdated 7 min read
The short answer
To make a purchase order in Excel, put the order details at the top, then a line-item table with Code, Description, Qty, Unit price and Line total. Keep prices on a separate sheet and pull them in with XLOOKUP. Multiply quantity by price for each line, then add a subtotal with SUM, tax with ROUND and a grand total.
Key takeaways
- A purchase order has three parts: order details at the top, line items in the middle and totals at the bottom.
- Keep prices on their own sheet and fetch them with XLOOKUP, so nobody types a price by hand.
- Line total is quantity times unit price; the subtotal is a SUM of the line totals.
- Round tax with ROUND(...,2) so the printed numbers add up to the printed total.
- Wrap lookups in IF(code="","",...) so empty lines stay blank instead of showing #N/A.
On this page+
A purchase order in Excel is a simple form: order details at the top, a table of items in the middle and totals at the bottom. With a few formulas, it fills in each price from your price list and adds itself up, so the order you send a supplier is always correct. This guide builds one step by step, with every formula checked on real numbers, for office managers, buyers and anyone who orders supplies.
What is a purchase order?#
A purchase order (PO) is the document a buyer sends to a supplier to place an order. It lists what you want, how many, the agreed price and where to deliver. The supplier later sends an invoice, and checking the invoice against the PO is a standard step before anyone pays.
A complete purchase order includes:
- A unique PO number and the order date
- Your company details and the supplier's name and address
- The delivery address and the date you need the goods
- One line per item: code, description, quantity, unit price, line total
- Subtotal, tax, shipping and grand total
- Payment terms (for example, Net 30) and who approved the order
If you already build invoices, the layout will feel familiar: a PO is the same shape seen from the buyer's side. The guide to building an invoice with formulas uses the same totals block.
How to lay out the purchase order sheet#
Use two sheets: PO for the form and Prices for the price list. Keeping prices separate means you update a price once, and every new order uses it.
The Prices sheet is a plain table:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Item | Unit price |
| 2 | PB-110 | Printer paper A4 (box) | 32.50 |
| 3 | PB-210 | Toner cartridge | 84.00 |
| 4 | PB-305 | Ballpoint pens (50) | 12.75 |
| 5 | PB-420 | Stapler | 18.40 |
| 6 | PB-515 | Desk organiser | 26.90 |
On the PO sheet, put the order details in rows 1 to 8 (PO number, date, supplier, ship-to address, payment terms). Start the line items at row 10 with the headers Code, Description, Qty, Unit price, Line total in A10:E10. Leave rows 11 to 14 for items. The totals go in E15:E18.
How to fill in prices with XLOOKUP#
XLOOKUP finds the item code in the price list and returns its price, so nobody types prices by hand. In D11 (Unit price), type:
=XLOOKUP(A11,Prices!$A$2:$A$6,Prices!$C$2:$C$6)The three parts are: what to find (the code in A11), where to look (the codes on the Prices sheet) and what to return (the prices). The $ signs lock the price list so it doesn't move when you fill the formula down. For code PB-210, the result is 84.00.
The description works the same way, returning column B instead. In B11:
=XLOOKUP(A11,Prices!$A$2:$A$6,Prices!$B$2:$B$6,"Code not found")The fourth argument is what XLOOKUP shows when the code isn't in the list. It turns a confusing #N/A into a clear message. The XLOOKUP function guide explains each argument.
XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. If your file must open in older Excel, use =VLOOKUP(A11,Prices!$A$2:$C$6,3,FALSE) for the price instead.
Keep empty lines blank#
A PO rarely uses every line. An empty code makes XLOOKUP return #N/A, which looks broken when printed. Wrap the lookup in IF:
=IF(A11="","",XLOOKUP(A11,Prices!$A$2:$A$6,Prices!$C$2:$C$6))If A11 is empty, the cell shows nothing. Otherwise, it looks up the price as before.
How to calculate line totals and the order total#
Each line total is quantity times unit price, and the totals block adds them up. Say the order is 12 toner cartridges, 20 boxes of paper and 6 packs of pens:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 10 | Code | Description | Qty | Unit price | Line total |
| 11 | PB-210 | Toner cartridge | 12 | 84.00 | 1,008.00 |
| 12 | PB-110 | Printer paper A4 (box) | 20 | 32.50 | 650.00 |
| 13 | PB-305 | Ballpoint pens (50) | 6 | 12.75 | 76.50 |
| 14 |
In E11, type =IF(A11="","",C11*D11) and fill it down to E14. Then build the totals:
| Cell | Label | Formula | Result |
|---|---|---|---|
| E15 | Subtotal | =SUM(E11:E14) | 1,734.50 |
| E16 | Tax (8%) | =ROUND(E15*G16,2) | 138.76 |
| E17 | Shipping | 45 (typed) | 45.00 |
| E18 | Total | =SUM(E15:E17) | 1,918.26 |
G16 holds the tax rate, 8%. Keeping it in a cell means you change it once when the rate changes. ROUND keeps the tax to whole cents, so the printed lines add up exactly to the printed total. The SUM function guide covers why SUM safely skips the blank line in row 14: the "" from the IF formula is text, and SUM ignores text in a range.
If your supplier gives volume discounts, the article on price lists with discounts shows how to apply a discount tier per line.
How to number purchase orders automatically#
Every PO needs a unique number so you can match it to deliveries and invoices. Keep the number itself in a cell, say H1, and build the display version with TEXT:
="PO-"&TEXT(H1,"0000")If H1 holds 42, the result is PO-0042. The "0000" format pads the number with zeros, so PO numbers sort correctly.
Excel won't remember which numbers you've used. The simplest system is: when an order is final, save a copy of the file (or the sheet) named after the PO number, then add 1 to H1 in the template. Saving a PDF copy for the supplier works well too.
How to make the purchase order hard to break#
A template that several people use needs a little protection. Three quick steps help:
- Add a drop-down for codes. Select A11:A14, go to Data > Data Validation, choose List and point the source at the codes on the Prices sheet. Now people pick a valid code instead of typing one.
- Lock the formula cells. Unlock the input cells (codes, quantities, details) in Format Cells > Protection, then protect the sheet with Review > Protect Sheet. Format Cells opens with Ctrl+1 on Windows and ⌘1 on a Mac.
- Colour the inputs. Give the cells people should fill a light fill colour, so it's obvious where to type.
Track your purchase orders#
Once orders go out, you need to know which are open, delivered or invoiced. Keep a simple log with one row per PO: number, date, supplier, total and status. The guide to an Excel order tracker shows how to count open orders and total spend by supplier with COUNTIFS and SUMIFS.
If you're sending prices to customers rather than ordering from suppliers, the quote template guide is the mirror image of this one. And if purchasing is your job, see the Excel skills for procurement roles.
Practise it#
Try the exercise on this page: it asks for one line total, using XLOOKUP inside a multiplication. Then practise XLOOKUP in a live spreadsheet, and build a full set of totals in the budget maths practice lesson. Each one checks your formula the moment you press Enter.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The price list is in A1:C6. The PO line in E2:E3 orders 12 of item PB-210. Click E4 and work out the line total: the quantity in E3 times the price XLOOKUP finds for the code in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | PO line | |||||
| 2 | PB-210 | |||||
| 3 | ||||||
| 4 | Line total | |||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 |
Tip: XLOOKUP(E2,A2:A6,C2:C6) finds the unit price. Multiply it by the quantity in E3.
Practise this in a lesson
Frequently asked questions
How do I create a purchase order in Excel?
Add the PO number, date, supplier and delivery address at the top. Below, make a table with Code, Description, Qty, Unit price and Line total columns. Use XLOOKUP for prices, multiply for line totals and SUM for the subtotal and total.
What should a purchase order include?
A unique PO number, the order date, buyer and supplier details, a delivery address, each item with quantity and agreed price, the totals including tax and shipping, payment terms and an approval signature or name.
How do I number purchase orders automatically in Excel?
Keep the last number in a cell and add 1 for each new order. To show it as PO-0042, use ="PO-"&TEXT(B2,"0000"), where B2 holds 42. Save each finished order as a copy so the number isn't reused.
What is the difference between a purchase order and an invoice?
The buyer sends a purchase order to the supplier to place an order. The supplier sends an invoice back to ask for payment. Matching the two documents is a standard check before paying.
Why does my purchase order show #N/A?
XLOOKUP can't find the item code in the price list. Check for typos and extra spaces, or wrap the formula in IF so empty lines return blank, and use XLOOKUP's fourth argument to show a message such as Code not found.