How To Do Three Different Drop Down Lists in Excel 2010

Tori Cushing from BlueGlass Inc will show users how to do three different types of Excel drop down lists. She will walk through creating drop downs with data validation, form controls, and activex control combo boxes.

---

Closed Caption:

hey guys this is troy pushing with blue
glass and sam's gonna want to do a quick
excel tutorial on how to do different
drop-down menus
the first one is going to be using data
validation I use this with the different
months of the year because sometimes sex
is confusing when some people abbreviate
july is GL or something like that
so to start this off we need to create a
list of months i did this already in
sheet - I like to do this in a second
sheet over and then end up hiding the
sheet at the end of the document that
way we can reference it in the data
validation but not have to see it
ok so what I did was just create a list
of months and then once i selected the
list of months
i named the range this is really simple
to do you just go up into the left
corner up in here and name it just like
that
already did this and I name my months so
now I go back over to my list of options
my table
I select them and I go up into data and
then i select data validation and there
are a whole bunch of different options
that you can use and data validation
today I'm just going to really focus on
the list so you select the list and then
you go to the source
you can either enter it manually by just
entering the named range or you could go
and actually select it to go to sheet2
and I select the months
the cool thing about this that you can
also entering your own personalized air
alerts so you can say that you can
either warn them that they're entering
the wrong set of data or you can
actually make them stop and turn your
own message to stop it
did the wrong ok so now when you select
one of the cells from this range it will
give you the options of all the months
now let's say I want to change something
in my list
so let's say I have radiated march to ma
are to go back in and it will
automatically update on its own
so my job down menu will actually show
ma are instead of March school it
ok now if I were to enter in the say gee
you all set of july my custom error
message will come up and say stop it you
did the wrong
so we're just going to cancel goes back
to dry so for the second part of this
tutorial you're going to have to make
sure that your Developer tab is viewable
you can see that mine is in right now
so what you're going to do is you're
just going to go up into the ribbon
right click customize the ribbon then on
the right-hand side you're going to have
an option to click on developer so it
okay
hey there's the Developer tab ok so next
we're going to insert a farm control
when i'm using right now is a combobox
cool thing about this is that you can
set up the size of your combo box and
you can even show how many options
I'm sorry how many rows you can show on
in your drop-down menu so you just right
click go to form control and controls
and you would input your range so i'm
going to select all the sources
now I like to link this to a cell so
whenever I click on this a google it
will output it
- we can click the cell
ok so let's say that I wanted to show 10
different drop-down lines now when I
click on this it will show me all of my
options
whereas when I go to data validation you
have to scroll through to see all the
options
now i'm going to actually what I usually
like to do is is set up the output cell
to be right behind the combo box and you
can just do this by right-clicking on it
and going into for our control then
selling going to move it to the 16
ok ok so lastly we're going to do an
activex control
this is a combo box using active x
instead of form
we're going to start this off by naming
our range over here
i named it all the source and you're
going to go back to the month sheet and
you're going to go to insert a combo box
and it's going to be under the activex
controls not the form controls you that
you just draw your box
and then you can start to actually form
at it you're just going to right click
and go to properties and go down to list
Phil range although sources and then you
want to link this combo box to a
specific cell
so I'm going to pick d19 we go
and it automatically updates the tricky
thing about activex controls that you
have something called design mode when
you want to change anything about this
control box you have to go into design
mode and then if you want to actually
access it instead of just being able to
click away and then click on it again
you have to turn off design mode ok then
you have all your options
I don't like the fact that you'd have to
scroll so I'm going to do is I'm going
to click and design mode click on the
box
go back to properties and make the list
row 10 instead of it
just close it out turn off design mode
yeah there they are all 10 of them
another cool thing about the activex
controls that you can go in and edit the
size of the text or even the style that
the texts in
so if i want to change the font from
colibri to something bigger or smaller
or formatted to the rest of your
document and I could change it in here
so say I have my whole document is
times new roman for example and I can
make it times roman and I can make in
size 12 or 14 and it automatically
updated you can also change your colors
event which is a really cool aspect to
close out your app design mode haha
there it is the color is probably wasn't
the best but you get the idea
the cool thing about that is you can't
do that with either the form control or
the data validation
so if you have a document that you
really want to have completely formatted
but also have a drop-down box
activex controls the way to go ok so i
really hope that you found this tutorial
helpful if you have any questions or
comments you can either post on this
YouTube video or you can post on the
actual blog post itself
I'll have a live link in the description
thanks