Hi everyone, Kevin here. Today we’re going to learn how to make dependent dropdown lists in Excel. When you select a value in one dropdown that’ll influence or filter the available values in the next dropdown or even multiple dropdowns. It’s easy to do, so let’s dive in. Here I am in Microsoft Excel, and if you’d like to follow along today, I’ve included this same exact workbook that I’ll be using in the description down below. Feel free to click on the link and then you can follow along. I’m pulling together an order form for the Kevin Cookie Company, and we offer a few different items. Right over here, we see a list with two items. You could order either a cookie or a drink. If a customer selects a cookie, well then, I want to show another list that presents all of the different cookies that we offer here. If a customer chooses a drink, well, we have a few different drink options and you can see all those options here. But if someone chooses, let’s say, a coffee within the drink list, well then, we have different options for coffee. And same with the fruit smoothie. If someone wants a fruit smoothie, they are delicious. Over here, we want to present a few more options. So how do we do this? Well, you might have to watch a video on YouTube. Oh, that’s what this is. Okay, over on the left-hand side, first off, let’s highlight the first list or the first column with all the different items. And we need to turn this into what’s called a named range or a named list. So, with these items highlighted, up on the top tabs, let’s click on the option that says formulas. And right here in the middle, we have something called the name manager. Let’s click on this option that says create from selection. When I hover over, you’ll also see that there’s a shortcut key that you can use. That makes it easier, especially if you want to turn lots of different ranges into named lists. I’ll click on this option and this opens up a dialogue. Here it says create names from values in the, and I want it to be the top row because I have my header in the top row and then all of the list items underneath that. So here I’ll make sure to check this item that says top row. I can leave all these other ones unchecked and then let’s click on okay. And I now have a named range for that list. To confirm that, you could go up to the top and we can click on the name manager. And when I click on this, here I can see that I have one list titled item, and within that list, I have two different items, cookie and drink. That’s perfect. Down below, let’s click on close. One other way that you can confirm that you successfully set up a named range, here I’ll click into one of these cells and here I can type in equals and then I’ll type in the name of this list. And remember the name is item. So here I’ll type in item. Here you see that it found named range titled item. And when I click on that and then press enter, that’ll return all of the items within that list. So that way we can confirm that it’s working properly. Now I don’t need that here, so I’ll click into this cell, hit delete to remove that. Now I want to create my first dropdown list. So down on the bottom sheets, let’s click into the one titled order form. On the order form sheet, I’d like to insert my first dropdown list in cell A7. This way a customer can come in and they can select the item. To insert a dropdown list, up on the top tabs, let’s click on the option that says data. And then right here near the center, let’s click on the option that says data validation. This now opens up a dialogue and here we’re in validation criteria. There’s a dropdown right here and let’s select the option that says list. We want to make a dropdown list, so let’s select that. And over here it says the source for the list. So, what items do we want to show in this list? Well, we want to use that named list that we already created. So here we could type in equals and then we could type in item. That was the name of our list. Alternatively, here, when you select list, another quick trick, you could type in equals and then press the F3 key on your keyboard. And this opens up all of the different named lists that you have, especially if you have many of them, this could be an easy way to get back to the right one. And here we just have one titled item, so I’ll select that, click on okay and that inserts it into the source. Now I’ll click on okay. And look at that, it’s now inserted a dropdown list. Now, if I click on this button, this exposes all the different items in the dropdown list. So here we have cookie and we also have drink. I’ll select cookie, that’s great. But when I select cookie, I now want another dropdown list here where I can now select my type of cookie. To do that, we need to create a few more named lists. So, let’s jump back to the sheet titled lists. To make more named lists, we’re going to do the exact same thing that we did with item. Here, I could simply highlight all of these different headers and let’s select this entire area. Now up on top, we could click back into formulas and here we have our name manager. Let’s click on create from selection. And again, I want the list name to be the top row. So here, I’ll make sure that just top row is checked and then click on okay. Now, if we go back to the name manager, here we could see all of our different lists. So here we have all the names and all of the values. But you’ll notice one problem. Here within coffee, we have all these empty values within this list. And here within drink, we have all these empty cells and same with fruit smoothie. Now, if we go back over here, you’ll see that I highlighted all of this, so it included all of these empty cells. Now that may be okay. The only downside is when you click into the dropdown list, you’ll have all these empty values. So, if you want it to be cleaner, you’ll probably want to go through and highlight each one of these lists and then turn it into a named list. And I’m going to do that. Here, I’ll click into the name manager and let me select these three. These all have these blank values included. So up here, I’ll click on delete, click on okay, and then click on close. The cookie column was fine, so I don’t have to delete that one. This one had all the values included, over here, I could highlight this. Then let’s create from selection, top row, click on okay. I’ll highlight this one and just those values, create from selection, just the top row. And I’ll do the same with the fruit smoothie. Here, let’s include the top row. Now, if I click into the name manager, you’ll notice that all of them are very clean and they just include the values. That’s exactly what I want. Down below, I’ll click on close. And now let’s add some more dropdown lists. On the bottom worksheets, let’s jump to the one titled order form. In cell B7, I now want to create a dropdown list that depends on the value selected in the first dropdown or in cell A7. If we expand this dropdown, there are two different values. You have either cookie or drink. And depending on which one the customer selects, that’ll influence what options show up in this dropdown. Now, one very important note, if we jump back to the lists sheet, here we see that there are two different options. You have cookie and you have drink. Depending on which one a user selects, you need to make sure that you have another list that matches that name. For example, if someone selects cookie, well, then you need another list with the name cookie and all the options underneath. Same here with drink. Here I have an item titled drink. And I also have another column or another list with the title drink and then all of the options. Now it won’t work if let’s say you have drinks and then your column is titled drink. They have to match one for one for this to work. Let’s jump back into the order form. And here I now want to insert my dependent dropdown. So, like we did earlier, up on top, let’s click into data and right over here, let’s click on data validation to insert our list. This opens up that same dialogue that we saw earlier. Now we want to insert a dropdown list. So, let’s click on this dropdown and then select list. And then down below, we need to enter in the source. Now we want this to reference the list name that’s selected in cell A7. So how do we do that? We’re going to use a function called indirect. So here I’ll type in indirect and then we want to use the value in cell A7. So, I’ll enter in A7. Now, if you just enter in A7 on its own, it’ll use the value of the cell. So, then our list would just have the value cookie. Instead, we want to reference the list and that’s how the indirect function helps us. It looks at this value and then it references any lists that use that value or that name. This all now looks good, so let’s click on okay. And now we have another dropdown list. Now, if I click on this dropdown, here I get all the different cookie types, but let’s test out to make sure this works properly. Over here, I’ll click on this dropdown and let’s switch to a drink. Over here, let’s click into this cell, click on this dropdown and here, I have all of my different drink options. That’s pretty cool. But here, if I select coffee, I now want yet another dropdown list. So, let’s do the exact same thing. With this cell selected, let’s go back up to data validation by clicking on this button. Just like we did before, let’s click on this dropdown and let’s select list. And over here, again, we’re going to use the indirect function. I’ll type in equals indirect and then open parentheses and I want to select cell B7. So here, I’ll select that and then close parentheses and then let’s click on okay. And here, I have another dropdown. When I click on this, we now see all the different types of coffee. That’s great. Let’s now try fruit smoothie. So over here, I’ll click on this dropdown and let’s select fruit smoothie. And let’s come over here and click on this dropdown. But nothing is showing up. Why is that? Let’s have a quick look at the lists sheet down below. Let’s click into this sheet. Over on the left-hand side, we selected drink and then we have a list with all of our drinks. And here’s fruit smoothie. And then we have a list with all of our fruit smoothies. And if we look here, fruit smoothie matches the title of this list, fruit smoothie. So why isn’t it working? Well, let’s investigate. Up on top, let’s click into formulas and then let’s click into the name manager. And here we see all of our different named lists. And right here, we see the option for fruit smoothie. But one thing you might notice is there’s an underscore included in here. When you create a named range and it includes a space, it’ll automatically insert an underscore in. So, let’s close out of this. Now, when someone selects fruit smoothie, it’s looking for a list with a space in it, but we don’t have a list with a space in it. We only have a list with an underscore in it. So, we need to update this. Let’s remove the space and insert an underscore. That way, when someone selects this option in the dropdown, it’ll then match the name of that list. Again, in the name manager, it has an underscore. And just to make things really clear, let’s also just insert an underscore here. Doesn’t really make a difference, but that way we know there’s a matching list for this item. Let’s now jump back into the order form. Right up above, let’s now click on the dropdown with the type of drink. And over here, let’s select the option for fruit smoothie with an underscore. Now over here, let’s come to this other dropdown. And here I have my different flavors, vanilla, chocolate, and strawberry. And come to think of it, I don’t know if these are smoothie flavors. This might be more of a milkshake, but hey, here we have all of these different options. Now, one thing you could do is you can also copy and paste this down to multiple rows within say an order form. Over here, I’ll copy or at least select all of these. And over here, I can now drag it down. So, let’s drag it down to a few more rows. Now, I don’t want these to all be filled in, so I’ll select all of these cells and press delete to remove all those values. Now I have a dropdown list on each one of these rows. Over here, let’s select cookie. Over here, we could select chocolate chip. Over here, why don’t we go with a drink? I’ll select drink. Over here, I can now select, let’s go with a coffee. And over here, I see all of my coffee options. So that’s working exactly like I expect to. All right, well, hopefully that wasn’t too bad. If you would like to learn even more about Excel, I offer a beginner’s course that will teach you all of the fundamentals. That way, you’ll be able to tell the story behind your data. You’ll learn things like pivot tables, formulas, and functions, charting, everything you need to know to be effective in Excel. You could find out more in the description down below. To watch more videos like this one, please consider subscribing, and I hope to see you in the next one.