What Is A Combo Box In Excel?
Combo Box is an object which can perform the activity of holding values as a drop-down list to allow the user to select the values from the available list.
A Combo Box is like the drop-down list we create in Excel, but a drop-down list is created in a cell. A combo box is an object, and within this object, we will provide the list of items to be included in the combo box.
There are 2 types of combo box in Excel. They are:
- Form Control Combo Box: This is the basic version of combo box in excel. In this type, we cannot edit any of the properties.
- ActiveX Control Combo Box: This is the advanced version and also supports VBA macro coding. In this version, we can change the properties, which are versatile and flexible.
Combo Box in Excel is available under the Developer tab.
However, if we use the Developer tab for the first time, we may not see this in our Excel ribbon.
It is because, by default, the tab is disabled in Excel. Hence, we need to enable it first to use combo box in excel.
The following steps will guide us to enable the Developer tab in Excel.
Step 1: First, go to the File tab.
Step 2: Click on Options.
Step 3: The Excel Options window opens up. Next, click on the Customize Ribbon option.
Step 4: Then, check the Developer tab box under Main Tabs.
Step 5: Click OK. Now we will be able to see the Developer tab.
Under the Developer tab, we can see Combo Box under the Insert option as highlighted in the below image.
For example, look at the following data in Excel.
To create a combo box for these city names, we need to use the below steps.
Step 1: To begin with, click on the Developer tab and insert Combo Box in excel from Form Controls.
Step 2: Next, draw the combo box on the worksheet.
Step 3: Right-click on the check box and click on Format Control… option.
Step 4: Next, in the Format Control window, choose Control tab.
Step 5: Then, in the Input range: box, choose the cell range A2:A4.
Step 6: In the Cell link: box, we should select any of the addresses of the cell. In this example, we have given the cell link as cell B1.
Step 7: Click OK, and the Format Control window gets closed.
Now, we will see the city values in the combo box.
Step 8: Next, select any of the values from the combo box, and we will see the selected value row number in the linked cell B4.
Step 9: We have selected the city, ‘Sydney,’ and in the linked cell B2, we can see the number 2 because the city Sydney is the second item on the list.
Likewise, we can insert combo box in excel.
Table of contents
Key Takeaways
- Combo Box is like the drop-down list we create in Excel.
- There are two kinds of combo boxes i.e., Form Control Combo Box and ActiveX Combo Box.
- A dynamic drop-down list requires the named range to be created and the same named range can be used as an input for the ActiveX combo box.
- The Form Control Combo box returns the row number of the selected value whereas the ActiveX combo box returns the selected value itself.
- We can change the background color, font name, size, and color with the ActiveX combo box where Form Control offers nothing.
How To Create Combo Box Using Form Controls?
As we have discussed earlier, we have two kinds of Combo Box in excel. Let us understand the method to create combo box in excel using Form Controls.
Let’s have a look at the overview of the Format Control window.
- Size: In this tab, we can control the size of the combo box. We can change the height, width, and rotation of the combo box in excel.
- Protection: This will lock the combo box in the worksheet. To make this work, we need to first protect the worksheet.
- Properties: This helps us to play around with the positioning of the combo box. We can choose whether we want to move the combo box with or without changing the size of the cells.
- Alt Text: Alternative Text is the space to enter the description of the combo box. We have entered the reason for creating the combo box.
- Control: This is the most important tab in the window.
The options available here are:
- Input Range: We need to provide cell ranges to create a drop-down list. In our example, the fruit list is there from A2:A7.
- Cell Link: The speciality of the combo box is that when we select any value from the combo box in the linked cell, it will display which line item has been selected.
- Drop-down lines: By default, this indicates the number of items to be shown. For instance, if we have 5 items and if we put 3 in the combo box, it will display 3 items by default. To access the remaining 2, we need to scroll down.
- 3-D Shading: This will apply the 3-D shading formatting for the combo box.
For instance, let us create combo box in excel to hold the drop-down list of items that are there in the range of cells as shown in the following image.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
The steps used to create combo box in Excel are as follows:
- First, go to the Developer tab, click on the Insert option, and then, choose Combo Box from Form Controls.
- Once we click on the icon, it will ask us to draw it on our worksheet. So, we should draw wherever we want to place it.
- Once we have drawn the combo box, we need to configure it to create a drop-down list of fruits.
So, right-click on the combo box and choose Format Control… option.
- The Format Control dialogue box opens up.
- Now, for the Input range: box, choose cells from the range A2:A7.
- For the Cell link: box, we should select any of the empty cell references.
- Similarly, for the Drop down lines: box, enter 4.
- Click on OK.
We will be getting a combo box as shown in the below image.
Click on the drop-down icon to see the list of fruits.
Select any of the items from the combo box and in the linked cell, we will see some numerical number as shown in the below image.
We have chosen ‘Mango’ and in the linked cell D3, we can see the number 3 i.e., from the selected cell range A2:A7, ‘Mango’ is the 3rd item. So, the combo box returns the position of the selected item from the list.
In the Format Control window, we should change the Drop down lines: to 8.
Now we will be able to see all the values in the combo box without the scroll-down option.
Using combo box in excel, we can fetch the unit sold value with the INDEX function.
In the cell E3, apply the following INDEX function.
=INDEX(B2:B7,D3)
The combo box returns the row number for the selected item and the INDEX function will get the value from the range B2:B7 for the given row number from the combo box.
How To Create Combo Box Using ActiveX Controls?
ActiveX control offers a variety of options and is more versatile compared to Form Controls. By using ActiveX control, we can create a dynamic drop-down list, change the colors, and change many other properties of the combo box.
Click on the Developer tab and select Combo Box from ActiveX Controls.
Draw the combo box on the worksheet.
As soon we draw, we can see some sort of coding in the formula bar which was not there when we inserted the combo box from form controls.
Right-click on the combo box and click on the Properties option.
This will open the separate Properties window as shown in the following image.
Using these Properties window, we can change the properties of the combo box. Change the name of the combo box to Fruits_Combo_Box.
This name can be used to select this combo box whenever we write VBA code.
Change the background color to Highlight.
This will make the background color blue.
Next under ListFillRange, give the excel cell reference as A2:A7.
The LinkedCell property gives the cell reference of D3.
Close the Properties window. Currently, the combo box is under Design Mode. To use the combo box, we should uncheck the design mode under the Developer tab.
After we exit the design mode, we can access the combo box. Click on the drop-down list of the combo box and we should see the list of items from A2:A7.
Whenever we choose any of the items from the list, in the linked cell D3, we will see the selected value not the selected value’s row number in the list.
Create Dynamic List
However, this drop-down is not the dynamic one because we have entered the range of cells manually as A2:A7. Anything added after the cell A7 combo box won’t fetch the values.
For instance, in the following image, we have added a couple more fruits data.
In cells A8 & A9 we have added ‘Guava’ and ‘Banana’, but the combo box is not returning the newly added fruits.
We need to create a Dynamic Named Range and use that named range while configuring the combo box properties.
To begin with, go to the Formulas tab and click on Define Name option.
Next, give a name for the named range that we are creating such as ‘Fruit_List’
In the Refers to: box, enter the following formula.
=OFFSET(A$2,0,0,COUNTA($A:$A)-1)
Click OK and the dynamic list can be seen.
To test it, enter an equal sign in any of the empty cells and start typing the name that we have created.
As soon as we select the named range, we will get the entire list of values entered in column A.
To test the dynamic nature of this named range, let us add couple more items to the fruit list.
As soon as we add new fruits to the list, our named range dynamically returns the complete list of fruits.
We will use the named range in the combo box properties to make the combo box dynamic. Go to the Properties of the combo box and in the ListFillRange property, enter the named range name.
Close the Properties window and exit the Design mode.
Add few fruits to the list, and the combo box should show the list of newly added fruits dynamically.
Like this, we can create a dynamic drop-down list using ActiveX Combo Box.
Important Things To Note
- Combo Box is an object, so we can move this object anywhere in the worksheet.
- Form control combo box does not allow any of the properties to be changed, but ActiveX control allows the user to change properties.
- Only 8 line items are visible by default in Form Control combo box. However, the user can decrease or increase the visible number of lines by editing the drop-down lines box.
- Both Form Control and ActiveX combo boxes require the cell reference of the named range as in the input, and users cannot enter the values directly.
- We need to first protect the worksheet to lock Form Control combo box to the cell.
Frequently Asked Questions (FAQs)
Combo Box is available under the Developer tab. To add combo box in excel, click on the Insert option under the Developer tab. We can insert combo box in excel using Form Controls and ActiveX Controls.
To remove Form Control combo box, right-click on the combo box and then hit the Delete button.
However, to delete the ActiveX combo box, click on Design Mode under the Developer tab and right-click on the combo box. Then, delete it.
A combo box is helpful to create interactive dashboards and charts. Apart from this, combo box helps us to create a searchable drop-down list using VBA coding.
A combo box is available under the Developer tab in Excel. Click on the Insert option under the Developer tab and we have two groups i.e., Form Controls and ActiveX Controls, a combo box is available under both of these types.
After inserting combo box in Excel, we can create a list of values in it. For example, look at the following data.
Draw combo box from the Developer tab.
Right-click on the check box and click on Format Control… option.
In the Format Control window, choose the Control tab.
In the Input Range, choose the cell range from A2 to A5.
In the Cell Link, we should give any of the addresses of the cell. We have given the cell link as cell C1.
Click on OK, and the combo box is ready.
In the combo box, we can see the value of the range A2 to A5.
Download Template
This article must help understand Combo Box in Excel with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Combo Box in Excel. Here we learn how to create Combo Box, using INDEX() & Dynamic List with examples & downloadable Excel template. You can learn more from the following articles –
Leave a Reply