Home > Formulas > VLOOKUP > Order Form 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. Watch the video, and follow the written steps. Get the free workbook to see how this Microsoft Excel order form works. Author: Debra Dalgleish

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 an Excel spreadsheet.

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.

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

Create the Product List

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

Note: The sample file has a short product list, but you could add all the items from your sales catalog.

Type the List

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

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

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

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 key

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)

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  

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.

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.

Note: The sample file has a single row for the grand total. If needed, you could add other line for subtotals, shipping fees, taxes, etc.

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.

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.

Format the Order Form

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

1. Format cells with green 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

Tip: You could add a company logo at the top of your order form, to personalize it.

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.

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.
• Later, you can add other columns, if needed, such as the company's name, or customer code.
3. In the following rows, starting in cell A2, enter contact information for a few of your customers.
4. Make the heading cells bold font

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

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 key

Create Customer Drop Down 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 buyer name from the drop down list in cell B5, and the address information should appear in cells B6 and B7.

Billing Address (Optional)

To add an enhancement to the completed Order Form, follow the steps below, to add a Bill To section, to the right of the Ship To section. The section will have a check box, that runs a small macro.

• To use the same address, click the check box, to add a check mark. The macro copies the shipping address to the Bill To section.
• To clear the billing address, click the check box again, to remove the check mark.

NOTE: This optional feature and the macro are in the Billing Address sample file. The completed Order Form does not contain any macros.

Save the File

The Bill To section uses macros, so follow these steps to save the workbook as macro-enabled.

• At the top of Excel, click the File tab
• At the left, click Save As
• At the top right, in the drop down list, choose Excel Macro-Enabled Workbook (*.xlsm) as the file type
  • NOTE: You could choose Excel Binary Workbook (*.xlsb) if you prefer
• (Optional) Enter a different name, and click Browse to choose a different folder for the file
• Click the Save button.
  • 

Add the Bill To Section

Here are the steps for adding a Bill To section at the top of the order form.

• Select the Ship To cells, B5:E7, and change the border to No Border
• Select cells B5:C7, and change the border to Outside Borders
• Select cells D5:E7, and change the border to Outside Borders
• In cell D4, type Bill to:, and format as Italic

Add the Check Box

• Click the Developer tab on the Excel Ribbon. If it isn't visible, follow the steps here.
• Click the Insert command arrow, and in the Form Controls section, click on the Check Box tool
  • 
• Click on cell E4, to add a check box
  • 
• If necessary, point to the check box border, and drag it into the correct position, just to the right of the "Bill To" heading
• Right-click on the check box, and click on Edit Text
• Delete the default text, and type: Same as Ship To
  • 

Link the Check Box

• Add a new sheet to the workbook, and name the sheet, Admin
• Select cell A1, and change its border to Outside Borders
• Change its fill colour to grey, to show that the cell should not be manually changed
• In cell B1, type a label: Same as Ship To
• 
• Go back to the Order Form sheet, right-click on the check box, and click Format Control
• On the Control tab, click in the Cell Link box
• In the workbook, click the Admin tab, then click cell A1
• 
• Click OK, to close the Format Object dialog box.

Test the Link

• Click on the worksheet, away from the check box, to unselect the check box
• Click the check box, to add a check mark, then go to the Admin sheet.
  • Cell A1 should show TRUE, if the check box has a check mark.
  • If the check box is cleared, cell A1 will show FALSE
• 

Name the Cell Ranges

Next, you'll create 3 named ranges -- for the Ship To and Bill To cells, and the linked cell on the Admin sheet. These names will be used in the check box macro.

• On the Order Form sheet, select cells B5:C7
• Click in the Name box, to the left of the Formula Bar
• Type a one-word name for that range -- ShipTo -- and press the Enter key
  • 
• On the Order Form sheet, select cells D5:E7
• Click in the Name box, to the left of the Formula Bar
• Type a one-word name for that range -- BillTo -- and press the Enter key
• Next, go to the Admin sheet, and select cell A1
• Click in the Name Box, and type the name, BillLink, then press Enter

NOTE: To see those names' definitions, go to the Formulas tab on the Excel Ribbon, and click the Name Manager command

Add the Macro

• On the Order Form sheet, right-click the check box, and click Assign Macro
• Click the New button, and the Visual Basic Editor window will open, showing a code module
• There is an empty macro procedure on that code module, with the name of your check box.
  • 
• Delete that procedure (3 lines -- from Sub, to End Sub)
• Next, copy the Bill To Macro code below
• Paste the copied code into the code module, where you just deleted the old code.
• Close the Visual Basic Editor window, to return to Excel

Bill To Macro Code

Here's how the "Bill To" macro works:

• If cell A1 on the Admin sheet (BillLink) contains TRUE,
  • copy shipping address (ShipTo) to the billing section (BillTo)
• If the cell contains FALSE,
  • clear the contents of the billing section.

Sub ChangeBillAddress()
Dim wsDE As Worksheet
Dim wsA As Worksheet
Dim rngBill As Range
Dim rngShip As Range
Dim rngLink As Range

Set wsA = Sheets("Admin")
Set wsDE = Sheets("Order Form")
Set rngBill = wsDE.Range("BillTo")
Set rngShip = wsDE.Range("ShipTo")
Set rngLink = wsA.Range("BillLink")

If rngLink = True Then
  rngBill.Value = rngShip.Value
Else
  rngBill.ClearContents
End If
End Sub

Assign the New Macro

Finally, follow these steps, to assign the new macro to the check box:

• On the Order Form sheet, right-click the check box, and click Assign Macro
• In the list of macros, click on ChangeBillAddress
• Click the OK button, to assign that macro to the check box.
• 

Test the Macro

• Before you test the macro, save your workbook, just in case something goes wrong
• Click on the Order Form sheet, away from the check box, to unselect the check box
• Click the check box, to add a check mark, and the shipping address should be copied to the billing section.
• Click the check box, to remove the check mark, and the billing section contents should be cleared.

Final Adjustments

Here are a few final adjustments you might need to make, to complete the billing section.

• Widen the columns, if necessary, to make room for the address lines.
• Change the cell formatting, so the billing address cells are left aligned, and in the correct font size.
• Move the check box, so it is just to the right of the Bill To label

Get the Workbook

1. Start: To follow along with the Excel order form tutorial, get 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, get the zipped Excel Order Form Completed sample file. The file is in xlsx format, and does not contain any macros
3. Billing Address: To see the completed order form with the Billing Address check box, get the zipped Order Form Billing Address Check Box sample file. This fils is in xlsm format, and contains a macro that runs when the check box is clicked.

More Tutorials

Worksheet Data Entry Form

To Do List with Check Boxes

Create a Drop Down List

VLOOKUP Function

Named Ranges

Check Box, VBA