How to Use Excel Combo Box (A Complete Guideline)

To add the combo box, we need to enter the Developer tab. Usually, the Developer tool is not available in the Excel Ribbon options.

Steps:

Go to File, then to Options. The Excel Options window appears.
Choose the Customize Ribbon option on the left.
Go to the Main Tabs from the Customize the Ribbon column on the right.
Find the Developer option on the list.
Check the box for the Developer option.
Press OK.

Enable developer mode in Excel

Return to the sheet.

We can see the Developer tab is available now.

Click on the Developer tab.
Choose the Insert option from the Controls group.
This screenshot shows two combo boxes of two different types.

Multiple combo box options of Excel

Select any of the marked combo boxes.
Place the cursor in the sheet of the desired location and click to add the combo box.

Place an Excel combo box

How to Add 2 Types of Excel Combo Box

There are two types of combo boxes. Those are-

Form Controls Combo Box and
ActiveX Controls Combo Box.

Case 1 – How to Add the Form Control Combo Box

We have a dataset of the names of days of the week. We will add a Combo Box that will select a day from the drop-down list and show the selection number. We’ll add a cell that will show the name of the selected day.

Steps:

Select the combo box from the Form Controls section.

Add Form Control Combo Box in Excel

Place the combo box in the desired location on the sheet.

Right-click on the new box.
Choose the Format Control option from the Context Menu.

The Format Objects window appears. Choose the Control tab.

Customize the combo box

In the Input range, select the range that contains values of the drop-down.
The Cell link box refers to a cell that will show the serial number of the selection.
The Drop down lines indicate how many options will appear in the drop-down.
Input the values as shown or according to your sheet and press OK.
Click on the down arrow of the drop-down.

Select option from the combo box drop-dwon

Choose an option from the drop-down list.

We can see that 2 is shown on cell D5. This cell is linked with the drop-down list.

Put the following formula on cell E5.

=INDEX(B5:B11,D5)

Press the Enter button to get the result.

Case 2 – How to Create an ActiveX Control Combo Box

We will just show the result using the combo box in this section at cell D5.

Steps:

Click on the Formulas tab and select the Define Name option.

Insert a Named Range in Excel

The New Name window will appear.
Input the name of the range in the Name box. We put “Day.”
Choose the range in the Refers to box from the Excel Sheet. This is the list of days of the weel.
Press OK.

Customize a Named Range

Insert a combo box from the ActiveX Controls section.

Create ActiveX Control Combo Box in Excel

Place that combo box next to cell D5.

Right-click on the combo box.
Choose the Properties option from the Context Menu.

The Properties window appears. Find the LinkedCell and ListFillRange options from the Properties window.

Link the Combo box with Cell and Named Range

Insert D5 as the linked cell and Day as the listed range.
Disable the Design Mode from the Controls group in the Developer tab.
Click on the down arrow of the combo box.

Choose any of the options.

We can see that day is showing at cell D5.

Similar Readings

Use Excel VBA to Make a Dynamic and Dependent Combo Box

We have two columns: Days and Months. We will introduce two combo boxes here. The first box will allow you to pick the category, months or days, and the second will show a list of options.

Steps:

Go to the Developer tab.
Click on the Visual Basic option from the Code group.

Enter to Excel VBA

The VBA window will appear.
Choose the UserForm option from the Insert tab.

Create a UserForm in Excel

We can see a UserForm appears with a Toolbox.

RIght-click on the UserForm.
Choose the Properties option from the Context Menu.

Go to Caption and input a name here. This is the title of the UserForm.

Customize the UserForm

Add a Label and a ComboBox from the Toolbox.

Copy those boxes with Ctrl + C and paste them a bit down by pressing Ctrl + V.

Right-click on the first Label.
Choose the Properties option from the Context Menu.

Customize the Labels of the Excel

Change the name, font color, size, and other formatting options you want from this Properties window.

After changing the attributes, our Userform will look like this.
Press the Run option from the main tab.

Here’s the dialog pop-up you’ll get.

Final look of the Combo box

Double-click the UserForm. This opens a VBA window.
Go to the right side and click on the arrow.
Choose Activate to option the list.

A code will be added to the window to activate the UserForm.

Remove the code of UserForm code from the VBA window.
Here’s the VBA code that needs to be in the window.

Private Sub UserForm_Activate()
Dim D_Sheet As Worksheet
Set D_Sheet = ThisWorkbook.Sheets("Dependent & Dynamic Combo Box")
Dim N As Integer
Me.ComboBox1.Clear
For N = 1 To Application.WorksheetFunction.CountA(D_Sheet.Range("1:1"))
    Me.ComboBox1.AddItem D_Sheet.Cells(1, N).Value
Next N
End Sub

Excel VBA to Make a Dynamic and Dependent Combo Box

When we click on the down arrow of the Category combo box, we see the options.
Click on the down arrow of the Options combo box. It’s empty and needs to be programmed.

Use of the combo box in Excel

Double-click on the ComboBox1.

Copy this VBA code and paste it into the window.

Private Sub ComboBox1_Change()
Dim D_Sheet As Worksheet
Set D_Sheet = ThisWorkbook.Sheets("Dependent & Dynamic Combo Box")
Dim N, M As Integer
M = Application.WorksheetFunction.Match(Me.ComboBox1.Value, D_Sheet.Range("1:1"), 0)
Me.ComboBox2.Clear
For N = 2 To Application.WorksheetFunction.CountA(D_Sheet.Cells(1, M).EntireColumn)
    Me.ComboBox2.AddItem D_Sheet.Cells(N, M).Value
Next N
End Sub

Run the VBA code by pressing the F5 button.

We can see the Options combo box is working now. It means the Options combo box is dependent.
Add another column to the dataset like the following.

Add new column in the dataset