Hi, this is Wayne again with a topic “Create Dynamic Dropdown Lists in Excel”.
In this article i’ll show you how to create drop-down lists in microsoft excel and keep watching till the end, because i’ll show you how to dynamically update those drop-down lists. So here we have a movie list with some data about the movies and whenever a new movie is added to the list, the person using the spreadsheet has to type in the category of the movie and also this other information. But in many cases like this, it’s better to make this simply a drop down list. So, instead of typing, the user can just click and select from a list.
There are a couple of different ways. You could do this. Let’S look at the simplest. If i select a cell or a range either way and then go up here to the data tab in the data tools group, you should see this symbol here. Just click on this drop down arrow and choose data validation.
Data validation is a powerful feature in microsoft. Excel that helps you control the type of data that is put into a cell or a range so i’ll click, data, validation and there’s various ways i can control. What’S put into this range, i could force the data to be whole numbers or to be decimals or any of these other options in this article, we’re going to focus on list, so i click list and now excel is looking for a list. What is the list that people will be able to choose from when they’re entering data into column b? Well, the easiest simplest way to provide excel with a list is to click here in the source box and type out a list.
So i could type family, comma adventure, comma comedy and i’ll stop there for now and click ok. So now, when someone clicks in cell b2 notice that there’s a drop down arrow, that pops up people can click and select family adventure or comedy. Why just those three? Because those are the only three that i selected in the data validation options now there are some limitations to this method of creating a drop-down list. Probably the biggest drawback with this method is that the entries are not dynamic.
If i decide i want to change the wording instead of family, maybe family friendly or something like that, i would have to reselect the range go back to data validation, change it in the list. Then click. Ok! If the list is a long list, that’s going to be kind of difficult to do so. Let’S try a second method and this method will make a dynamic drop-down list. The first thing i need to do is select the range that has the data validation in it and then go here to the data, tab, data tools, group i’ll, select, the drop-down list, data validation and then clear, all click. Ok, now all of the data validation has been removed from this column. So let’s look now at how to create dynamic, drop-down lists. The first step is to have a list of every possible option that you want in the drop-down list, and i have that here notice that i’ve included a blank cell.
I want it to be an option to put nothing into the cell and the very best way to handle this list is to put it on a different tab. So i’m going to select the list hold ctrl tap x, to cut and then i’ll click. This button to give me a new tab, a new sheet and then ctrl v to paste i’ll double click between the a and b columns so that it fits better in the column. Okay.
Next, i’m going to go back to my movie list spreadsheet and i’ll select. The range of data where i want the drop-down list to appear all the way down. In fact, i’m going to go beyond the list. That’S already there and then release the mouse button with that range selected, i’ll go to the data tab, data tools, group click, the drop down, arrow data validation and again i want to switch this to list, but instead of typing in my list, i’m going to click And then click on sheet 1 and click and drag to select the range of options that could be chosen notice.
What excel is put in this box equals sheet and it shows which sheet and what range on that sheet. I click. Ok. Now, let’s look at the result, if i click let’s say next to elf, i could change it from a comedy to what it really is, which is a horror movie. So the drop down list is working great, it’s pulling it from my second sheet. If i want to, i can select no category so now that the drop down list is set up linked to. What’S on this other sheet, look what i can do. I could rename one of these categories instead of romance how about romantic tap enter.
Now that it’s renamed, i go back to my spreadsheet. I use the drop down arrow to select and look, it doesn’t say romance. It says romantic. So that’s one of the things that makes this method superior to the first one.
I showed you this method’s not perfect, though either watch what happens if i add a brand new category to the bottom of the list. Maybe something like romantic comedy. If i go back to my movie list, look it’s not included in the options. You can probably guess why it’s, because when i set up the drop down list, i specifically chose this range and the new category doesn’t fit inside that range.
So it’s not included in the options when you click the drop down arrow one way around that problem would be to select the new item that you want to add to the list hold the shift key and then use the mouse to drag it up. I’Ll put it in alphabetical order, but basically dragging it within the existing data. Now, if i go back to my movie list, i can select the drop down arrow and it is included. Okay, i’m going to remove the data validation off of this range by selecting it going to the data, validation options and choosing clear all click.
Ok. So this time i’m going to go to my second sheet, where i have my list of categories, but i’m going to convert this list into a table, so i’ve clicked and dragged to select a1 through a18, now i’ll go to the home tab in the styles group. I’Ll click format as table and i’ll pick. Maybe this style here, i’m going to check this box to say that my table has headers click. Ok, so now we have a table and then i’m going to create a named range, i’m going to name the range that includes each of the valid options. So i clicked and dragged from a2 through a18 here in the upper left, i’m going to name that range i’ll call. It categories tap enter on the keyboard. It doesn’t look like it worked, but if you click this arrow here you can see that it is working.
I now have a named range called categories, and it consists of this so now back in my movie list i’ll select the range that i want to use, data validation with and then i’ll go to the data. Tab data ribbon. Here’S the data tools group. I click the drop down arrow data validation. I want it once again to allow a list what’s the source of my list. Well, in this case, i can just click here and type equals categories, because i named the range categories.
That’S all i need to do. Equals categories now i do have an option for a blank category, so i might want to uncheck ignore blank i’ll click. Ok, let’s see if it worked now i have my same drop down list. That’S on the other sheet. I can pull it in when a new movie is added to the list. I can tab over and click the drop down arrow and choose the appropriate category. Now what happens when i add to this list of categories? If i go here on the second sheet, i can click underneath western and, i could add, let’s say cartoon, let’s say sports and classics, because this is a table watch what happens when i go back into movie list. I can click and at the bottom of my drop down list, i do have my latest entries cartoon sports and classics once i’m happy with the list of categories on this second sheet, it’s a good idea to right click on the sheet name and choose hide so At this point, anyone using this spreadsheet is very unlikely to accidentally delete or change the data that the drop down list relies on. If i ever need to unhide the list in order to change it, i can just right click here on my movie list tab and choose unhide.
It shows me the hidden sheets and i can select one click. Ok and now it’s unhidden, a couple of other advanced options that you have with drop down lists is, when you select the data and go into data data tools, data validation, in addition to specifying where the list of options is notice, that we have two other choices In the form of tabs, so next to settings, i have input messages, show input message when cell is selected. So whenever someone clicks anywhere in this range, this input message could be shown. I could give it a title. I could put something like click button to select and then i could put an input message, something like that: click.
Ok. So now i don’t see any message until i click on a cell where i’ve added this message and the message: pops up click the drop down button to select from the available categories, and so this message is a way to guide the user so that they put Better data into the spreadsheet there’s one other thing i could have added to my data validation. When i selected my range, i could have gone here to the data ribbon, data tools, data validation and, in addition to putting in my list and making some changes, putting in an input message, i could have also gone here to the error alert tab. So this makes it so that when the user enters invalid data, a specific error alert is posted.
Okay, something like this should work. Fine, i click. Ok, let’s look at the final results. I can click to select the category of movie when i do select a cell.
I’M guided on how to add the data and, if i put in invalid data, i get a warning now, as you probably noticed, i added this data validation not just on the existing data, but i went down a few rows, but i didn’t go all the way Down to the very bottom of the spreadsheet, so what happens if i end up with 200 or 500 movies in this list, the data validation that i’ve set up isn’t going to cover the data that i enter down here. Well, this is what i could do. I could select a cell that has the data, validation, hold control and tap c to copy that information, and then i could click and drag to select the range that i want to have the same data validation, i’m going to go down to row 903. Now i’ll go to the home tab and in the clipboard group, i’ll click, this drop down arrow underneath paste and i’ll choose paste special.
I only want to paste the validation. I click. Ok. So now, let’s go down all the way to let’s say row.
250. Look. I already have the data validation set up and the drop down list works it’s available and if i make a mistake, i get the error message. So in this article i’ve shown you.
What, in my opinion, are the best ways to set up drop down lists in microsoft, excel i’ve shown you all the most important features and options and how to set them up properly. If there’s a lot of interest in this article, i’d like to make a follow-up video that shows how to create multiple dependent, drop-down lists so that, if you select let’s say comedy, then other options appear because you selected comedy, and if you had selected crime different options, Would appear, but in the meantime thanks for watching, i hope you found this tutorial to be helpful. If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you can support me through my patreon account or by buying channel merch and you’ll, see information about those options in the description below the video. Recently, our channel surpassed a major milestone: 1 million subscribers.
Thank you so much to all of my subscribers. Everyone who has watched my videos subscribed or supported the channel in any way, including my great patreon supporters, those that have clicked the thanks button below my videos. Anyone who has purchased merch from my channel and really anyone that has supported the channel in any way.
I can’t truly express how much i appreciate all of your support and encouragement. Thank you. Oh it’s! So big, it’s huge! Oh, my goodness.
That’S awesome! [ Applause! ]! Nice, that’s so cool for passing 100 subscribers .