🗺️Create a DYNAMIC Map Chart in Excel – Data Validation, UNIQUE, SORT and FILTER☝️

In this little lesson, we're going to be exploring the wonderful world of dynamic map charts. So what do I mean by that? Well, we're going to insert ourselves a lovely looking map chart based off of our data, and I'm going to show you how you can make it a little bit more dynamic using a data validation dropdown list so that when we make a selection from that list, the map chart updates. Oh, so much fun. Let's go. Now, if you've never ever created a map chart before, then you really are missing out. So let's start out by taking a look at a basic map chart so you can understand what I'm talking about if this is all new to you. Now, in order to create a map chart, we need to have geographical data. So that might be states, countries, regions, things like that. So I'm going to start out with just some basic information that shows sales data for different states in America. So in its most simple form, all we need to do is click in the data. We could put this in a table to make it a bit more dynamic. I'm not going to bother just to save a little bit of time because this is just a very basic example. But to create a map chart, we jump up to the insert tab. And then over in the charts group, notice we have a maps option. And our only option here is a filled map. Check it out. It zooms in on the US only, and it's showing me those sales represented as gradient values. And you can see based on the legend, the lighter the value, the lower the number, the darker, the higher. I can then go in, give my chart title. I could maybe choose to change this to a different chart style to make it look a little more bougie. But that is your basic map chart. And the good thing about these map charts is that they will zone in on the specific region. So if I'd put, instead of states of America, I'd put counties in England or provinces in Canada, then it will zone in to that particular country. And if you have just a list of different countries around the world, what you'll get instead is a world map. So it's pretty intuitive as to which region of the world your data is based on. So now we understand those basics. Let's dive into something a little bit more complex. Now over here, I have some more geographical data. I have some regions in the first column, countries, and then sales. And what I really want to do here is I want to be able to select the region. And I've actually forgotten to type that in the top. That would help, wouldn't it? I should have done that before, but there we go. I want to be able to select a region from the dropdown list and then have it pick out of my table the countries that belong to that region, the sales, and then I can create a map chart based off of that, which will update when I change the region in the dropdown. So we have quite a few different techniques that we need to employ to get it to do this. So we might as well get started. First thing I'm going to do here is I'm going to put this data into a table, Control-T, so that everything is completely dynamic. And if I add more records onto the bottom, my data validation list is going to update automatically. The next thing I need to do is to create the actual data validation dropdown list. So I want a list of the regions. And because my regions repeat in this table of data, I need to first grab a unique list of the regions. So I'm going to use the unique function to do this. So we just type in unique. We're going to select the regions, close the bracket, hit enter. And there we get a unique list. Now I also want to sort these so that they're in alphabetical order because currently they aren't. Now what I can do here is simply use the sort function. So I'm going to go up to the formula bar. I'm going to add sort onto the beginning. And the default for sort is simply to sort A to Z, which is exactly what I want to do. So I don't need to add anything else. Let's hit enter to sort those regions. Now that I have this, I can build my data validation. So up to data, into data validation, we're going to choose list and our source list is just here. Remember, you can hide that column once you've done this. You don't have to have that list there for all time. So now I can select regions from the little dropdown. And I am in fact going to right click and let's just hide those regions. Now the first thing I'm aiming to do here is to be able to select a region and then below have it produce the countries that are part of that region and the sales. That's the first step. So in order to do that, I need to add a little formula in here. So I'm going to insert a helper column. I'm just going to call this helper. And what I need to say here is equals. We're going to test the first one. And so if Australia is equal to what's been selected in the dropdown, then it's going to produce the countries. Now I need to lock this cell by pressing F4, let's hit enter, and I get a whole bunch of trues and falses. And we're now going to use these in a filter formula in order to get the result that we want. So over in country, let's say equals filter. The first argument here is the array. So we basically need to select the columns that we're looking to return, which in this case is country and sales. So I'm going to select these as my array. Remember, because I put this in a table, I'm now getting table references instead of the cell references. I then need to specify what I want to include. And this is where I select this true or false column. Because when it's true, it's going to include it. When it's false, it's not. I don't need to add anything else here. Let's close the bracket, hit enter, and check out what we have. Let's see if this is working. Let's change it to Europe. There we go. I'm just getting the countries that belong to Europe and those sales values. So now that we have this, we can create a dynamic chart based off of this data. So I'm just going to click in the data. Let's go to the insert tab. Let's go for filled map charts and check out what we have. It's showing me the entire Europe region and the countries that have been filtered are showing on the map chart. Now check out what happens if I change the region. Let's go for South America. It switches to South America and I'm now seeing that. So this is a really nice way of building yourself a dynamic map chart, which you could then maybe move to an overall dashboard displaying key metrics. We've got to see quite a few different techniques in there. You might not have been familiar with unique, filter, sort, any of those things. So now you have a little bit of insight on those as well as how you can create yourself beautiful maps. If you enjoyed this video, then smash that old like button. Give me a follow. It really does help me out. It means I can continue creating these videos for you. That's it for now, guys. I will see you later. Bye bye.