How To Add A ComboBox In Excel.
No Coding Needed!
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
ComboBoxes are a great tool for enhancing user interaction with your spreadsheets.
They are drop-down list type elements that enable the user to choose among selections in a list.
Right out of the box, Excel offers a couple of options for creating ComboBoxes.
They do not require you to have any special coding knowledge.
Let’s create one!
What Is a ComboBox?
We already stated that a ComboBox is a drop-down list element. We also said that they enhance the user experience with spreadsheets.
On the administrative side, creating a ComboBox allows for more control over user input. This can be practical in cases where text input is not optimal.
In Excel, we find two options for creating a ComboBox. There is one option called Form Control while the other is ActiveX Control.
Where to get started
The first thing you will need to do is ensure the Developer tab is visible in the Excel Ribbon.
If you have already enabled the Developer tab in your version of Excel, you should be able to find it next to the View tab.
If you don’t see the Developer tab, you will need to walk through the following steps.
Go to the File tab
Click on Options
When the Excel Options dialog box opens, click on Customize Ribbon.
In the column on the right under Customize the Ribbon, make sure to select the checkbox for Developer.
If you do not see Developer in that column, select All Tabs from the Choose commands from drop down. This will be at the top of the left column.
Then click on Developer to highlight and click on the Add button between the columns. Again, make sure to select the checkbox for Developer once it shows up in the right column.
Now click OK and you should find the Developer tab in your Ribbon.
Create Form Control ComboBox
Now that we have set Excel up to create a ComboBox, let’s create a simple list to use.
Next, go to the Controls group of the Developer tab and click on the arrow under Insert.
Now you should see both Form Control and ActiveX Control elements.
Let’s select from Form Controls.
Once you click on ComboBox, you can now drag your mouse and draw the ComboBox into your worksheet.
The next step is to right click on our new ComboBox element and select Format Control.
Once the Format Object dialog box opens, go to the Control tab.
Then click on the button to the right of the Input range text box to select the range of values for our list.
We can also change the number of drop down lines to 6.
Now click OK. Now you should be able to click on the down arrow in your ComboBox to see your list.
Create an ActiveX Control ComboBox
You cannot make changes to the font of a Form Control ComboBox. However, an ActiveX Control ComboBox does allow for this.
When working with ActiveX Controls, our procedure for connecting it to a list is a bit different. There are a couple of different ways to do this, but for the sake of brevity, we will show just one.
For this example, we will create a named range. This is easy. Simply go to the Formulas tab and click on Name Manager in the Defined Names group.
This will open the Name Manager dialog.
Click on New and name the range EyeColor with no space between the words.
Then click into the Refers to box and use your mouse to select the range B3 to B8 that includes our eye color list.
Click OK and you will now see your new range in the Name Manager list.
To create our ComboBox this time, we need to select from ActiveX Controls.
Once you place the ComboBox where you want by dragging your mouse, left click on it and select Properties.
This will open the Properties dialog for the ComboBox. Once it is open, find ListFillRange and type the name of our named range, EyeColor.
Click on the X in the upper right corner of the dialog box to close it.
Click on Design Mode in the Controls group of the Developer tab to unselect it.
Now check your ComboBox. It should list all the values in the named range we created.
These are the two methods for creating ComboBoxes in your Excel spreadsheets. Depending on your needs, you can choose one or the other.
You can do some things with an ActiveX Control ComboBox that you cannot with a Form Control ComboBox. This includes changing font, size, and colors.
Conclusion
These are the two methods for created ComboBoxes in your spreadsheets. These are a great way to create some user interactivity.
Furthermore, much like a data validation drop-down, ComboBoxes can control user input.
With some extra know-how, ComboBoxes can lead to some very powerful features.
Learning these two methods of creating ComboBoxes gives you more options. More options is what you need when you want to customize your spreadsheets.