Hi everyone, my name is Kevin. Today I want to show you how you can create drop-down lists in
Microsoft Excel and as full disclosure before we jump into this I work at Microsoft as a full-time
employee. My company requires me to say that anytime I talk about our products. First off,
why would you want to create a drop-down list in Microsoft Excel? Well, imagine that you set
up a spreadsheet and you're going to have other people filling it out, perhaps you only want them
to fill out a cell with specific values and the drop-down list is the perfect way to make sure
that people select from those specific values. It's actually very easy to set up and I'm going
to show you step by step how to do it so you too can create a drop-down list. All right. Well,
enough talk. Let's jump on the PC. Here I am on my PC and I already have Microsoft Excel open.
This version of Excel comes with Office 365. It's the latest and greatest. However, drop-down lists
have been available as part of Microsoft Excel for a long time. Even if you have an older version,
you should be able to take advantage of this. Before I show you how to create drop-down lists, I
want to show one thing that's been added recently and that's a drop-down tutorial. If you click on
file and then home, you'll see a list of different tutorials. Not only will it show you how to create
drop-down lists, but you could also learn how to work with formulas. You get an introduction to
Excel, you can work with pivot tables, charting, so they're all types of tutorials that are added
as part of Excel that will teach you how to use it. For drop-down lists, this is an excellent
tutorial. Imagine that I run a grocery store and we have all different types of food that we sell
within this grocery store. You can find things like apples beef and bananas. What I want to do is
I want my staff to come in here and indicate what department these different food items can be found
in. Now I could rely on my department members to simply type in, well, you know, this is this
is produce or this is meat or this is bakery and they could go through and type these in. However,
what would probably happen is you have a lot of people working on a sheet. You'll have people
type in different things. Maybe they'll type it in differently from others and then we'll have
all different types of values and I want to make sure that the data that's entered is valid. So,
we list is perfect for doing this. So to create a list what we're going to do is so I have my
table set up and I want people to come in here and basically select a value from a list right here in
this cell. So, I'm going to select all these cells here B2 through B13 and what I'm going to do now
is I want to make sure I click into the data pivot on top. That's where we're going to be able to add
a drop-down list, and within here, there's an option under data tools called data validation.
What you can also do is if you just want to get there quickly up here and search you could simply
type in data validation and that'll also pop it up. So that's another way to get to this. So,
let's go ahead and we'll click on data validation and by default, we’ll be on this first tab called
settings. And now what I want to do is under here it says allow and today anyone could go in there
and type in any value they want. So it's set to any value. Like I said, I want to make sure that
people choose from a list. So, I'm going to go down and select list right here. And what I'm
going to do now is it says well what is the source of the list and so I could just type in items.
I could type in produce and then I'll type in meats comma and then a space and then maybe I'll
type in bakery. So those are going to be my three different departments. Of course, I could put more
in here, but I'm going to use that as my list. So, what I'm going to do now is I'll click on OK
and you'll notice what happened now is whenever I click into a cell, you'll see there's this little
drop-down arrow. And if I click on there now, you have to choose from one of those three values. So,
I could say well this is produce and then the beef is in meats and then let's see if I could find
some bread. That'll be in the bakery. So people could go through now and rather than typing a
value they could choose from a list. So pretty simple way to insert a fixed list. Now let's
get a little more advanced and I'm going to show you how you can create a list that will update.
So what we're going to do now is here's another here's another table where I have people who've
applied for a job. So I have application status and I want my basically my interview team to go
in and put what the status of each interviewee is. So what I'm going to do now is I want to create a
list just like I did on the previous tab, but I want it based on a list on within one of my
sheets. So, what I'm going to do here is I'm going to highlight these and now I'm going to go to
insert, So I'm going to go to the insert pivot and what we're going to do now is I'm going to insert
a table. So I'm going to click on table and it has headers because status list. That's my header. So
I'll say my table has headers and now I've created a list here. So just like I did in the previous
example, I'm going to highlight all of these cells and once again, I'll click on the data pivot. I'll
click on data validation and here I want to create a list again. So I'm going to go and click on
list and now for source instead of typing in the different things like say in process or submitted,
rather than doing that, I'm going to say my source is on the sheet itself. So,
I'm going to click on this little thing and that'll allow me to select my source. So, I'm
going to select these three because these are the different Status items that I want to put within
here. So I'm going to select that as my source and click OK. So now for Dave, if I click on this,
you'll see that I can select my item on a list and that's coming from this table over here. Now
what I could do is I could also add another item and perhaps I'll say completed or maybe we'll say
application status is accepted or I could say rejected and so I can add different items on here,
and as I add those different items to the status list, if I click on the drop-down, you'll notice
that they're automatically added to this list right here. Now what I could do is this list
the list of options happens to be on the same worksheet is where I want people to go through
and select an item from the drop-down list. What I could also do is this list could be on a different
worksheet. So I could have another worksheet with all the values for my lists and then the sheet
that people go in to fill out Is separate from these items so you could do that too.
So that's, those are the basics of how to insert a list whether you simply type in the items or
whether you're relying on a list within a sheet for your drop-down list. Those are kind of the
two ways you can do it. I want to show a few extra things that you could do to really get fancy here
and what we could do is I'm going to go back to data validation and what you could use you
could also have an input message appear. Now so this way when someone clicks into this cell you
show some instructions or some type of message. So, I could say maybe the title is select an
option. Indicate the status of the application in this cell from the drop-down. Something like that
and I'll go ahead and click on OK. Now what will happen is as I click into this cell,
you'll see this message appears that says select an option indicate the status and so I could
provide a little tool tip or a hints to people who are filling out my sheet what they should do. So,
that's kind of one neat thing that I could do. And for icing on the cake, I'm going to go back
to the data validation. Next what we're going to do is we're going to click on this error alert
option. And now what you could do is if someone enters invalid data, we could show a message So
maybe I'll show a warning that says please select an option from the drop or this is the title. So
please select an option and then my error message. Make sure to select an item from the drop-down
list. So I'm going to go ahead and select that and so now if I come in here and just say
In progress, maybe I'll just say in progress right now and I click Next I get this error
that says please select an option. Make sure to select an item from the drop-down list
and then I could say continue and that way it'll just inform me that this value should
not be entered and instead I should use the drop-down list that's provided to me right here.
All right, well that's how you work with drop-down lists in Microsoft Excel,
it's very easy to do. Hopefully you learned how to do it, and if you have any spreadsheets
that you're setting up where you need drop-down lists and you were able to do it successfully,
please give this video a thumbs up. If you want to see more videos like this in the future,
hit that subscribe button. That way you'll get a notification any time new videos new content come
out. And lastly, if there are any other topics that you want to see me cover in the future,
leave a comment down below. I read them all and I'll add them to my list of videos to create in
the future. All right. Well, that's all I had for you today. I'll see you next time. Bye.