Make a Combo Box

In Excel, a combo box is a sort of drop-down list from which you can choose a value. The values that are offered in a combo box are populated from a range of cells in an Excel file. These values can be in the same file, or in another file. Say you have the list of names shown below in Column B to use in a combo box.

 

 

To create a combo box in cell D2 and populate it with names from Column B, follow these steps:

1. Click on the cell where you want to insert a combo box, and in the Ribbon, go to Developer > Insert > Combo Box (Form Control).

 

 

1. Drag the cursor (a little cross) and drop it to make a combo box.

 

 

As a result, the combo box is created in cell D2.

 

 

1. Now link the combo box to the range containing the list of names to populate it. Right-click the combo box and choose Format Control.

 

 

1. In the Format Object window, go to the Control tab and click on the arrow next to the Input range box in order to select cells.

 

 

1. Select the range with values for the combo box (B2:B10) and press ENTER on the keyboard.

 

 

1. Now you’re back in the Format Object window. Click on the arrow next to the Cell link box.

 

 

1. Select the cell where the selected value of the combo box appears (D3) and press ENTER on the keyboard.

 

 

Now, clicking on the combo box lets you select from all values from the list.

 

Select a Value in a Combo Box

Now select a value in the combo box (for example, Jennifer). As you can see below, cell D3 now has the value 4, which is the position of the selected value in the input range (B2:B10).

 

 

To get the selected value in the combo box in a cell, use the INDEX Function. This function returns the value from a given position in a range. Here, to find the 4th value in the range B2:B10, enter the formula in cell D4:

=INDEX(B2:B10,D3)

 

 

Now the selected value in the combo box (Jennifer) is also in cell D4, and you can reference it elsewhere in the file.