How to make a cell into a Drop-down combo-box in MS-Excel – Part 2

In the last post we saw how we can make a simple combo-box with a small list of values. Now we will see how we can make a drop-down combo with a huge list of values, in which case it will be cumbersome to write down all those values in the Source field of the Data Validation dialog.

HOW TO: MS-Excel - Create a Combo-box with a huge list of values

In this case, we can use a range in the Source field of the Data Validation dialog, which MS Excel uses to identify the values to be provided in the combo-box. Simply list the values to appear on the drop-down list in a spreadsheet column and provide the range of this list in the source field.

We can see how with an example.

Now the drop down appears as below with all the values from the range.

Optionally you can hide the column containing the values, here column D to make the spreadsheet appear neat. To do this, right-click column D and select hide as in the figure below.

By doing the above the spreadsheet combo-box looks as below.

Tip: You can also name the range, so that the source can just refer to the name, say ‘=fruits’ as below.

To name the range, just select the range and type a name for that in the name box which is to the write of the formula bar as given below.

Cool, isnt’t it!

Hope you liked this. In the next post we will see how we can make a much more complex drop down, where in the combo-box value is dependent on the value in another cell. This will be particularly useful when we have a category and sub-category classification.