How to Create Linked Drop-Down Lists in Excel

hi I'm Jamie and welcome to teachers Tech  today I want to show you how to create a   multiple dependent drop-down list in Microsoft  Excel by using only a couple different functions   we're going to go through this step by step I  have the practice sheet that you can download   in the description and follow along with me let's  get started on creating this in Excel today now

there's different ways that you can do this I  actually have different videos showing different   uh functions and formulas to create this I'll put  a list of those down below and you can check those   out to if those work better for you in this one  here today that you'll be working through we're   going to be using the unique function and the  filter function so what we're going to do I'm   just going to give you a little demo here to start  with and what it means is when we have a dependent   dropdown list what I choose in here will influence  what I CH get over here so if I drop down here it   shows the different departments these different  departments are being pulled from the this column   over here so if I pick Finance here then my list  will change here so it will change on what I can   pick so there's only accountant in here so I'll  show you if I picked a different one if I went   to uh security and then drop down you'll see  that I get head of security or the guard so if   I was picking uh guard and then drop down I only  have one person that's listed as a guard but if I   was going to go back and pick maybe let's say  marketing and then I was picking pi marketing   assistant and then drop down notice that two  people came up for marketing assistant and then   I could pick one of them and then their salary  comes up based on what I picked so these were   all dependent upon what I chose before and that's  what I want to show you how to create today in   Microsoft Excel so the first step here is to be  able to pull out the individual Market names here

you can see that there's two sales here uh three  sales there's uh multiple marketing I don't want   it to be written each time I just want sales to  be once and finance to be once uh so I just want   to be able to have all the different department  name so what I'm going to do is use the unique   formula to begin with and I'm not going to put  it in this spot right here I'm actually going   to just put it to the side here and I'm going to  start my uh formula here I'm going to be putting   all my uh my unique and filters down in this row  and then I'm going to hide it after so you can't   see it uh so I'm going to go equals and then  I can start typing unique you can see as I I   start typing gets closer and closer here's unique  I'm going to select it all I need to do is Select   what I need to be searching and it's going to  be this right here so I'm just using a ranger   if you were using a table uh you would be able to  select that column uh by the column's name too but   I'm just using a range so I'm actually going to  just hit enter and I get my unique uh different   department names now this is what I'm going to  going be putting in Department over here and this   is where I need to use data validation and where  you find this is if you go up to the data tab up   top and then looking in the ribbon find your data  validation so I'm just going to click on it and   what we need to change it to is list so allow list  just like this and what is the list going to be so   I'm just going to click on this it's in this spot  here this is where the list is coming from and I'm   just going to hit enter and I'm going to show you  something I'm going to make a change to this so   it works better in a moment I'm going to hit okay  and watch this what happens so I drop down notice   it only says sales on this um what I could do I  could have selected everything so it would have   shown everything but what I like to do is and  I'm just going to click on uh data validation   again and open it back up if you add a hashtag at  the end it will always adapt to how many spaces   uh is in that list that it's pulling so if I hit  okay now and go back you can see that all of these   from here are now in my drop-down list here so  this is the first step that we're going to do so   the next step that I want to have happen I want  to be able to once I choose sales then I should   be able to only look within the sales job titles  that are there so it should look down this list   and they are repeats of different ones you can  see there's sales assistant and sales assistant   and sales manager so I should it should find two  different things so I am going to be using unique   but I also need to filter it and I need to connect  it to this H2 cell so let's go ahead and start our

unique function formula over here and to do this  I'm just going to put equals and then start typing   unique and I can see it pop up but right away I  need to uh add the filter function so if I start   uh typing filter and I need to add this and what  do I need to be looking at the array so I'm going   to be comparing uh this and I'm just going to  select the whole column here and then I'm going   to put a comma it's going to be uh comparing to  this one here but when this one equals what well   this is where I need to put the equals sign in  not the plus equals this spot here so I going   to look down C to see when this connects to here  and then that's the end so that's one bracket we   have to add another bracket because we have uh the  the two opening ones there too so I'm just going   to hit enter and you can see since I have sales  now it brought back sales manager and a sales   assistant if I drop down and go to marketing it  brought back these two now we're going to do the   same thing what we did before we are going to put  uh the data validation up here so   I go to this spot I click on data validation  and what am I going to use well I'm going to   use a list again and I'm what's my source going  to be so I can click on this and I'm just going   to click in this cell here and I'm going to add  the hashtag right away hit enter and hit okay   so now we should have this marketing manager  marketing assistant it's pulling from here so   if I change this to finance and look what we have  there should only be accountant and you can see it   reflected over here we'll make one more change to  make sure it's working security and now we should   have this drop down between head of security and  guard okay let's move to our next spot so now we   want the names to populate down here based on  the guard so like if we had this selected it   should just choose the one guard that we have the  name list so how do we do this well the names are   all unique already so we don't have to use the  unique uh function in this one we'll just use   a filter and connect it back to the uh H2 spot  or H3 cell that we already have so I'm going   to go back to my J column and I'm going to put in  equals and we'll use our filter here and what is   it that we're going to be needing well I'm going  to need this column right here and what's going   to be the next one it's going to be the job title  so it's when the job title is equal to what well   whatever is  in this cell right here uh so now I can go ahead   and just close that and you can see there's the  one name so let's see what happens up here as   we choose if we choose marketing and we choose uh  let's say marketing assistant we should have two   names right here uh so it's pulled the list so now  we have to use our data validation to put it back   into the drop-down list here so I'm just going to  select this cell make sure you're under data go   to uh data validation drop down choose your list  choose your Source it's just going to be this spot   here I'm going to add my hashtag hit enter and  hit okay so now I should get those two here so

we have one more step here that we want to add for  this last step I'm going to be using the V lookup   function in Microsoft Excel if you're unfamiliar  with this powerful function take a look at this   tutorial I have up above in the card that will  get you started on how to use it so our function   is going to go in this cell right here in H7 and  we're going to start this with our equal and we're   going to look up for v l and there it is I'm going  to select it so what are we looking up well we're   going to look up what name is in this cell right  here so I'm going to select it that's my lookup   Value Place my comma what's my table array well my  table array is going to be going from uh this part   up here the beginning of the names down to the  uh end of the salary here now our next point is   our column index now remember since employee name  is the First Column this is number one so this is   the first column in the array this is one two 3  4 so we're going to need to have the column index   number be four so I'm going to put in number four  like that and I'll put another comma and I want   it to be false because I want the exact match  and I'll just end this and hit enter and let's   try it out a few times so if we're dropping down  here and if I was looking in sales drop down see   the maybe I want to see sales manager and drop  down uh here and I can see Walter White gets $186,500 information coming up there and then  you have your uh multiple dependent dropdowns   working right through here and then you have  the V lookup function getting that uh salary   from reading this last cell so like I said  before there's many different ways that you   can create multiple dependent uh dropdowns in  Microsoft Excel I do have other ones I like   the X lookup one too you don't have anything  to hide in cells so take a look at that   one and I have some other ones too I'll put the  links to those as I mentioned uh down below and   in the cards I hope you found this method  of creating a multiple dependent dropdown   in Microsoft Excel useful let me know what you  think Down Below in the comments what else do   you want to learn in Microsoft Excel or other  technology products thanks for watching this   week on teachers Tech I'll see you next  time with more Tech tips and tutorials