Excel Drop Down List Tutorial

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 formulas. You get an introduction to Excel. You can work with pivot tables, charting. So there are all types of tutorials that are added as part of Excel that'll 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 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 a list is perfect for doing this. So to create a list, what we're going to do is 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 can 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 okay. 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 table where I have people who've applied for a job. So I have application status, and I want 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 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 sources 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 do is 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, indicate the status of the application in this cell from the drop down, something like that. And I'll go ahead and click on okay. And now what will happen is as I click into the 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 hint 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 into data validation. And 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 anytime new videos and 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.