How to Create an Order Form in Excel (Step-by-Step Guide)

When a customer wants to buy from you without a storefront checkout, you need a simple document that captures what they ordered, how much it costs, and who is buying. That document is an order form, and a spreadsheet is the fastest way to build one. In this guide, you will learn how to create an order form in Excel step by step — from laying out the columns to adding drop-down lists, automatic totals, and saving it as a reusable template.

How to Create an Order Form in Excel (Step-by-Step Guide)

What Is an Order Form and What Should It Include?

An order form is a structured document a buyer fills in to request products or services. For a small business, it replaces back-and-forth emails with one standardized record: the buyer's details, the items ordered, quantities, prices, and the amount due.

A practical order form usually contains three groups of fields:

1. Customer information — name, phone or email, and delivery address.

2. Order details — item name or SKU, quantity, and unit price, one row per product.

3. Financial summary — subtotal, discounts or tax if applicable, and the total due.

Keep only the fields you actually use. Every extra field adds friction for the customer and cleanup work for you. Once the field list is clear, building the form in a spreadsheet takes minutes.

Order form essential fields overview

Use Word, Excel, and PPT for FREE

Free Download

How to Create an Order Form in Excel: Main Steps

This is the core workflow for building an order form in Excel. The same steps work in WPS Spreadsheet and Google Sheets, because the layout and formulas are identical.

1. Set up the header area. In rows 1–4, type your business name, the words Order Form, an order number, and the date. Merge a few cells above the table for a clean title bar.

2. Create the item table. In row 6, type the column headers: Item, Qty, Unit Price, Line Total. Leave 10–15 empty rows below for products.

3. Add the customer fields. Above or beside the table, add labeled cells for Customer Name, Email, Phone, and Delivery Address.

4. Add the summary rows. Below the table, add Subtotal, Tax, and Total Due labels in the last two columns.

5. Format for printing. Select the table, add borders, bold the headers, and set the print area so the form fits one page.

At this point the skeleton of your Excel order form is done. The next two sections make it easier to fill in and harder to get wrong.

Building order form columns in Excel

Add a Drop-Down List and Data Validation to Your Order Form

Manual entry is where order forms go wrong — misspelled product names make totals and reports unreliable. Data validation fixes this by restricting what can be typed into a cell.

1. List your products. On a second sheet named Products, type each product name and its current price in two columns.

2. Select the Item column in your order form where customers choose products.

3. Open data validation. Go to the Data tab and click Data Validation.

4. Choose List in the Allow box, then point the Source at your product names on the Products sheet, or type them separated by commas.

5. Add quantity rules. For the Qty column, set a whole number between 1 and 999 so negative or text entries are rejected.

Now the person filling in the form picks products from a drop-down list instead of typing them, which keeps every order consistent. If you later want to clean up or edit these lists, see this guide on how to remove a drop-down list in Excel.

Use Word, Excel, and PPT for FREE

Free Download

Auto-Calculate Totals in Your Order Form

The biggest advantage of a spreadsheet order form is that math happens automatically. You only need two formulas.

1. Calculate each line. In the first Line Total cell, enter =Qty*Unit Price (for example =B7*C7), then drag the fill handle down for all item rows.

2. Sum the subtotal. In the Subtotal cell, enter =SUM(D7:D20), covering the whole Line Total column.

3. Add tax if needed. In the Tax cell, enter =Subtotal*8.5% — replace the rate with your local rate.

4. Compute the total due. In the Total Due cell, enter =Subtotal+Tax.

To hide zeros on empty rows, wrap the line formula as =IF(B7="","",B7*C7), so the form looks clean before any products are added. Prices update themselves the moment a customer changes a quantity, and the same approach powers most free invoice templates you will find online.

Auto calculate order total formula

Turn Your Order Form into a Reusable Template

Once the form works, save it as a template so every new order starts from a blank, protected copy instead of a used one.

1. Clear the sample data. Delete the item rows you typed while testing, but keep the headers, formulas, and drop-down lists.

2. Save as a template file. Go to File > Save As and choose the Excel template format, or simply save a master copy named Order-Form-Master and duplicate it for each new order.

3. Lock the formulas. Select the cells customers fill in, open Format Cells > Protection and uncheck Locked, then protect the sheet. Editing is now limited to the input cells.

4. Keep a product sheet in sync. Update prices on the Products sheet rather than in the form itself, so the template stays accurate.

With a template in place, creating a new order takes under a minute: duplicate the file, fill the drop-downs, and the totals calculate themselves.

Saving order form as reusable template

Use Word, Excel, and PPT for FREE

Free Download

The Best Free Alternative: WPS Office for Order Forms

You do not need a paid Microsoft 365 subscription to do any of the above. WPS Office is a free office suite whose Spreadsheet app opens and edits .xlsx files with full support for the features used in this guide — drop-down lists, data validation, SUM formulas, cell protection, and PDF export.

The workflow is identical: set up the header, build the item table, add validation, and let formulas handle the totals. WPS also includes thousands of ready-made spreadsheet templates, including sales order forms, so you can start from a designed layout instead of a blank grid. If you already received Excel order forms from customers, WPS opens them directly with formatting intact.

WPS Spreadsheet interface

FAQs

1. Can I create an order form in Excel for free?

Yes. Every feature in this guide — tables, data validation, formulas, and templates — works in free office suites such as WPS Office, so you never need a paid subscription to build an order form in Excel.

2. How do I add more products to my order form later?

Add new rows inside the item table and extend the drop-down Source range or the product list on the Products sheet. Keep the SUM range wider than your current rows so new items are counted automatically.

3. Can customers fill in an Excel order form digitally?

Yes. Protect the sheet so only input cells are editable, then send the .xlsx file by email. Customers fill it in any spreadsheet app and send it back, or you can export it to PDF for printing.

4. How do I prevent formula errors in my order form?

Use data validation to restrict quantities to whole numbers, wrap line totals in an IF formula that checks for empty rows, and protect the formula cells so they cannot be overwritten by accident.

5. What is the difference between an order form and an invoice?

An order form is filled in by the buyer before payment to request goods, while an invoice is issued by the seller after the order to request payment. Many businesses generate the invoice directly from the completed order form.

Free Office Download

   

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

100% secure

Algirdas Jasaitis