Combo Box In Excel

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.

Combo Box in Excel

However, if we use the Developer tab for the first time, we may not see this in our Excel ribbon.

Combo Box in Excel -1

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.

Combo Box in Excel -Step 1

Step 2: Click on Options.

Combo Box in Excel -Step 2
Combo Box in Excel -Step 3

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.

Combo Box in Excel -Step 4

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.

Combo Box in Excel - Step 5

For example, look at the following data in Excel.

Combo Box in Excel - Basic Example

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.

Basic Example - Step1

Step 2: Next, draw the combo box on the worksheet.

Basic Example - Step2

Step 3: Right-click on the check box and click on Format Control… option.

Basic Example - Step3

Step 4: Next, in the Format Control window, choose Control tab.

Basic Example - Step4

Step 5: Then, in the Input range: box, choose the cell range A2:A4.

Basic Example - Step5

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.

Basic Example - Step6

Step 7: Click OK, and the Format Control window gets closed.

Now, we will see the city values in the combo box.

Basic Example - Step7

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.

Basic Example - Step8

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.

Key Takeaways
  • Combo Box is 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.
Combo Box using Form Control
  • Protection: This will lock the combo box in the worksheet. To make this work, we need to first protect the worksheet.
Combo Box using Form Control -1
  • 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.
Combo Box using Form Control -2
  • 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.
Combo Box using Form Control -3
  • Control: This is the most important tab in the window.
Combo Box using Form Control -4

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.

How to create Combo Box in Excel

The steps used to create combo box in excel are as follows:

  1. First, go to the Developer tab, click on the Insert option, and then, choose Combo Box from Form Controls.


    How to create - Step 1

  2. 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.


    How to create - Step 2

  3. 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.

    How to create - Step 3

  4. The Format Control dialogue box opens up.


    How to create - Step 4

  5. Now, for the Input range: box, choose cells from the range A2:A7.


    How to create - Step 5

  6. For the Cell link: box, we should select any of the empty cell references.


    How to create - Step 6

  7. Similarly, for the Drop down lines: box, enter 4.


    How to create - Step 7

  8. Click on OK.


    We will be getting a combo box as shown in the below image.

    How to create - Step 8

    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.

How to create Combo Box using ActiveX Control

Draw the combo box on the worksheet.

ActiveX Control - 1

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.

ActiveX  Control - 2

Right-click on the combo box and click on the Properties option.

ActiveX Control - 3

This will open the separate Properties window as shown in the following image.

ActiveX Control - 4

Using these Properties window, we can change the properties of the combo box. Change the name of the combo box to Fruits_Combo_Box.

ActiveX Control - 5

This name can be used to select this combo box whenever we write VBA code.

Change the background color to Highlight.

ActiveX Control - 6

This will make the background color blue.

Next under ListFillRange, give the excel cell reference as A2:A7.

ActiveX Control - 7

The LinkedCell property gives the cell reference of D3.

ActiveX Control - 8

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.

ActiveX Control - 9

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.

ActiveX Control - 10

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.

ActiveX Control - 11

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.

Combo Box in Excel - Dynamic List

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.

Combo Excel - Define Name

Next, give a name for the named range that we are creating such as ‘Fruit_List’

Combo Excel - New Name - Fruit List

In the Refers to: box, enter the following formula.

=OFFSET(A$2,0,0,COUNTA($A:$A)-1)

Combo Excel - New Name - Refer's To

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.

Combo Excel - NEw Name - Empty Cells TEst

As soon as we select the named range, we will get the entire list of values entered in column A.

Combo Excel - Entire List

To test the dynamic nature of this named range, let us add couple more items to the fruit list.

Dynamic List - added items

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.

Combo Box - Dynamic List - 1

Close the Properties window and exit the Design mode.

Combo Box - Dynamic List - 2

Add few fruits to the list, and the combo box should show the list of newly added fruits dynamically.

Combo Box - Dynamic List - 3

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)

1. How to Add Combo Box in Excel?

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.

Combo Box in Excel - FAQ4

2. How to Remove Combo Box in Excel?

To remove Form Control combo box, right-click on the combo box and then hit the Delete button.

Combo Box - FAQ 2

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.

Combo box - FAQ 2a

3. What is the use of Combo Box in Excel?

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.

4. Where is the Combo Box in Excel?

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.

Combo Box in Excel - FAQ4

After inserting combo box in excel, we can create a list of values in it. For example, look at the following data.

Combo box - FAQ 4

Draw combo box from the Developer tab.

Combo box - FAQ 4a

Right-click on the check box and click on Format Control… option.

Combo box - FAQ 4b

In the Format Control window, choose the Control tab.

Combo box - FAQ 4c

In the Input Range, choose the cell range from A2 to A5.

Combo box - FAQ 4d

In the Cell Link, we should give any of the addresses of the cell. We have given the cell link as cell C1.

Combo box - FAQ 4e

Click on OK, and the combo box is ready.
In the combo box, we can see the value of the range A2 to A5.

Combo box - FAQ 4f

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published.