How To Make A Great Form

Make Forms Keyboard-Friendly. You have no way of knowing what type of device a user will encounter your form on, but it should be equally easy to fill out anyway. Consider all the different types of keyboards that could send information to the form and adjust fields to detect and use the appropriate option. Start Here a Professional Logo Designer: how you can create a.

  • Step 2: Edit and format a form or quiz. You can add, edit, or format text, images, or videos in a form. Edit your form; Create a quiz with Google Forms; Choose where to save form responses; Step 3: Send your form for people to fill out. When you are ready, you can send your form to others and collect their responses.
  • Take advantage of these great SurveyMonkey resources, and you’ll create a survey in no time! Easy question creation. With 15 question types, including rating scales, multiple choice, and more, it’s easy to create online surveys from scratch.

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.

Create Drop Down Lists

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.

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

How To Make A Great Frozen Margarita

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 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

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 Introductionpage.

  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.

  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. 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

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

How To Make A Good Formal Letter

  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 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 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.

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
How To Make A Great Form

Name the Cell Ranges

How To Make Great Forms

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 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.

How To Make A Great Film

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.

How To Make A Good Formal Speech

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

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
  3. Billing Address: To see the completed order form with the Billing Address check box, download 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.

How To Make A Good Form In Word

More Tutorials