🌍 How to Turn Data into Interactive Excel Map Chart

today i want to show you how you could turn your data in microsoft excel into a dynamic map at the kevin cookie company we have a ton of sales data by country but it's really hard to make sense of it it'll really help me visualize the data if i could turn that into a map and wow look at that it appears that india is one of our top markets it's a good thing that we greenlit those cardamon cookies you could also turn your map into a dynamic map this way you can update the map based on your selections lastly we'll also look at how you can turn your data into a 3d map this way you can fly around to visualize your data in different ways if you'd like to follow along today i've included a sample spreadsheet right up there alright let's check this out here i am in microsoft excel and here i have a performance dashboard for the kevin cookie company i want to add a map view to this dashboard that shows me visually where all of the cookie sales are occurring if you want to just build a simple dashboard in excel i've included a video right up above that'll walk you through step by step how to do that in this video we're purely looking at the mapping capabilities down below let's click on the worksheet titled basic map here on the basic map sheet you'll see that i have two columns of information in the first column we list out all of the countries where we sell cookies at the kevin cookie company and in the second column we have the associated number of cookies sold to be able to create a map you need some type of geographic information here i have countries but you could also use city names states or even zip codes along with that you need some type of data or values that go along with that geographic location and here i'm using number of cookies sold now unfortunately you can only have one other column go with that geographic location now right now looking at all of this data it's kind of hard to make sense of it it'd be a lot better to visualize it with a map so let's insert a map two insert a map go up to the top ribbon and on the tabs select the insert option in the center the first way to insert a map is via recommended charts when you click on that here in the second position you'll see the option to insert a map if we go back you'll also see a dedicated button for inserting maps here when i click on that i can insert a map and down below there's also the option for more maps however there are no more maps there's only one type of map that you can insert so let's click on this map and check that out i now have a map that shows me visually where we've sold all of our cookies the darker the color the more cookies we've sold and the lighter the color the fewer cookies we've sold i can now make modifications to this map if i click on the title i could give it a new name up above on the ribbon i could also choose a different style i could adjust the colors and i could even add in data labels if i'd like to so i have a few different options for customizing this map the great thing about maps is it'll adjust the view based on the geographic locations that you have in your data here if we click into basic map 2 you'll see that i have all of the different states in the united states here once again i'll go up to insert and all insert another map here this will now generate a map that shows sales data for cookies in the united states so here it optimizes the view just the geographic location that i have data for now that we know how to create a basic map let's take this to the next level and create a dynamic map back on my performance dashboard i want someone to be able to come in and select a region and depending on the region that someone selects it'll show a map just for that region all right let's click into the dynamic map worksheet and we'll walk through how to make this work here on the dynamic map worksheet once again i want someone to be able to come in here and they could select one of the regions here you can see all of the different regions that we sell cookies in once someone selects one of these regions i want this table down below to only show me the countries within that region and once it updates the table i also want a map just to show me that specific region to get started i want to create a drop down list here where someone can choose the region so here i'll select all the different regions we operate in and all copy this column here i'll paste it in over here up on the top tabs i'll select data and here all remove duplicates i'll click on ok and this leaves me with all of the different unique regions also i want this to be sorted in alphabetical order so right up above also within the data view here i'll click on a to z and that's now sorted all of the different regions next i want to insert a drop down list right up here with all of the different unique regions to do that also within the data view here i'll click on data validation this opens up the data validation prompt and here under allow i'll set it to list for the source of the list here i'll click on this option and i'll select all of these different regions this now looks good so i'll click on ok and here now i have my drop down list next i want to select a region from this drop-down list so here let's say i go with asia when i select asia i wanted to filter this table down below to just those countries within the asia region and to do that we're going to use the filter function but before i can use that filter function there's one other thing that i need to do first i need to know whether each one of these countries is within that region and i'll use a basic true or false statement to do that here i'll insert another column i'll right click on b and here i'll go down to insert over here i'm going to simply ask is this country in this region so here i'll enter an equal sign i'll select the region over here and i'll ask if it's equal to this value right up here i'm going to select this to an absolute reference i'll press the f4 key up here and that'll insert a dollar sign before the letter and before the number then i'll hit enter so here i can see that india it's true that it is within the asia region here i'll pull it down and now i can see whether every country is in that region so here i could see the united states is that within asia no that's false because the united states is in north america i'm now ready to use the filter function here i'll click into this table with the country and the cookies sold and by far the easiest way to enter a function is to use the function helper here i'll click on the insert function icon and over here i'll type in filter and then click on go here i'll select the filter function and then click on ok this opens up the function arguments and first it asks me for the array basically when i run this function what values do i want to get back well here i want the country and i also want the cookies sold so here i'll highlight country cookies sold and i'll highlight all of this data next it asks me what values do i want to include well i only want to include the values where it says true so here i'll highlight this entire column right here down below it asks me what to do if it's empty and that's an optional argument so i'll just leave that blank this all looks good now so i'll click on ok and check that out this now produces a table with all of the different countries within asia here i could go up to my drop down list and i could switch it to let's say europe or i could switch it to north america and here you see this table automatically updates here i'll shift it back to asia and now i'm ready to insert my map here i'll click within my data once again let's go to insert on the top ribbon and right over here once again let's click on maps and let's insert a map and look at that i now have a map that shows me cookie sales in the asia region but the really neat thing now is i could go up to my drop down list and when i click on this let's take a look at europe here i'll select that and the map automatically updates here i'll go back up to the drop down and let's shift to north america look at that so this map is now dynamic it'll update based on my selection in this drop down list this is now working exactly how i want it to work here i'll cut this map and i'm going to bring it into my performance dashboard i'll press control x and let's jump over to the dashboard view within my dashboard i'll press ctrl v to paste it and here i'll choose a position for my new map that's looking pretty good with the map on my dashboard but i also want to have the drop down on my dashboard i'll click into the dynamic map worksheet and here i'll highlight the region and the drop down list i'll press control x i'll go back to my dashboard and i'll paste it in right over here the drop-down list didn't transfer successfully so here i'll click on the drop down i'll go back up to data and over here i'll click on data validation here i see the source it's currently referencing this sheet so here i'll update the reference i'll click on this let me go to the dynamic map sheet here i'll highlight all of my different regions i'll click back on here and then i'll click on ok back now on my performance dashboard i could click on my drop down and here i can update the region and then the chart updates to reflect that region this dashboard is now looking pretty slick lastly i also want to show you how you can visualize your data on a 3d map you can then set up a scene you can fly around your data and you can even export it as a video so you could bring it into say a powerpoint presentation let's jump back into the basic map worksheet and then click into your data up above click on insert and then click on 3d map this opens up 3d maps and i can now visualize my data on a 3d map of the world over here on the right hand side it successfully brought in the country data but i don't yet have the cookies sold data in here right down below under height i'll click on the plus icon for add field and i'll select cookies sold and look at that i now have stacked columns showing me cookie sales around the world i can visualize this in different ways here i could also load a clustered column i could also load bubbles here i could click on a heat map or i could even select a region view so this is similar to what i saw in the 2d map in excel over on the right hand side i can also add categories you can even add the time dimension if you want to visualize how data changes over time you could also add other filters and other layer options one feature that i really like you also have the ability to animate your data on the map over on the left hand side where you can see all of your scenes on scene one i'll click on this settings gear right over here i can choose different effects i'll select rotate globe you can also set the speed and then i'll close this out now i could click on the play icon and this will now animate my data on the globe now that i've set up an animation up in the top left hand corner i can even create a video with that animation here i could choose the quality level i could also choose a soundtrack and then i could export this video so i could take this video and i could say embed it in a powerpoint presentation or i could bring it anywhere else i can bring videos all right and that's how easy it is to create a map view of your data in excel and finally i can visualize how our cookies are selling around the world to see more videos like this please consider subscribing i'll see you next time [Music]

Channel: Kevin Stratvert

Views: 46,550

Rating: 4.96982 out of 5

Keywords: kevin stratvert, map chart, excel, excel map chart, excel maps, excel map, map excel, map chart excel, microsoft, interactive map, drop down, list, 3d, 3d map, data, maps excel, how to, tutorial, power map, world, us, united states, zip, state, country, region, microsoft excel map, plot data, dashboard map, interactive dashboard, heat map, chart, region chart, heatmap, geography, geographic, county map, xls, xlsx, bing map, bing, visualize, dynamic map, interactive, dynamic, data mapping

Id: ZJWZ9udqFAU

Length: 12min 53sec (773 seconds)

Published: Wed Aug 25 2021