How to use Combo Box (Form Control)

Combo Box is one of the ways to create drop down menu in Excel. More about drop down menus is on https://excel-example.com/other-tutorial/how-to-create-drop-down-menu-in-excel-worksheet.
In this example we create a simple Order Form that calculates final prize. Combo boxes will be used for choosing goods. Here is simple list that will be source for Order Form.

Solution

Let’s create a new order form on a new sheet  (Sheet2). For simplicity, it contains only 3 items to order. This is a blank template into which we’ll add combo boxes and formulas.

Step 1 / add combo box

Go to the Developer menu and select Combo box from “Form Controls”.

Draw Combo box. Right-click on it and choose “Format Control…”

 

In Format Control, we have to fill Input range and Cell link. Input range is source for items in combo box, so it will be list of goods. Notice that the first row of the list is empty. Cell link is the cell that will be indicate which item in the combo box was chosen. You can create some auxiliary table for this. I place it right next to the Order Form, but you can place it wherever you want (for example: to the other sheet).

 

Now choose one of the item from Combo Box you have just created. For example item “Swim Cap”. It is the third in the goods list (including first blank row). Therefore the cell link shows number 3. In the next step we will use this number to find the price of the product. Before that we will add 2 more Combo Boxes on the Order Form.

 

You can add them using previous procedure or you can copy them. Anyway, you have to assign each Combo Box to different Cell link. In this, I will use cell F4, F5 and F6.

 

Step 2 / formulas

How to find the right price for the selected item? We can use INDEX function (from Lookup & Reference menu). Formula for the first price:

 

Argument Array is range B2:B8, the prices including first blank cell. Argument Row_num is address of the Cell link. Argument Column_num is 1, because we are searching in the first column of argument Array. Now just copy this formula to other items.

Formula for the Total Price is easy:

 

And finally Grand Total formula:

Step 3 / hide auxiliary table

It is better to hide auxiliary table from the users. If you put it like me, just hide the column F. Right click on the letter F on the top of the column and select Hide.