Excel Purchase Order Template Generator

About this template

A purchase order is the document that goes out before anything is bought: the buyer states exactly what they want, at what price, by when, and gives the order a number the supplier will quote back on their invoice. This workbook produces that document, with the line arithmetic already in place so the committed value is never a figure someone typed twice.

It is for the buying side - an office manager ordering supplies, a workshop ordering materials, anyone whose finance process wants an approved order on file before the invoice arrives. If you are the one sending a bill rather than placing an order, the invoice template is the same layout aimed the other way.

The sheet is laid out with your company in column A rows 3 to 7, the supplier in column C over the same rows, and the reference block in columns D and E: PO number, order date, and a required-by date, both date cells formatted as yyyy-mm-dd. Row 9 heads the table with Item code, Description, Qty, Unit price and Total, and fifteen blank order lines follow by default. Subtotal, Tax rate, Tax and Total stack under the Total column, and a wrapped notes block at the bottom is where delivery instructions or approval wording go.

The item-code column is the practical difference between this and an invoice: it is the field a supplier picks up to match your order against their catalogue, and getting it right is what stops the wrong item being shipped.

How to use it

1. Name both parties - A4:A7 is the company placing the order and where the goods are billed; C4:C7 is the supplier. Both are plain text blocks, and blank lines are better deleted than left showing their placeholder text.
2. Give the order a reference and a deadline - The PO number belongs in E3 - suppliers quote it back, so keep it unique. E4 is the order date and E5 the required-by date; both are formatted as dates so they sort and subtract properly if you keep a folder of orders.
3. Format the item-code column as text first - Select column A, choose Format Cells > Text, and only then type your codes. Excel treats 00123 as the number 123 in a general cell and the leading zeros disappear, which is the single most common way a purchase order goes out wrong.
4. Enter each line - Item code, description, quantity in column C and unit price in column D. Column E multiplies the last two as soon as both are numbers, and the Subtotal picks the line up immediately.
5. Set the tax rate and check the total - E26 is percent-formatted, so type 20 for twenty percent; the Tax row multiplies the subtotal by it and the Total adds the two together. That Total is the figure you are committing to, so it is worth reading before the order leaves.
6. Add order lines inside the block - Insert a row between the first and last item row and SUM(E10:E24) widens to include it. Copy the Total cell down from the row above to give the new line its formula, since an inserted row arrives empty.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(COUNT(C10,D10)=2,C10*D10,"") - The line Total. COUNT checks that both the quantity and the unit price are numbers before multiplying them, so rows you have not used yet stay visually empty instead of showing a zero the supplier has to interpret.
• =SUM(E10:E24) - The Subtotal, spanning exactly the fifteen order lines the default builds. Unused lines return an empty string, which SUM skips.
• =E25*E26 - Tax as subtotal times the rate cell directly above it. Keeping the rate in its own cell means a supplier in a different tax jurisdiction is a one-cell edit, and the rate you applied stays visible on the order.
• =E25+E27 - The order total: subtotal plus tax. With the tax line switched off at generation the formula becomes =E25 and the Total keeps its position on the sheet.

Customising it

• Derive the deadline from the order date: put =E4+14 in E5 for a fortnight's lead time, and every order you copy from this one keeps the same rule.
• Add a receiving check by inserting a Qty received column after the Total and a variance formula such as =C10-F10, so the same sheet records what actually turned up.
• Constrain the item codes with Data > Data Validation > List if you order from a fixed catalogue - paste the codes as the source and the column becomes a dropdown.
• Add approval lines under the notes block: a row for Approved by and one for Date is often all a finance process needs, and the notes area is already merged and wrapped for longer instructions.
• Set Page Layout > Print Area to A1:E32 and Fit All Columns on One Page so the order prints on a single sheet for the file copy.