Insert a Combo Box (Form Control) in Microsoft Excel 2010


In the previous post we told you how to insert a list box and its values into the Excel worksheet. In this post we tell you how to insert a Combo box (Form Control) and its values into the Excel worksheet.

A combo box can be used to select a list of data or numbers by using its optional values. When you select an optional value from the Combo box, the option’s index position as specified in the list in the excel worksheet will be returned.

For example, say, you have inserted six colors in cells H1 to H6 and made these colors appear in a combo box. Now, when you click on any color in the combo box, the respective color’s index position as specified in cells H1 to H6 is returned. Below is a tutorial that will guide you completely on the above mentioned process with an illustration.

To insert any form control in Excel worksheet, you should first add the Developer Tab in the Ribbon.


To insert a Combo box, do the following

1) Click on the ‘Developer’ tab and then under the Developer tab, in the ‘Controls’ category, click on the ‘Insert’ icon and then under ‘Form Controls’, click on the ‘Combo Box (Form Control) icon.


Figure 1



2) Now click on the cell A1 of the Excel worksheet to insert the Combo box.


Figure 2




To insert values into the combo box, do the following

1) Insert some six to eight colors in cells B1 to B6.


Figure 3



2) Then Right Click on the Combo box that you inserted and select the option ‘Format Control’.


Figure 4



3) Now, in the ‘Format Control’ dialogue box thus opened, in the ‘Input Range’ box, enter the range as ‘B1:B6’ and in the cell link box, enter the cell link as ‘C1’.The cell link is the cell where the index position of the color that you select in the combo box will be returned.


Figure 5



4) Now, click on the arrow of the combo box and you can observe that the Combo be populated with the list of colors that you entered in cells B1 to B6.


Figure 6



5) Now, if you click on any color in the combo box, the respective color’s index position as specified in the cells B1 to B6 will be returned in the link cell i.e., cell C1. For example, if you clicked on the color ‘Violet’ in the combo box, a value ‘5’ will be returned in the cell C1. 5 is the index position of the color violet that you have specified in cells B1 to B6.


Figure 7



So, this is all about the Combo box form control and its insertion into the Excel Worksheet.

See also How to Create and Run Macros in Excel 2010 Application.




Read Other Applications