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.

named range for product lookup

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 B5:B12
  2. Choose 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(B5="","",
  • 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(B5,ProductLookup,2,FALSE)
    • Click this link to learn more about VLOOKUP

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

  1. On the Order Form worksheet, select cells C5:C12
  2. Type the formula:
      =IF(B5="","",VLOOKUP(B5,ProductLookup,2,FALSE))
  3. Press Ctrl + Enter, to enter the formula in all the selected cells. go to top

VLookup Formula

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 E5:E12
  2. Type the formula:      =IF(C5="","",C5*D5)
  3. Press Ctrl + Enter, to enter the formula in all the selected cells. go to top

Calculate the Row Total

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 E14
  2. Type the formula:      =SUM(E5:E12)
  3. Press Enter, to complete the formula. go to top

Total the Order

Format the Order Form

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

  1. On the Order Form worksheet, add column headings in row 4, as shown below.
  2. Add the main heading, "Order Form", in cell B2
  3. In cell C14, type "Total"
  4. In cell E2, type the formula:       =TODAY()
  5. Select cells B4:E12, and add borders.
  6. Add fill colour to columns B and D, where data entry is required.
  7. Format the heading fonts as bold
  8. Format the date as desired. go to top

Format the Order Form

Add a Customer List

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

Add a Customer List

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, DateEntered.
  3. In the following rows, enter data for a few sample customers.
  4. To name the customer list (for the dropdown list), select cell A2
  5. Choose Insert | Name | Define
  6. Type the name: CustList
  7. In the Refers to box, type:
    =OFFSET(Customers!$A$2,0,0,COUNTA(Customers!$A:$A)-1,1)
  8. Click Add
  9. To name the Customer lookup table (for the address lookup), type the name: CustLookup
  10. In the Refers to box, type:
    =OFFSET(CustList,,,,6)
  11. Click OK

Create Customer Dropdown and Lookup

  1. On the Order Form worksheet, select rows 3:7
  2. Choose Insert | Rows
  3. In cell B4, type: Ship to:
  4. In cell B5, add a Data Validation list, with CustList as the source
  5. Select cells B6:E6, and choose Format | Cells
  6. On the Alignment tab, add a check mark to the Merge Cells box, click OK
  7. Select cells B7:E7, and press the F4 key, to repeat the previous formatting
  8. In cell B6, type the formula:
    =IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE))
  9. In cell B7, type the formula that will lookup city, province, and postal code:
    =IF(B5="","",VLOOKUP(B5,CustLookup,3,FALSE)&", " &VLOOKUP(B5,CustLookup,4,FALSE)&" " &VLOOKUP(B5,CustLookup,5,FALSE))
  10. To test the formulas, select a customer from the dropdown list in cell B5, and the address should appear in B6 and B7. go to top

select customer from drop down list

Download the Workbook

To see how the Excel order form works, download the zipped 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

 

 


Last updated: February 22, 2017 2:47 PM