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.

Select any of the marked combo boxes.
Place the cursor in the sheet of the desired location and click to add the 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.

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.

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.

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.

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.

Insert a combo box from the ActiveX Controls section.

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.

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.

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

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.

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.

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.

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

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.

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.