Creating a Combo Box to display a drop down pick list

You can use combo boxes to ensure continuity in formulas and user selections. This helps enhance the user experience and guide the user via standard options. Confused? Let me show you an example.

Let’s say you want to have the user select an item color so that you can return the sales for that item / color combination. Well you could just leave a cell blank and have the user type in the color. However they could type the color name incorrectly or perhaps select a color that is not valid.

I can guide the user by creating a combo box. In this example I have created a sample list of colors in column A.

	A
1	Colors
2	Red
3	Orange
4	Yellow
5	Green
6	Blue
7	Indigo
8	Violet

From the developer tab I select Insert then Form Control Combo Box (not to be confused with the Active X Combo Box). I then place the Combo Box in Cells C3 and D3. Since the box is free form I can make it as large or small as I wish.

Next I right mouse click on the Combo Box and choose Format Control. On the control tab, I set the input range to be A2 – A8. I want to put my Combo Box result in cell G2. I also display 7 lines since I have 7 options. If I had a large data set such as 50 States, I could set the display to 10 and a scroll bar would appear.

When done I can click on the combo box drop down arrow to display my colors.

The end result is that the number 4 is placed into Cell G2 (our cell link).

	A	B	C	D	E	F	G	H
1	Colors
2	Red						4
3	Orange
4	Yellow
5	Green
6	Blue
7	Indigo
8	Violet

But wait a minute. You are staying that I selected Green and you are correct. Why does cell G2 not have the word Green? Well the Combo Box will return the number of the selection. Green is the 4^th item in the list. If I selected Indigo, then cell G2 would show 6.

I can use the number returned in formulas or can use a Vlookup to return the Color based on the number returned.

So the next time you need to need to have a user enter in a value, consider using a Combo Box.