How to Create Drop Down Lists in Cells - The Complete Excel Guide

welcome to Excel campus my name is John and in this video I'm going to explain how to create drop-down lists in cells so these are also called data validation lists and we can see when we click on this cell here we get this little drop-down icon next to the cell and when we click on that we get all these different choices to choose from and when we choose one of those that entry will be input into the cell so in this video I'm not only going to explain how to create the drop-down list but I will also share how to use keyboard shortcuts to make selections from the list how to copy and apply the list to multiple cells how to handle errors and invalid inputs and how to update the dropdowns with new data so let's take a look at how to create these I'm going to go over to this inventory sheet here and we're going to create a few different drop-down lists the first one will be in this column here we just have these values of yes and no so we want to create a drop-down list for those values so we can first just select this cell here we're going to go to the data tab on the ribbon and then over here there's a data validation button this is a split button and we're going to click the top half of the button that'll open the data validation window here and on the Settings tab we have under the allow drop-down we're going to choose list so this will create our drop-down list and really the most important setting here is the source so in this source box we can either reference a range or we can just put values in here we can just type values in here that are comma separated so if we just wanted to have yes and no as two options there we can just type yes comma no hit okay and then we'll see we have the drop-down list or the icon added right here to the cell and when we click that we'll get our options for yes or no and we can choose one of those and then that value will be input into the cell now we might also have a much longer list that we want to choose from for example we have this product column here and we also have a products sheet that has a table here it's a long list of product names and we might want to add these to our drop-down menu so we can do that by referencing a range so go back over to our inventory sheet and right here and cell a2 again we'll just select that cell data tab on the ribbon click data validation button open the data validation window we're going to choose list and now in this source section we can either click right here in this box or we can click this little icon right here and that'll allow us to select a range we're going to go over to the products sheet I'll select cell a2 and then I'm going to use ctrl shift down arrow that keyboard shortcut to select all of the cells here all they use 2 cells in that column so it's all the way down to row 83 we'll just click this button again and then we can see that we have our source range there that's great then we'll click OK and that will add that drop down there and when we click that now we can see that we have all of those values that are in that range listed right here and again again same thing we can select one of those to add it to the cell now there are actually a few different ways to input data here as we just saw we can use the mouse we can also just type a value right here if we know the value if we know the names of one of these items maybe like this one here of course we can just type that in and hit enter and that will be input there we can also use the keyboard shortcut alt down arrow so hold the Alt key press the down arrow key that'll bring up the list use the down arrow or the up arrow to go through these and then hit enter when you want to select one now if you want to search through this list unfortunately there's no built-in way to do that in Excel but I do have a free add-in called list search that allows you to search through these data validation lists I'll put a link to in the description below this video with more info on that so now that we have our data validation list in this cell right here we might want to copy it down to the other cells in this column because these columns here do not contain those data validation lists yet so one easy way to do that is if we just select the cell the first cell that contains a data validation list then select the rest of the cells in the column here we have this as our active cell we can see the data validation list there we'll go to the data tab again click the data validation button and Excel will warn us here that the selection contains themselves without data validation settings do you want to extend the data validation to these cells so we can just click yes right here and that will open the data validation window just make sure everything looks good and then we'll just click OK and that will then extend or add the data validation to all the cells in that selection so we can see it right here for all those cells and it also applies it to blank cells so we have blank cells down here we can still see the list here and we can choose a new value for that cell it's also important to note that we can also copy and paste cells that contain data validation to other places so if we just right click copy or hit ctrl C we can go somewhere else in our workbook or worksheet and just paste right here control V or just paste and we'll see that we have the data validation in this cell as well so that's another way to copy and just copy and paste to new locations to apply data validation to other cells if you're enjoying this video please click that big red subscribe button below the video to subscribe to our Channel and also click the notification bell icon there to get notified when new videos are published so let's now take a look at the errors and rules Wow with data validation so I'm going to go ahead and just hit escape here to undo that and if we're in this cell right here and let's say we type in a product that might not be in our list I'll just type baseball and hit enter you can see that we get a warning here so we're getting this message saying the value doesn't match the data validation restrictions defined for this cell and it's just telling us to either cancel or retry so we don't get any options here we're limited or prohibited from inputting data that does not exist in that source data range and we can control these rules so I'm going to go ahead and hit cancel here and again we'll go open our data validation window so we can look at some of these rules so right here on these tabs we have this error alert tab and this is where we can control those rules so right now the default setting has this checkbox checked that just says show an error alert after invalid data is entered which is exactly what just happened when entered baseball we can uncheck this if we want to allow the user to just input anything even though there is a drop-down list there if you want them to be able to input any other value that's not in the list just uncheck that and then hit OK and that will allow that the other thing you can do is check this and then we have some options here for what happens when invalid data is entered so we can the default was stopped which we just saw we can also display a warning and in this case the warning would come up you can put a title here or maybe an error message something like please add the data to the products list we'll do something like that and then we can hit OK and we'll see what happens now so now when I type baseball in this cell and hit enter we get this warning message that just says you can still continue and it's asking you yes or no but you see the warning message here that we just typed in please add the data to the products list and then you can choose to continue we'll say yes and that will allow us to continue now you'll notice that we do have an air warning here in the bottom I'm sorry the top left corner of the cell we get that little air box warning if we click that drop down we can see we have a data validation error we can even click here to get more info on that air it's important to note that this when we enter baseball here this did not enter that into our source data range if we go over to our products list here baseball has not been entered in this list anywhere it's not down at the bottom or anything like that we'd have to go take that extra step to add this value to the list so let's jump back over to our inventory sheet and then the other option we had there if we open our data validation menu again for air alerts is just information and this will really bring up kind of the same window there but we don't have that yes/no option the user will just continue the entry will be input here in this cell and it'll just give a give the user a little information letting them know they might need to take an additional action so those are the three different options we have there for the invalid entries in our data validation list and it's also good to know that any existing values in the cells will not be impacted by any changes you make here so for example if I was to change this to stop that means we can't enter any invalid data and invalid entries and I hit ok this value will not change this would be an invalid entry it would kind of break the rules of the data validation but excels not going to go change this for us we do still get that warning box here that we can go make changes to but Excel is not going to make any changes to any existing values even when we change the data validation list for the cell and finally let's look at adding new data to our source range so let's say we did want to add baseball as a product and go over here to our products table and down at the very bottom here and we can just type baseball in the list and hit enter now that's not going to automatically add baseball to our data validation list if we go back over here to one of these lists and we go all the way to the bottom we can see that we don't see baseball here it's also important to point out that this list is in the same order as the source data range so if you want it in alphabetical order you can go back over to the source data range and sort that range but we don't have baseball here so we need to update our data validation list so I'm going to select the cell go to the data validation and then we're going to go to the Settings tab here and in the source box we can either just type our new row here so delete 83 replace that with 84 or you can select this button and go reselect the range now the important step here is this checkbox right here that says apply these changes to all other cells with the same settings we want to make sure we click this and when we do that we can see that the other cells that contain the same data validation list are also selected and so all of these changes that we make or any changes we make will be applied to those cells as well so we'll go ahead and click OK and if we hit this drop down here we can now see that we have our new item right here at the bottom of the list and you know even if we go look at some of these other lists here they also have the new item right there at the bottom now there are ways to automate this process so we don't have to date that source range every time our product list changes or whatever your source list is here every time this changes we don't necessarily have to go make changes to our data validation settings and I'm going to create a whole nother video on that explaining how to use tables and named ranges you can also use dynamic named ranges for that and I'll create another video that explains that in more detail and then finally if you want to clear the data validation list that drop-down menus from the cells you can do that by again selecting a cell that contains data validation open the data validation menu we have a few options for this as you can see there's a clear all button right here that will clear all of the settings including the air alerts any input messages you create that will clear all of the data validation list settings for this cell or if we check this box here for all of the cells you could also change this back from a list to just any value just know that when you do that the air alerts and input messages will still be applied so instead of doing that we might just want to again check this box and hit the clear all button and then we'll hit OK and that will clear our drop-down lists from all of these cells so I hope that helps get you started with data validation lists of course if you have any questions please leave a comment below if you enjoyed that video there are a few simple things you can do to help me out if you are watching this video on youtube click the like button below the video and leave a comment with any questions or feedback and please don't forget to subscribe to my free email newsletter to get more tips and tricks that will help you learn Excel thanks again for watching and I'll see you soon

Channel: Excel Campus - Jon

Views: 1,430,733

Rating: 4.9119787 out of 5

Keywords: excel, excel campus, excel drop down list, drop down list, excel data validation, microsoft excel, drop-down list, excel tutorial, how to create drop down list in excel, copy drop down list in excel, clear drop down list in excel, make dropdown list in Excel, drop-down list to an excel cell, extend data validation list automatically, include new items in data validation list, add new items to data validation list, make drop down list larger

Id: KGnvCKiOLM0

Length: 12min 47sec (767 seconds)

Published: Mon Aug 13 2018