How to Use ComboBox in Excel for Interactive Worksheets

Do you need to design Excel worksheets that actively interact with the user? A ComboBox provides you with the tools to make your Excel worksheets more dynamic by reacting to the changes and creating outputs based on the user’s selection.

What Is a ComboBox in Excel?

In Excel, a ComboBox is a widget that makes the workspace more interactive by letting users choose a value from a predefined list. ComboBox combines a text box with a list box to create a drop-down list. 

With a ComboBox, you can create worksheets that perform differently based on the value selected by the user. This makes Excel even more versatile and useful for a wide range of applications.

Let’s get to know this helpful Excel feature more.

When to Use ComboBox in Excel

Use a ComboBox on the following occasions:

• Give users the means to select a value from a predefined list of choices.
• Hide list items on the worksheet by default.
• Display values retrieved from a predefined list, residing in the same worksheet, a worksheet in the same workbook, or even from another workbook.

Types of ComboBoxes 

Before adding a ComboBox, you should know the different types of ComboBoxes to understand which one is a better option for your needs:

There are two types of ComboBoxes in Excel with different properties and methods:

• Form Control: It is a simple ComboBox that you can add to your spreadsheet. It doesn’t require any coding.
• ActiveX Control: It is a more complicated type of ComboBox with more options for modification and customization, so it would be better if you knew advanced Excel or VBA coding.

How are Form Control and ActiveX Control ComboBoxes Different

Boost your productivity by getting a free consultation from Excel experts, and discover tailored solutions to optimize your data management and analysis.

How to Create a Form Control ComboBox?

Generally, most users prefer to add form control ComboBoxes to their spreadsheet because, in most cases, this type of ComboBox is enough for what they want. Furthermore, it is simple to create, and the users don’t need specific knowledge of advanced Excel and VBA programming.

To add Form Control ComboBox, you should follow these steps:

1. Select a column that you can hide on the worksheet and create a list of items that you want to display in your ComboBox. 

1. Go to the Developer tab. Click Controls group > Insert and select the ComboBox option from the Form Controls items. Then click on the Excel sheet where you want to put the ComboBox. 

In our case, we want to create a ComboBox for the Gender field. 

2. Adjust the position and size of the ComboBox to match the design and layout of your worksheet.

1. Right-click on the ComboBox and select Format Control. The Format Object dialogue box will open. 

Input range: Activate the Input range box then select the list you created in the first step. This is the list of items you want to show in your ComboBox.

Cell link: If you need to do further calculations on the value selected from this ComboBox, you should link it to a cell. To do this, activate the Cell link box and then click the cell you want to link to your ComboBox. You can see the absolute address of the linked cell is added to the Cell link box.

In practice, when you select an item from this ComboBox, the index of the selected item will appear in the linked cell. For example, in our case, if ‘Male’ is selected from the ComboBox, the value of cell D6 will be 2 as ‘Male’ is the 2nd item in the input range.

Drop-down lines: Enter the number of items you want to see in the ComboBox as you click on it. If the number you select for this field is smaller than the number of Input range, the list will scroll.

Click OK, and you will have a functioning ComboBox.

As you can see, the value of D6 will change based on the item you select from ComboBox.

Enhance your software capabilities with our customizable Add-In Solutions, seamlessly integrating new features to meet your business needs.

How to Create an ActiveX ComboBox?

ActiveX ComboBox is appealing for applications where we need more control and freedom for advanced customization. This can be done through either advanced Excel commands or by programming in VBA for even more advanced configuration. In this blog, we will take the former approach to customize our ComboBox.

To add ActiveX ComboBox, you should follow these steps:

1. Select a column that you can hide on the worksheet and create a list of items that you want to display in your ComboBox. 

To create an ActiveX ComboBox, it’s better to define a named range from this list. There are multiple ways to create a named range in Excel. To do it in the simplest way, select the range and type a suitable name in the Name Box.

1. Go to the Developer tab. Click Controls group > Insert and select ComboBox from the ActiveX Controls items. Then click on the Excel sheet where you want to put the ComboBox. 

In our case, we want to create a ComboBox for the Gender field. 

2. Adjust the position and size of the ComboBox to match the design and layout of your worksheet.

1. Go to the Developer tab > Control and make sure that Design Mode is selected. Now right-click the ComboBox, then select Properties. The Properties window will open.

Many options are available on the Properties window, play around with these properties to see how you can modify the ComboBox.

1. Find ListFillRange from the Properties options. Write the name of the named range you created in the first step. If you have not created a named range in the first step, enter the input range, e.g. &A&1:&A&3.
2. If you need to do further calculations on the value selected from this ComboBox, you should link it to a cell. To do this, find LinkedCell from the Properties options and add the address of the cell you want to link to this ComboBox.

In practice, when you select an item from this ComboBox, the selected item will appear in the linked cell. For example, in our case, if ‘Female’ is selected from the ComboBox, the value of cell D6 will be ‘Female’.

1. Close the Properties window. 
2. On the Controls section of the Developer tab unselect the Design Mode.

Now you have a functioning ActiveX ComboBox ready to be used. You can see that as you choose an item from the ComboBox, the selected item will appear in D6. 

Unlock valuable insights with our Data Visualization and Data Analytics Services, transforming complex data into clear, actionable strategies for informed decision-making.

How to Create ComboBox with Automatic Input Range Adjustment

So far, we have created ComboBoxes with static input ranges. That is, if we modify the input range on the fly, the ComboBox doesn’t reflect these changes automatically, we have to configure the ComboBox once again to account for this change. In this section, we are going to address this shortcoming in an easy and straightforward way.

Create Form Control ComboBox with Automatic Input Range Adjustment

To add a Form Control ComboBox with automatic range adjustment, you should follow these steps:

1- Select a column that you can hide on the worksheet and create a list of items that you want to display in your ComboBox.

The trick to automatic range adjustment is defining an Excel Table from this list. There are multiple ways to create a table in Excel. To do it in the simplest way, select the range and press cntl + t. The Create Table dialog box opens, make sure the selected range conforms with what you like. Press OK. You can see Excel creates a table with a default name.

2- Go to the Developer tab. Click Controls group > Insert and select ComboBox from the Form Controls items. Then click on the Excel sheet where you want to put the ComboBox. 

In our case, we want to create a ComboBox for the Gender field. 

3- Adjust the position and size of the ComboBox to match the design and layout of your worksheet.

1. Right-click on the ComboBox and select Format Control. The Format Object dialogue box will open. 

Input range: Activate the Input range box then select the range of the table you created in the first step. This is the list of items you want to show in your ComboBox.

Cell link: If you need to do further calculations on the value selected from this ComboBox, you should link it to a cell. To do this, activate the Cell link box and then click the cell you want to link to your ComboBox. You can see the absolute address of the linked cell is added to the Cell link box.

In practice, when you select an item from this ComboBox, the index of the selected item will appear in the linked cell. For example, in our case, if ‘Male’ is selected from the ComboBox, the value of cell D6 will be 2 as ‘Male’ is the 2nd item in the input range.

Drop-down lines: Enter the number of items you want to see in the ComboBox as you click on it. If the number you select for this field is smaller than the number of Input range, the list will scroll.

6. Click OK and you will have a functioning ComboBox. As you can see, the value of D6 will change based on the item you select from ComboBox.

7. Add more items to the input range and you will see that the ComboBox will update automatically without any need for further manual configuration.

Create Form Control ComboBox with Automatic Input Range Adjustment

To add an ActiveX ComboBox with automatic range adjustment, you should follow these steps:

1. Select a column that you can hide on the worksheet and create a list of items that you want to display in your ComboBox.

The trick to automatic range adjustment is to define an Excel Table from this list. There are multiple ways to create a table in Excel. To do it in the simplest way, select the range and press cntl + t. The Create Table dialog box opens, make sure the selected range is conforming with what you like. Press OK, you can see Excel creates a table with a default name.

1. Go to the Developer tab. Click Controls group > Insert and select ComboBox from the ActiveX Controls items. Then click on the Excel sheet where you want to put the ComboBox. 

In our case, we want to create a ComboBox for the Gender field. 

2. Adjust the position and size of the ComboBox to match the design and layout of your worksheet.

4. Go to the Developer tab > Control and make sure that Design Mode is selected. Now right-click the ComboBox, then select Properties. The Properties window will open.

5. Many options are available on the Properties window, play around with these properties to see how you can modify the ComboBox.

6. Find ListFillRange from the Properties options. Enter the input range of the table you created in the first step. In our case, we enter $A$2:$A$4.

7. If you need to do further calculations on the value selected from this ComboBox, you should link it to a cell. To do this, find LinkedCell from the Properties options and add the address of the cell you want to link to this ComboBox.

In practice, when you select an item from this ComboBox, the selected item will appear in the linked cell. For example, in our case, if ‘Female’ is selected from the ComboBox, the value of cell D6 will be ‘Female’.

8. Close the Properties window. 

9. On the Controls section of the Developer tab unselect the Design Mode.

Now you have a functioning ActiveX ComboBox ready to be used. You can see that as you choose an item from the ComboBox, the selected item will appear in D6.

10. Add more items to the input range and you will see that the ComboBox will update automatically without any need for further manipulation.

Bottom Line

Creating a ComboBox in Excel can greatly enhance the functionality of your worksheets. By following the simple steps outlined in this guide, you can easily create a pre-set list of choices. This feature is particularly useful for tasks requiring active interaction with the user of the Excel file. With the ability to develop Excel files with conditional functionality, you can create more versatile solutions for a wide range of applications. Furthermore, knowing how to create a ComboBox in Excel is one of the Excel skills employees should possess.

FAQ