Listing the Choices
Yesterday, we discussed the idea of data validation in MS Excel using a range of values. It’s a great idea and it’s definitely very helpful in certain situations, but what if you have a list of data items that are the only acceptable choices?
Maybe it’s a list of specific values and the range would allow for unwanted data to be entered. Or, maybe it isn’t numeric data at all and you’ve got a list of text items that are considered acceptable.
Whichever the case, it’s not a bad idea to extend your knowledge of data validation. I feel it’s always good to have one more trick up your sleeve.
So, let’s take a look at creating a drop down list of choices for a cell or range of cells.
The first step is to highlight the cell(s) for the drop down list of data choices.
For that, let’s return to the Data menu, Validation choice.
This time, when the Data Validation window opens, under the Settings tab in the “Allows:” field, choose List.
At the bottom of the tab, a field called “Source” will appear.
In this field, you can enter the choices you want displayed in the drop down list attached to the cell. Be sure to separate your items with a comma.
Now, if you already have the choices in a set of cells somewhere else, you could choose to simply tell Excel where to look for the choices.
To use this method (instead of typing the items in the field), click the button at the end of the field that looks like a worksheet.
You’ll then be taken back into your file with a little floating Data Validation window.
Highlight the cells that contain the choices. (Excel automatically enters the location of the newly highlighted cells into the Source window for you).
Next, click the button at the end of the field (it now looks like the small window you came from).
You’ll be returned to the full-sized Data Validation window where you can set the Input and Error Messages, just like we did when we set a data range.
(I should add one quick side note here about this second way of giving Excel the list choices. If you should happen to delete the items from the cells you directed Excel to use, your list will be empty. If you make a change to the data in the cells, the list will change too. This could work for you, but it could also work against you. Just be sure this is the method you want to use, because the list can be so easily and inadvertently altered).
Click OK when you’ve made all your choices.
Now, back in the file, you’ll find that the cell(s) you highlighted before you began now have down arrows to access the drop down list of choices you just set.
It’s as easy as that. Now you’ve got complete control! Most users either use one of these or leave the cell blank. There’s no ifs, ands or buts about it!