Search for an order form template and Excel is the first answer everyone gives, for a good reason: unlike every form builder, Excel understands arithmetic. Quantities times prices, subtotals, delivery fees, discounts, running totals across a whole week — that is what spreadsheets were invented for. The trouble starts when the file has to travel. This guide covers both halves: the fifteen-minute build that gets you a form which adds itself up, and the six ways it falls apart once customers are involved.
Option A: start from a Microsoft template
Worth knowing before you go looking: Microsoft does not publish an order form template. Search its gallery for "order form" or "purchase order" and you come back empty. What it does publish free are close cousins — the invoice templates (Sales invoice, Small business sales invoice, Simple invoice) and the inventory templates (Product price list, Inventory list with reorder highlighting). Find them in Excel under File > New, or in the Excel template gallery on the web.
An invoice template hands you a formatted header, an itemised table with line-total formulas in place, and a subtotal-and-tax footer — most of the layout. It does not hand you your products, your prices, or any protection against the formulas being overwritten. Budget twenty minutes to strip the sample data and re-point the formulas at your own rows.
Option B: build one that actually calculates
Nine steps. Assume a fresh workbook.
- 1
Make two sheets: Order and Products
Everything a customer touches lives on Order. Everything you maintain lives on Products. Keeping them apart is what stops a price edit from breaking a submitted order.
- 2
Fill the Products sheet
Three columns: Item, Unit, Price. One row per product, no blank rows, no merged cells, no currency symbols typed into cells — format the column as currency instead. Select the block and press Ctrl+T (Ctrl+L in Excel for the web) to turn it into a Table, then rename it under Table Design > Table Name to something like ProductList. Give it more than one row before you build on it; single-row tables handle structured references badly.
- 3
Build the header block on Order
Rows 1 to 8: business name, order date, customer name, phone, email, pickup or delivery, pickup date, slot. Label in column A, input cell in column B. Shade the input cells a pale colour so it is obvious where to type.
- 4
Lay out the line-item table
Around row 11, five headings: Item, Unit, Unit price, Qty, Line total. Leave twenty empty rows beneath — more than almost anyone orders, and still one printed page.
- 5
Add the product dropdown
Select the Item cells (say B12:B31), then Data > Data Validation > Allow: List, and set Source to =ProductList[Item]. If your version of Excel refuses the table reference, select the Item column on Products, name it in the Name Box (for example ProductNames), and use =ProductNames instead.
- 6
Look up the unit and the price
In the Unit price cell for the first line, use =IFERROR(XLOOKUP($B12, ProductList[Item], ProductList[Price]), ""). Do the same for Unit against the Unit column. XLOOKUP does not exist in Excel 2016 or 2019 — on those, use =IFERROR(VLOOKUP($B12, Products!$A:$C, 3, FALSE), ""), and note that the FALSE is mandatory for an exact match. The IFERROR is what stops empty rows showing #N/A on a printed page.
- 7
Calculate line totals and the order total
Line total: =IF($E12="", "", $D12*$E12) where D is unit price and E is quantity. Order subtotal: =SUM(F12:F31). Add rows beneath for a delivery fee and a deposit, and a Total that is =F33-F35 or whatever your arrangement is. Format every money cell as currency, not as a number with a dollar sign typed in.
- 8
Protect everything except the inputs
This is counterintuitive: every cell is Locked by default, and Locked does nothing until protection is switched on. So select the cells a customer should type in — header inputs, Item cells, Qty cells — press Ctrl+1, open the Protection tab, and *clear* the Locked checkbox. Then Review > Protect Sheet. Now nobody deletes a formula by tabbing into the wrong cell. Excel for the web uses a different model (Review > Manage Protection > Unlocked ranges), so set this up on the desktop app.
- 9
Save it as a template
File > Save As, and choose Excel Template (*.xltx). Opening a .xltx creates a fresh copy rather than editing the original, so the master file cannot be overwritten by someone who forgot to Save As.
Two optional upgrades worth the five minutes
- Conditional formatting on the quantity column — a rule that highlights any cell above your per-item maximum in red. It will not stop anyone, but it puts the limit in front of them.
- A one-cell order reference using =TEXT(TODAY(),"yymmdd")&"-"& a manually typed sequence. Crude, but an order with a reference on it is an order you can find again. Excel has no auto-numbering button, and =ROW() is positional — sort or delete a row and every "order number" changes.
Getting it to the customer
There are three routes and each has a specific cost.
| Route | How it works | What it costs you |
|---|---|---|
| Email the .xlsx | Attach the file, they fill it in, they email it back. | Every customer now has their own copy. Prices drift, formulas get pasted over, and you cannot tell which version someone ordered from. |
| OneDrive share link | Upload to OneDrive, Share, set to "Anyone with the link can edit", they fill it in in Excel for the web. | One file, so everyone types into the same rows. Fine for one trade account, chaos for twenty at once — and if one person opens it in a version that does not support co-authoring, everyone else gets a locked-file error. |
| Print or PDF | Print the protected sheet, or Save As PDF, and let people fill it in by hand at the counter. | Works perfectly and always has. You then retype every order yourself. |
For a business selling to a handful of trade accounts, the OneDrive route is genuinely reasonable — a shared workbook per stockist, updated weekly, is how plenty of wholesale still runs. For anything consumer-facing, it is not.
Where an Excel order form breaks
These are not theoretical. They are the six failures that show up in every business that has run orders on spreadsheets for more than a season.
1. Version sprawl
The moment you email the file, you have as many order forms as customers, each frozen at the prices that applied the day it was downloaded. Six weeks later someone orders at last quarter's prices and is entirely right to argue about it.
2. Formulas do not survive customers
Sheet protection helps and does not solve it. People paste text over cells, insert rows in the middle of a SUM range, and open the file in an app that quietly drops the data validation. The failure mode that costs you is not a workbook that breaks visibly — it is one where the total is wrong and still looks right, because a SUM range silently stopped covering the last two lines. Nothing in the file will tell you, and neither will the customer.
3. There is no total across orders
The big one. Each workbook totals its own order beautifully. What you need on a Friday is "how many sourdough loaves across all forty orders" — which means opening forty files and copying rows into a forty-first. Every hour you have spent consolidating order spreadsheets was this problem.
4. No caps, no sold-out state
A spreadsheet cannot know that eleven other people already ordered the last of something. Conditional formatting warns about a per-line quantity; nothing in Excel enforces a limit across separate copies of a file. Overselling is not a risk here, it is the default.
5. Nothing tells you an order arrived
Excel has no submit event, so nothing can notify you. Orders land as email attachments among everything else, or as silent edits to a OneDrive file you have to remember to open. No unread state, no way to tell at a glance what is new. Microsoft's own documented answer to "tell me when an order arrives" is Forms plus Power Automate plus Excel-as-a-database — which is to say, not Excel on its own.
6. It is genuinely bad on a phone
Most customers open the file on a phone. The Excel mobile app renders it, and then they pinch, scroll sideways, tap the wrong cell, and give up. Against a web page that scrolls one direction, the completion rate is not close.
The split that fixes most of it
You do not have to abandon the spreadsheet. The version of this that works is a split of responsibilities:
- Collection happens on a link. A web page, on a phone, with the products and prices already on it, that people can complete in ninety seconds and cannot break.
- Analysis happens in Excel. Export the orders to CSV, open it in Excel, and do the pivot tables, the supplier order, the margin maths, and the invoicing where those things belong.
The arithmetic stays somewhere you trust, and the part that was never Excel's job — being a form forty strangers fill in correctly on their phones — moves somewhere built for it. For how a free form tool handles the collection half, the Google Forms walkthrough covers the build and its own gaps. QuickOrderForm does that job with prices, caps, and totals built in, and exports to CSV in one click so the spreadsheet half of your workflow does not change at all.
