Explore Dika Ops

How do I create a purchase order spreadsheet in Excel?

How do I create a purchase order spreadsheet in Excel? Set up the header, order lines, totals, numbering and an approval step that holds up in daily use.

7 min read Business software
Hand writing on a printed purchase order form with a yellow pen

To create a purchase order spreadsheet in Excel, build one sheet with a header block for the buyer, the supplier and the order details, a table of order lines where each row calculates its own tax and total, and a totals block that adds the lines up. Save the sheet as a template, number every order from a log, and send the supplier a PDF only after someone other than the buyer has approved it. A careful version takes about an hour to set up.

A spreadsheet is a sensible starting point, but it breaks in predictable ways. Someone copies an old file and forgets to change the number, a total gets typed over by hand, or a delivery arrives with no order to match it to. The method below avoids those problems with fixed layouts, formulas and a simple approval record. If you would rather start from a form, our free purchase order generator asks for the same fields, so you can check your layout against it.

Decide which fields the order needs

Before you open Excel, agree the fields with whoever receives the goods and whoever pays the invoice. Each missing field usually means a follow-up question later. A usable order normally includes:

  • Order details: purchase order number, order date, requested delivery date and currency.
  • Buyer and delivery: your company name, the delivery address and the person who receives the goods.
  • Supplier: supplier name, address, contact and the supplier's own reference, if they have one.
  • Terms: payment terms, delivery terms and any instructions the supplier must follow.
  • Approval: the name of the person who approved the order and the date of approval.

Set up the header block and the order number

Use one sheet for the order and a second sheet as a log. Follow these steps once, then keep the file as a template that nobody edits directly.

  1. Open a blank workbook and type the header labels in column A, leaving column B for the values.
  2. Create a second sheet called Log with columns for order number, order date, supplier, total and approver.
  3. Type the next order number by hand, taking it from the last row of the log. Never copy a number from an old order.
  4. Select the payment terms cell, open Data, choose Data Validation and create a list such as Net 30, Net 60 and Prepaid. This stops variations like "30 days" and "thirty net" from appearing across your orders.
  5. Protect the sheet with Review, Protect Sheet, and leave only the input cells editable. Totals and formulas should not be typed over.

Build the order lines with formulas

Put the line table below the header block and turn it into an Excel Table using Insert, Table. A table extends its formulas automatically when you add a row, so you do not have to copy them down by hand and risk missing one. Each row needs the columns below.

ColumnWhat to enterFormula or check
Item codeYour code or the supplier's part numberCheck it against the supplier catalogue
DescriptionPlain description with size or specificationAvoid internal abbreviations the supplier may not know
QuantityUnits orderedWhole numbers unless you buy by weight
Unit priceAgreed price per unit, before taxCopy it only from the latest quotation
Line netNothing, calculated=Quantity * Unit price
Tax rateThe rate that applies to this itemUse a drop-down list, not free text
Line totalNothing, calculated=Line net * (1 + Tax rate)

Below the table, add three totals: net subtotal, total tax and grand total. Each one uses SUM across the matching line column. Check the result with a calculator once when you build the template, and again whenever you change a formula.

Example, with the assumptions stated: 40 cartons at a unit price of 12.50, plus tax at 20 percent. The line net is 500.00, the tax is 100.00 and the line total is 600.00. If your sheet shows anything else, a formula is pointing at the wrong cell.

Add an approval step the sheet cannot skip

A spreadsheet will not stop anyone from editing a total, so the control has to come from how you use it. The rule we recommend is simple: the person who raises an order does not approve it. Give the approver a cell for their name and a cell for the date, and only let them fill those cells. Once approved, save a PDF copy in a folder where buyers can read the file but not change it.

The table below shows where a plain spreadsheet works, and where it needs a second check.

ControlHow to do it in a spreadsheetWhere it fails
Order numberingLog sheet, with the next number typed from the logTwo people number orders from different copies
ApprovalApprover name and date cells, filled only by the approverLines edited after the approval date is entered
Version controlRead-only template, and each order saved as a new fileAn old total is reused in a new order
Receipt matchingGoods-received column in the logDeliveries are recorded only in email
Two people reviewing a printed document together at a table
Reviewing an order before it reaches the supplier is a step a spreadsheet cannot do for you.

Send the order and record it

Once the order is approved, follow the same routine each time so that nothing depends on memory.

  1. Check the order against the supplier quotation line by line, including tax and delivery terms.
  2. Save the approved file as a PDF with the order number in the file name.
  3. Send it from a shared purchasing mailbox, so the order does not depend on one person being in the office.
  4. Add the order to the log with the approver's name and the expected delivery date.
  5. When the goods arrive, record the quantity received and compare it with the order before you pay the invoice.

When a spreadsheet is no longer enough

Spreadsheets suit a few orders a month, one buyer and a stable list of suppliers. They become risky when several people order at once, when stock levels matter, or when every invoice must be matched to an order and a delivery. At that point you spend more time checking files than running purchasing.

In Dika Ops, purchasing is one module alongside sales, inventory, accounting and the others, and they all run on one record. An order stays linked to the supplier, the goods it brings in and the accounts it affects, so you do not retype the same numbers in three places. We built Dika Ops for companies that make, move and sell things. You can read about it on the purchasing module page.

If you need a tool that follows your own approval rules, our custom software team can also build one with you. You can read our earlier guide on how to create a purchase order for a supplier for the wording and fields, our inventory guide for small businesses if stock counts drive your orders, and our month-end close checklist to see how purchase records feed the accounts.

Frequently asked questions

Can I create a purchase order spreadsheet for free?

Yes. The method works in any spreadsheet program with tables, formulas and data validation, including free online versions of Excel and Google Sheets. The steps are the same, though menu names differ, so check the help menu if a command is missing.

Which formula calculates the total on a purchase order?

For each line, multiply quantity by unit price to get the net amount, then add tax with the line net multiplied by one plus the tax rate. The order totals use SUM over the line columns. Test the result against a worked example before you use the template for a real order.

Should purchase order numbers be sequential?

Sequential numbers are the easiest to audit, because a gap is visible at a glance. If you cancel an order, keep its number in the log and mark it as void instead of reusing it. This keeps your records consistent with what suppliers have received.

Who should approve a purchase order?

Choose someone who has authority over the budget and who is not the person raising the order. In a small team this may be the owner or a manager. Record the approver's name and the date, and keep the approved PDF as your reference.

When should a small business move from spreadsheets to purchasing software?

Consider a system when several people place orders, when stock levels must update with each delivery, or when invoices are regularly matched late to orders. Check your local rules and your accountant for any extra record-keeping you need for purchases.

What to do next

Build the template this week: header block, log sheet, line table with formulas, and an approval cell. Run three test orders against a calculator before anyone relies on it. If the spreadsheet starts to slow the team down, talk to us. We are Dika Design, and we will look at your orders with you and tell you honestly whether a system would pay back.

Have a project in mind? Let us talk it through.

Tell us what you want to improve. We will reply with a clear next step.