How to Make / Create a Combo Box in Excel

This tutorial demonstrates how to make a combo box in Excel.

 

combo box final data

 

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.

 

combo box initial data list

 

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

 

insert combo box form control

 

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

 

insert combo box form control 2

 

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

 

insert combo box form control 3

 

  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.

 

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

 

combo box format control input range

 

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

 

combo box format control input range 2

 

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

 

combo box format control cell link

 

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

 

combo box format control cell link 2

 

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

 

combo box final

 

Note: You can also populate and use a combo box with VBA code.

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

 

combo box cell link

 

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)

 

combo box link cell index

 

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