Tip #4130 - Listing the Choices
Printer Friendly Version | E-Mail This Tip
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!
~ April
Want To Comment On This Tip? Click Here! We'd love to hear from you :-)
Like These Tips? Get 'Em Free In Your E-mail
