How to Easily Create Multiple Dependent Drop-Down Lists in Excel-2021

In this step-by-step tutorial, first we learn how to create a basic drop-down list in Microsoft Excel. Next, we look at how to create multiple dependent drop-down lists. With multiple dependent drop-down lists, the second or the subsequent drop-down list adjusts based on the selection in the first drop down list. This is helpful to validate data entry.

Hi everyone I want to show you,

         (How you can create multiple dependent drop-down lists in microsoft excel).

So, what does that even mean and why would you ever want to use something like this well.let's say you have people entering data into microsoft excel to make sure people don't make data entry errors maybe,

   you want to include a drop-down list that'll make it as easy as possible and the option that someone selects in that first drop-down list will influence.

   what options are available in a second or a subsequent drop-down list,otherwise let's check this out here. I am in microsoft excel and management at the Martin cookie company they do a good job of keeping employees busy,but they've requested that,I pull together an order log an order log is something that we fill out every time we get an order here.we enter in the sales person.

   who drove the order and then,we also enter the associated customer now.I want to make sure that when we have our employees fill this out that they enter it as accurately as possible.

so,i could have people just manually type it in and here we'll enter in the sales person martin,but undoubtedly what happens all the time is people make mistakes and here we see that kevin was entered incorrectly.

Create simple drop-down list :

 

so instead. we can use just a simple drop-down list to make sure that people enter the sales person name in correctly to insert a basic drop-down list.

    let's go up to the top tabs and click on the one called data over here under the data tools category there's an option called data validation click on that this opens up a data validation prompt and right here. we can set the validation criteria and currently this cell is set to allow any value at all and that's why i was able to enter martin with a c.

so instead i'll click on this drop down list and let's select list right here.

   I want this to be a drop down list.i'll select that and next i have to indicate what is the source of this drop-down list now. if we look over here here i have a list of all of our different sales people at the Martin cookie company.

   so for the source i'll simply highlight these three cells and then i'll click on ok back on the sheet i can now see that i've successfully added a drop down list here.

   I have a drop down list icon and when i click on this i can see all of the names of our different sales associates now.If i try to enter a name of someone who's not on the sales team let's say for example :

                adele here we'll get this nasty error message telling us that it doesn't meet the data validation criteria and of course it doesn't adele no longer works here.

 she tried to sell our secret recipe here.i'll click on cancel and i'll go back to the drop down list and let me select Martin now that we've created a simple drop down list next.

 I want to show you (How we can create a dependent drop down list)

dependent drop down list excel :

Create dependent drop-down list :

so here. If we look over we have all of our different sales people and then each sales person at the Martin cookie company has the customers that they're in charge of so here you can see i have the heaviest workload.i've got a lot of customers then oliver has some customers and ava has some customers.

so,I want to set it up.so when i select a sales person over here.I get another drop down list in the customer cell that's dependent on the sales person.

    So,How do we do this well let's move over to column h and we're going to build our list right here under the header filtered list and first off. I want to get all the customers back who are associated with the sales person selected here and,

    we're going to use a function called x lookup with x lookup you can pass in a name of a salesperson. who you're looking for and it'll send back all of the associated customers it kind of works like magic to use x lookup let's go up to the top left hand corner and let's click on the insert function icon this is by far the easiest way to enter a function this opens up a prompt.

where we can now insert a function and right up here.

excel dependent drop down list vlookup :

Use XLOOKUP function :

Let's search for the function called xlookup type that in and then click on go and let's select a function right down here then, click on ok we can now.

   pass in arguments to the x lookup function now. over here we see that some of them are in bold and some of the arguments are not in bold the ones in bold are required and those are going to be the only three that,
   we use and first we need to enter in a lookup value now. once again i want to get all of the customers associated with this sales person.

so, I want to look up the customers for Martin.
so for the lookup value i'll select Martin right here, next i need to define what is the lookuarray.
     so,what does that mean well. I want to look up Martin and i want to look it up against all of our different sales people. so right here i see that Martin  oliver and eva are our three sales people.

so that's going to be my lookup array now.once it finds a match so here it'll look for Martin it'll find kevin right here and now. I need to indicate well what should it return.

so once it finds Martin.I want it to return all of these different customers.

so for the return array i'll simply highlight all of the different customers down here.i'll even include a few additional rows
at the bottom.
   just in case we had some additional customers in the future now.
 
once again these additional arguments are all optional
and, I don't have a need for them. so i'll simply leave them blank we're all set now,on entering our function so let's click on ok and check that out.
  I now have all of the different customers associated with Martin  listed out right here.
   when i click into the very top cell here right up in the top left hand corner.once again.I can review the function that i entered.

If i click into the second row here, i see the exact same formula but this time it's ghosted or it's grayed out the actual formula, itself sits here in this very top cell and the results that it returns spill over into all of these other cells.

So, if i want to go back and modify the formula again.I have to go into this very top cell now,that i have this list with all of these customers how do we turn this into a drop down list well it's, just as easy as the drop down list that we created before over here i'll click in the cell under customer.

let's go back up to the top tabs. Click on data and then let's click into data validation and right here let's set it to a list
type right here i need to define the source of this list and here i'll click in the very first cell of this array and because this is an array that i'm passing back there's one special thing that we need to do i need to add the hash symbol or the pound symbol and this will tell excel that,we don't just want to return this one specific cell.

    But, we also want to return the entire spill right here.

Create drop-down list with XLOOK UP output :

Once you enter that in let's click on ok,and that successfully added a drop down list for customer here. i'll click into customer let's click on the drop down list and i can see all the different customers that are associated with Martin  over here i can select a different sales person let me pick
oliver and here. i'll click and we can see all of his customers right here.
  Now of course we have all of these zeros at the bottom
and what are those. when we set up our x lookup earlier for the return array we selected this entire area.
  so that's why we're getting the zeros back with kevin i have four zeros down here and with oliver.I have even more so how do we get rid of these zeros.

we're going to add an additional portion to this function to get rid of all of those with this cell selected let's go back up to the top here and in front of x lookup type in the function unique then,
    we're going to open the parentheses and close the parentheses to customize the unique function. let's put our mouse over the unique portion of the formula and then let's click into the function helper this opens up the function helper and here.
     I can see the array that i pass in and this is the x lookup that,we entered earlier the x lookup returns all of these different customer names and that's exactly.what i want so i'll leave this as is we could skip over by column and right at the bottom it says exactly.
   
  once we only want to include values that appear one time the zero appears more than once. So here if we type in true that'll remove all of the zeros next.

let's click on ok and here now.

Use UNIQUE function :

I can see an updated list for oliver and,when i click over into customer here i'll open up the drop down list again and now.
    we no longer see any of those zeros now one of the really neat things is here i'll take dollar captain from Martin and maybe we're,just balancing out the portfolio of customers
i'll move this customer over to oliver here again.
   I'll click on the drop down list and here you see that it's been automatically updated with dollar captain.
   I can now jump over to a different sales person let's take Martin  as an example :
     I'll move back over to Martin and here.when i click on my drop down list here.
   
you'll see that this list exactly matches my list of customers of course one of the downsides right now,is this list in the drop down matches this list exactly and it's not in ascending or descending order.

Use SORT function :

So, it's going to be a little bit more difficult to find the values that i'm looking for now. i could come over here and i could sort all of these lists but, let's say i'm moving items around and i don't want to have to go back and sort instead.   
   I could add sort to the function that we used earlier here. when i click into this cell again.I can see the function that we added up above within this function here.
   I'll add one more function and i'll type in sort here. i'll open the parentheses and then,i'll close the parentheses at the end.
   once again i can click on to sort and i could click into the function helper this opens up the function helper and here. I can see the array that i'm passing in and once again the array is just this list that we saw earlier over here.i could decide.

Wrap up :

How do i want to sort it do, i want to sort it in ascending order descending order.

I just want it in alphabetical order. So i'm not going to make any other changes,but i just wanted to show this so you
know that, you can also customize the arguments of this function.
    once you're all done let's click on ok now,that i've added sort i can come over here and let's once again open up the drop-down list and now.
     you see that my drop-down list is in alphabetical order.
so the order doesn't match what appears here, but this will make it a little bit easier for our employees to find alright well let me know down below in the comments was this easier than you expected it to be to see more posts.