Microsoft Excel - Adding an in-cell dropdown menu

http://Excelopedia.com presents a tutorial explaining how to add a dropdown menu to an individual cell in a Microsoft Excel spreadsheet. Applies to Excel 2003 or earlier versions.

---

Closed Caption:

adding an in cell drop-down menu one
thing that can really help speed up data
entering and also help eliminate some
mistakes is the practice of adding in
cell drop-down menus to your spreadsheet
a sample drop-down has been created in
the yellow shaded cell when selecting it
a small down arrow is displayed and
clicking the arrow presents a list of
values available for selection selecting
one places the value in itself to remove
the cell simply use the Delete key if a
user attempts to type in a value that's
not from the specified list an error
message is displayed clicking cancel
deletes invalid entry and the user is
back to choosing one of the listed
options the process for adding a drop
down menu is pretty simple
suppose we wanted to add one to this top
green sell that gives the user a choice
of either yes or no to do this
having selected the cell where we want
to drop down to appear go up to the data
menu select validation and any interface
that comes up one of three tabs will be
on top we're concerned with the settings
tab in the allow field select list and
in the source field type the values that
you want to appear in the drop-down
separated by commas you can also put a
space after the comma makes no
difference make sure that this in cell
drop-down checkbox is checked and click
OK and our drop-down menu is working
building on the same method we can also
have excel pull the values for our
drop-down from a range of cells in the
spreadsheet itself so for this example
suppose in this middle green cell
we want to drop down that had the values
highlighted here in this orange area to
do that once again select the cell where
you want to drop down to appear data
validation settings tab you want list in
the allow field and this time instead of
typing the values in we're going to
click on source and then drag your mouse
over the cells that contain the values
we want to use notice that excel puts in
the range address from those cells
click OK and our drop-down is working
one handy feature of this is that if you
change one of the values in your range
your drop-down is also updated but
rather than have all these drop-down
values clutter up your page
it's a little cleaner to pull them from
another sheet excel makes this a bit
more complicated but it can be done
first let's try doing it the way we've
already learned for this example i'd
like to pull in these values for the
months and place them in this this cell
bottom green cell next to billing month
so selecting the cell that we were we
want to put the drop-down data
validation settings list and source when
i attempt to click on the tab the sheet
tab it doesn't allow me and I try to be
clever and type in the specific address
on another sheet
Excel gives me a message that
specifically says I can't use references
to other worksheets or workbooks for a
drop-down so according to excel
it can't be done but there is a trick to
get around this will need to use
something called a named range which is
a whole other tutorial topic but will
touch enough on it here to produce our
drop-down basically rather than list a
specific spreadsheet address for a
drop-down source we're going to give
that address a name enlist that name is
the source probably a bit easier just to
show you
so first of all we're going to name the
range i already have it selected I go up
to insert name define i'm going to name
this range once and click OK back over
on the sheet where I want to add the
drop-down I once again select the cell
where I want to drop down to appear data
validation settings allow field should
be list and in the source now more type
equals and the name of the range that i
created click OK and it's pulling the
values from the other sheet so using the
three methods we've just examined we
have a fair amount of flexibility in
using drop-down menus look for this and
other handy excel tutorials
edexcel appiah . com