 Hello everyone and welcome back to another tutorial. In this video, we're going to be looking at the Excel dynamic arrays So basically the new functions we have available to us in Excel for making a few scenarios particularly a great deal easier for us In this particular video, we're going to be looking at a dynamic drop-down list And as you can see we've got a couple of points we've marked here the parts we want to cover off So we want to use these new formulas to one give us a dynamic drop-down list So based on department so you can see in department. We've got a number of different departments Technology finance HR and business, but in our drop-down. We only want it to be a unique list So we only want each of those departments to appear only once and we'll be populating that here in column F Based on that value in column F. We want to then do a dependent drop-down list For application So by that what I mean is if we were to select finance in our first one here Then we'd only want to have one of these three or these three apps available in our second drop-down And likewise if we were to select the different departments such as business We would then only see the business of business applicable applications. So obviously this is Really important and a really helpful function to us in Excel And as you will see as you go through this the formulas or the functions we're going to be using to do this are so A lot easier a lot more straightforward than they used to be once upon a time You'd be doing line upon line Or maybe it's a bit of an exaggeration But you'd be doing better extents of formulas to get this same outcome But now we've got some four functions that allow us to do this very simply and straightforward Once we've been able to get those drop-downs, but then when they use a couple of other functions now available So we're going to use filter So that we can ensure that our lists are in alphabetical order. So as you can see for both of these various department, obviously you can see it's not in A to Z order and likewise with application It's sort of mixed all over the place Oh, sorry, I've tried jumped ahead So sort is the last thing we're going to do sort them A to Z and the third point here What I just messed up is to filter the list data So obviously making sure that we only have those values that appear once So what we'll do without further delay will jump straight into the video and get straight to it And I'll try my best to talk through the functions as we go through them and hopefully offsets keeps it all nice together So the first thing we need to do is we need to build out our unique list of departments for that All I'm going to do is do our new functional unique What you can see then and all I'm going to do is just select this first column The data in all this table. I have over here has been created as an Excel table If you're not sure how to do that, you can just go check out one of our previous videos I've touched on it before alternatively. I'll give you a quick tip. It's up here under this insert tab So very simple need to do but there you go There's a tip if you want to try and work out yourself else just go and check out some of our previous videos And you'll see straight how to do that Because it's a table all we need to do is hover above this column So you get this little black arrow select and you can see that that department column has been selected the unique function does give us a couple of Optional addition so we can add here so by column and exact once But we don't need to worry about those going to close our brackets hit enter and you can see that We now have a unique list of values and as you'd have noticed the it's been expanded So what I mean by that is I was just adding the formula into this row here row 15 and Dependent on the values that is then going to expand the results as required Good way to demonstrate that is if I was to add a new department on here. So let's say I Catering You can see that it's automatically populate in there for us and if I was to remove catering then it's been removed Oh, I've got need to make sure I delete that line when I do that Delete rows and you can see it's automatically removed for us So what it will do is it will expand or retract as required based on the values available to us and Just that one function of I really demonstrates the power that we now have available in these new functions in Excel So in order to create the drop-down list from these values here All we need to do is go into our The cell in which we want to have the drop-down and as you know because you'd probably checked that or you would have checked out our previous videos We didn't go into our data validation We're going to select the list option of list and when we come to do our source All we need to do is select the cell in which our range is going to start from so the first value there and Rather than select a range as we would have previously done All we need to do is enter a hashtag at the end of that range and what that hashtag does it enables The function or when it comes to data validation What it's going to do is we'll say right go to this reference of f15 and then Expand as required based on however many values are available there As simply as that So it's a lot more. It's very technical what it's doing But in terms of obviously the entry need to do for it to do that. It's very simple with that hashtag So what's we're gonna go? Okay, and Now when we select our drop-down We can see that we've got all of those options available to us there Now I'm just gonna add catering back in one more time And let's say we have catering Number one So that's ready And when we come into our drop-down you can now see it's been updated to now include catering So very simple and straightforward how to do but there you go very quickly. We've managed to add that first drop down this So the second one of dependent drop-down for application So we're going to be using a different function now and that function is called filter And what that allows to do is to filter the results in application here Based on the value that is in our drop-down. So it might help if I just select one of those to start off with so let's go for finance So I want my list to start here. What I'm going to do is get equals filter open brackets So this time I want this list here. So I want an array of There so I'm just hovered above that column Go to the next part. So I've got include and this is where I need to enter my criteria So I only want to include those where this column is Equal to the value in our first drop-down Close our brackets and you can see that's now expanded for us So for finance, we know that we've got these three apps here. And that's the three we have If we're to train train change the drop-down to business You can now see that we have the business applications only available to us now so that's that and Lastly, all we need to do as we did with this unique drop-down here If you go enter data validation as a list and our source is going to be the first or or the Starting point for where this range of being or the first row followed by our hashtag and Select enter and you can now see as this gets changed over here. So it's a technology I'll drop down will also get updated as well And you can see the values obviously replicated underneath here for what will be in that drop-down And I guess the best best or the best ways of practice for this is you'd probably have this information here The drop-down values Maybe on a separate sheet or hidden away from these drop-downs here Just obviously to make it easier and maybe easier on the eye for the user So they know what they're dealing with but that's how you do it So the next part we've done so we've ticked off the dynamic drop-down list So we know how to do one of those and we know how to do the dependent sub drop-down list And I guess to be honest two and three are kind of the same really Because we need to use that filter list data to bad populate our sub drop-down. So let's just mark those as both yellow So we know we've done all three of those So let's go like that just so we can track where we are So the last thing we need to do is to sort the list data So ideally we don't want these to be in random order. We want them to all be in order of a to z All we will need to do to do that is just make a simple update to our function So I was going to get beginning of unique here. I'm going to enter sort open brackets Unlike with Iodia function you can see we have a number of options available to us But the default value is a to z what I'm going to be using so all I need to do is close brackets Hit enter and you can see it's now sorted out perfectly in alphabetical order for us So it's worked perfectly so what business case from finance who resources and technology and lastly, we'll just apply that to the Dependent drop-down list as well. So we've got sort open brackets Close brackets hit enter and there we go. Sorry. I just said and push the wrong button Not push the wrong button, but some way to miss there, but you can now see that we have them organized as well So one last time. Let's select finance and You can now see we have our dependent drop-down Populated as well in perfect alphabetical order and let's do one more change here So let's go into business and you can see now the business ones applications are all in alphabetical order as well So we hope you enjoyed that that tutorial is a bit fast-paced and I sort of skip through quite a lot that quite quickly But I hope it is a pace that enabled you to a one understand the topic and also Yeah, basically just understand the topic without obviously making this video far too long If you do have any questions at all, please do leave a comment below and I will get back to you as soon as I can If you enjoyed the video, do please give the video a like So not only appreciate by myself as it shows me more content that you would like to see But it also does help that YouTube algorithm to help more people see our content And lastly if you is the first time you come across the channel or you've watched a few other videos Please make sure you subscribe to the channel and hit that bell notification button That way you'll be notified of all of our videos as soon as they come out So thank you very much for watching and we'll see you in the next video Before you go, don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always don't forget to subscribe and hit that bell notification button Thank you very much for watching