Contextures

How to Create Excel Order Form

How to create a simple order form in Excel, with a drop down list of products. After a product is selected, formulas show the price. Get the free workbook to see how it works.

Excel Order Form

In the screen shot below, you can see the completed order form, with drop down lists to select products. The steps below explain how to build this order form.

In the download section, you can get

  • the beginning file for this tutorial, with the customer and product lists typed on the sheets
  • and a completed version of the Excel order form workbook.

completed Excel order form

Video: How to Make an Excel Order Form

See how to make an Excel Order form in this step-by-step video. The written instructions are below the video.

Video Timeline
  • 00:00 Intro
  • 01:00 3 Sheets
  • 02:26 Excel Tables
  • 03:27 Named Ranges
  • 05:34 Start the Order Form
  • 06:21 Add the Headings
  • 06:58 Product Drop Downs
  • 07:43 Price Lookup
  • 08:59 Add VLOOKUP
  • 10:09 Row Total
  • 11:26 Grand Total
  • 12:06 Add Formatting
  • 13:01 Customer Drop Down
  • 13:49 Customer Lookup
  • 15:15 Combined Lookup
  • 17:44 Final Formatting
  • 18:34 Get the Sample File

Set up the Workbook

To begin, set up two sheets in a new workbook:

  1. Open a new blank workbook
  2. Rename Sheet1 as Order Form
  3. Insert another sheet, and rename it as Products

two sheets in workbook

Create the Product List

Next, follow the steps below to create a lookup table with product names and prices. The list will be formatted as a named Excel table, and named ranges will be added.

Type the List

  1. On the Products sheet, type the list of products and prices, as shown below, starting in cell A1
  2. type the product lookup table

Format as an Excel Table

  1. Select any cell in the product list
  2. On the Excel Ribbon, click the Home tab
  3. In the Styles group, click Format as Table
  4. Click on the Table Style that you want to use
  5. format as table

  6. Check that the product table range is correct -- it should be $A$1:$B$6
  7. Add a check mark in the box for My Table Has Headers (it should be checked automatically)
  8. Click OK

format as table dialog box

The product list is now formatted as a table, with an outline, and drop down arrows in the heading row. Learn more about Excel Tables, and how they work.

Name the Product List

Next, follow these steps to name the cells in column A. To learn more about Excel names, go to the Name a Range page.

  1. Select the list of products in cells A2:A6 (do NOT include the prices in column B)
  2. Click in the Name Box, at the left of the Formula Bar
  3. Type a one-word name for the range --ProductList -- then press the Enter key

named range for product list

Name the Product Lookup Table

Next, follow these steps to name the lookup table. The heading row will not be included..

  1. Select all the product names and prices in cells A2:B6 -- do NOT include the headings.
  2. Click in the Name Box, at the left of the Formula Bar
  3. Type a one-word name for the range --ProductLookup-- then press the Enter keygo to top

named range for product lookup

Start the Order Form

To start the Order Form, follow these steps to add the headings:

  1. Type the heading "Order Form" in cell B2
  2. Format the heading font and font size
  3. Make column A quite narrow, e.g. Width 1.00 (13 pixels)
  4. In cell E2, enter this formula, to show the current date: =TODAY()
  5. Format the date cell -- this example has day-Month
  6. In cells B9 to E9, enter the column headings: Product, Price, Qty, Total
  7. Select cells B9 to E14 and apply the All Borders option from the Borders list.
  8. Reduce the height of rows 1, 3 and 8 to about 4.5 (6 pixels)

named range for product lookup

Create Product Drop Downs

The next step is to create drop down lists on the Order Form sheet, to make it easy to enter a product. There is more information on creating drop down lists on the Data Validation Introduction page.

  1. On the Order Form worksheet, select cells B10:B14
  2. On the Excel Ribbon's Data tab, click Data Validation
  3. From the Allow dropdown, choose List
  4. In the Source box, type:   =ProductList
  5. Click the OK button  go to top

data validation settings

Add the Price Lookup

Formulas in column C will return the price for each product selected in the order form. The formula combines two Excel functions -- VLOOKUP and IF.

  • The IF formula checks the product cell, and if it is empty, it returns an empty string "", so the price cell looks empty: IF(B10="","",
  • The VLOOKUP formula tries to find the selected product in the ProductLookup table.
    • If the product is found, the formula returns the price for that product, from the 2nd column in the table: VLOOKUP(B10,ProductLookup,2,FALSE)
    • Click this link to learn more about VLOOKUP

Follow these steps to add the formula in the Price column:

  1. In cell B10, select a product from the drop down list
  2. On the Order Form worksheet, select cells C10:C14
  3. Type the formula:
      =IF(B10="","",VLOOKUP(B10,ProductLookup,2,FALSE))
  4. Press Ctrl + Enter, to enter the formula in all the selected cells. go to top

VLOOKUP for product price

Calculate the Row Total

Quantity will be entered in column D, and the row total calculated in column E. The formula is wrapped with an IF formula, to prevent a zero calculation if no product has been selected in a row.

  1. On the Order Form worksheet, select cells E10:E14
  2. Type the formula:      =IF(C105="","",C10*D10)
  3. Press Ctrl + Enter, to enter the formula in all the selected cells. go to top

formula for row total price

Total the Order

The SUM function is used to total all the rows in the Order Form.

  1. On the Order Form worksheet, select cell E16
  2. Type the formula:      =SUM(E10:E14)
  3. Press Enter, to complete the formula. go to top

formula for order total price

Format the Order Form

Add cell borders and font formatting, to make the Order Form more attractive, and easy to use.

  1. Add fill colour to columns B and D, where data entry is required.
  2. Format the heading fonts as bold
  3. Format the date as desired.
  4. Add currency formatting in the Price and Total columns, and in the Order Total cell

go to top

formatted order form

Add a Customer List

If you have regular customers, you can add a worksheet with your customer names and addresses. Then, select a customer name on the order form, and have their address fill in automatically.

customer drop down

Add the Customer List

  1. Insert a new worksheet, and name it Customers
  2. On the Customers sheet, in row 1, and the headings, Name, Street, City, Prov, PostalCode.
  3. In the following rows, enter data for a few of your customers.
  4. Make the heading cells bold font

format as table

Format as an Excel Table

  1. Select any cell in the customer list
  2. On the Excel Ribbon, click the Home tab
  3. In the Styles group, click Format as Table
  4. Click on the Table Style that you want to use
  5. format as table

  6. Check that the customer table range is correct -- it should be $A$1:$E$5
  7. Add a check mark in the box for My Table Has Headers (it should be checked automatically, because of the bold heading font)
  8. Click OK

Name the Customer List

Next, follow these steps to name the cells in column A. To learn more about Excel names, go to the Name a Range page.

  1. Select the list of customers in cells A2:A5 (do NOT include the heading cell)
  2. Click in the Name Box, at the left of the Formula Bar
  3. Type a one-word name for the range --CustList -- then press the Enter key

named range for customer list

Name the Customer Lookup Table

Next, follow these steps to name the lookup table. The heading row will not be included.

  1. Select all the names and address infomation in cells A2:E5 -- do NOT include the headings.
  2. Click in the Name Box, at the left of the Formula Bar
  3. Type a one-word name for the range --CustLookup-- then press the Enter keygo to top

named range for customer lookup

Create Customer Dropdown and Lookup

Next, follow these steps to add the Ship To section at the top of the Order form.

  1. In cell B4, type: Ship to:
  2. In cell B5, add a Data Validation list, with CustList as the source
  3. In cell B6, type this formula to get the customer street address, if cell B5 is not empty:
    =IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE))
  4. In cell B7, type the following formula that will lookup the city, province, and postal code, for the selected customer name, if cell B5 is not empty. The & operator combines the results in one cell, with a comma after the city, and a space after the province:
    =IF(B5="","",VLOOKUP(B5,CustLookup,3,FALSE) & ", " & VLOOKUP(B5,CustLookup,4,FALSE) & " " & VLOOKUP(B5,CustLookup,5,FALSE))
  5. To test the formulas, select a customer from the dropdown list in cell B5, and the address information should appear in cells B6 and B7. go to top

customer name and address

Download the Workbook

  1. Start: To follow along with the Excel order form tutorial, download the zipped Excel Order Form Start sample file. It has sheets with sample products and customers, so you don't have to type them. The file is in xlsx format, and does not contain any macros
  2. Completed: To see how the completed Excel order form works, download the zipped Excel Order Form Completed sample file. The file is in xlsx format, and does not contain any macros

More Tutorials

Worksheet Data Entry Form

Create a Drop Down List

VLOOKUP Function

Named Ranges

Custom Search

 

30 Excel Functions in 30 Days

 

 

 

 

 

 

excel tools

 

 

Get weekly Excel tips from Debra

 

 


Last updated: February 5, 2019 6:50 PM