Dependent Drop Down List in Excel Tutorial

hi everyone kevin here today 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 if you want to follow along with this video i've included a sample file in the description down below otherwise let's check this out here i am in microsoft excel and management at the kevin 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 kevin but undoubtedly what happens all the time is people make mistakes and here we see that kevin was entered incorrectly 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 kevin 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 kevin 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 kevin 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 so here if we look over we have all of our different sales people and then each sales person at the kevin 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 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 kevin so for the lookup value i'll select kevin right here next i need to define what is the lookup array so what does that mean well i want to look up kevin and i want to look it up against all of our different sales people so right here i see that kevin 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 kevin it'll find kevin right here and now i need to indicate well what should it return so once it finds kevin 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 kevin 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 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 kevin 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 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 kevin 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 kevin as an example i'll move back over to kevin 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 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 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 videos like this please consider subscribing and i'll see you next time [Music] you

Channel: Kevin Stratvert

Views: 572,891

Keywords: kevin stratvert, multiple, dependent, multiple dependent drop down lists, drop down, drop down list, drop down lists, drop-down, excel, microsoft excel, microsoft, excel drop down list, list, drop, down, drop down list in excel, tutorial, excel drop down list tutorial, how to, how to create drop down list in excel, kevin, stratvert, dropdown, dropdown in excel, create drop down, how to add, create a drop down, excel tutorial, data, data validation, creating, menu, create, add, in excel, lists

Id: vh2jdOQ5VLs

Length: 11min 10sec (670 seconds)

Published: Tue Jul 06 2021