How to Create Excel Drop Down List

hi everyone my name is Kevin today I want to show you how you can create drop-down lists in Microsoft Excel and it's full disclosure before we jump into this I work at Microsoft as a full time employee my company requires me to say that any time 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 gonna have other people filling it out perhaps you only want them to fill out a cell with specific values and a 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 two 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 want learn how to work with 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 I'll teach you how to use it for drop down lists this is an excellent tutorial pagin that I've 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 meter 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 as perfect reviewing this so to create a list what we're gonna 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 gonna select all these cells here b2 through B 13 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 in 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 gonna 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 beats comma and then a space and then maybe I'll type in bakery so those are gonna be my three different departments of course I could put more in here but I'm gonna use that as my list so what I'm gonna 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 isn't meats and then let's see if I could find so 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'll update so what we're gonna 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 so what I'm gonna 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 then I'm going to go to insert so I'm gonna go to the insert pivot and what we're gonna do now is I'm gonna insert a table so I'm gonna 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 gonna 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 gonna go and click on the list and now for source instead of typing in the different things like say in process or submitted rather than doing that I'm gonna say my sources on the sheet itself so I'm gonna click on this little thing and that will allow me to select my source so I'm gonna select these three because these are the different status items that I want to put within here so I'm gonna 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 going 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 up here 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 OK and now what will happen is as I click into this cell you'll see this message appears it 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 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 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 gonna go ahead and select that and so now if I come in here and just say in progress maybe whether say in progress right now and I click Next I get this error but 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 alright 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 or 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 by

Channel: Kevin Stratvert

Views: 147,609

Rating: 4.9516263 out of 5

Keywords: excel, drop down, drop down lists, list, lists, drop-down, microsoft, microsoft excel, tutorial, help, guide, how to, support, data validation, valid, spreadsheet, worksheet, workbook, excel 2019, excel 2017, excel 2013, excel 2010, excel 2007, office, office 365, 365, 360, microsoft office, cell, cells, criteria, source, input message, error, warning

Id: QR04owFaJy4

Length: 8min 36sec (516 seconds)

Published: Thu Jan 02 2020