Excel Combo Boxes Tutorial


Creating combo boxes in Microsoft Excel is quite simple if you know how. A combo box is a drop-down list from which one of many items can be selected. You can create a combo box in your Excel spreadsheet without using Visual Basic, with or without optional extra features such as 3D shading and a return value (which returns the number of the selected item on the list).

Combo box item list

To create a combo box, begin with making a list of items you wish to include in your combo box. Put one item per cell consecutively in a column. As an example, we will create a transportation list, typing "car" into cell A1, "plane" in A2, "ship" in A3, "train" in A4 and "bicycle" in A5.

Combo box item list

To find the combo box, unhide the Forms toolbar. To do this, click on "View" (in the File menu), "Toolbars," then "Forms." A floating Forms toolbar will appear. Click on the "Combo Box" icon. Click on the location in the spreadsheet where you wish to place the combo box. Drag and release to insert the size of combo box you desire.

Creating a combo box object

To link the combo box to a list of items, first right-click on the combo box, then select "Format Control." On the Control tab, in the Input range text box enter the range of cells where your list resides. In our transportation list example, we would enter A1:A5 as this references the cells where the items are listed. Now enter the number of items in your list in the Drop down lines text box, 5 in this example. This isn't required, but it will prevent truncation of your list in the combo box or extra white space at the end of the list if the incorrect value is populated.

Formatting the combo box

To finish making your combo box, click "OK" in the Format Control pop-up menu. When you click on the drop-down arrow in the combo box, your list of items will appear. Click on the one you wish to display in the combo box.

Using the combo box

After creating a combo box, you can resize and/or relocate it on the spreadsheet. To move the box, select it, and then drag and drop or use the arrow keys. To resize your combo box, select it and drag by the circles on the selection outline. Alternatively, you can open the Format Control menu (see the Formatting section above), and then manipulate the size from the Size tab. You can set the positioning/print properties on the Properties tab of the Format Control menu.

To display the item number selected, navigate to the Control tab of the Format Control menu, then in the Cell link text box enter the cell number where you want the item number displayed. In our transportation example, if D1 is entered in the Cell link text box, the item number selected will appear here. If we select train, for example, in the combo box, the number 4 will appear in cell D1 because "train" is the fourth item in the list.

To give your combo box a bit of a 3D appearance, check the 3-D shading check box on the Control tab of the Format Control menu.

Additional Formatting


Promoted By Zergnet


Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!