Create a Geographic Heat Map

hi this fandom sure to create a basic Geographic heat map and this is using Excel 2016 now in this particular version of Excel it's called the 3d map in maybe a previous version Excel Excel probably Excel 2013 it was called power map well you think about this more of a visualization feature if I wanted to look at some data this is data from power stations off of a Microsoft site this is some sample data that they provided and I've wanted to do some analysis I could probably put this into a pivot table alt NV create the pivot table and maybe I want to look by county and by capacity of megawatts these are my values let's sort this not by the alphabet but by the highest to smallest and we see Jefferson County Mirror Maricopa County are kind of the big hitters here now if I wanted to see this visually I can put this into a chart let's just take the first chart he gives us click OK spread this out and this is our basic chart to kind of give an idea so there's another way to do that and of course that's through a geographic heat map so what would that look like let's take a look so here is that same set of data we've got our county and we're looking at the county and we're looking at the capacity of megawatts now you can see here it's given us a visual heat map of which counties have the most megawatts right so it's summed it up in the red you can see in California here you can see in Arizona some parts of Texas here so this is just another way to visually chart out something and looking at through a geographic map map of the United States in this instance so I'll show you how to create something like this so here I have the data this is the same day that I had in the previous sheet and I just kind of put it into a new sheet and some things to think about when you want to use the 3d map feature in Excel 2016 is there are some specific formats that it can use to plot out the map includes latitude latitude longitude street addresses city County State and province zip code postal code country and region you can see here I've got a couple columns that have that kind of data we have got state we've got County so what this does is it goes online and it kind of figures out using Microsoft's mapping utility Bing to kind of plot this out now what it does is the power map automatically detects this and matches the columns of the data and kind of puts it into the geography fields and this process they they call it geocoding and there are conflicts that could occur during this process for example power map may show some error messages that let you know couldn't map out some of the geographic fields and this could happen when there are several locations and maybe in this and this example counties that in different states so you'll you'll notice that and I'm gonna show you later on that there's a specific county here or there's one of many but I'm gonna highlight one Jefferson County it shows up in different states there's not just one Jefferson County so if you were to do this if you wanted to be exact you probably wanted to look at different column fields that will help you kind of further narrow down the location you know state county zip code latitude longitude those can help so let's get into how we create this it's actually pretty easy I've got my data here and all I need to do is go under insert and go to 3d maps and create a new map and it's gonna take a while to bring it up look and it's kind of picked up the location so it's kind of picked up County and state as the location and if you had other things in here like zip code you'll probably put that in there now by default it uses the stacked column chart so you can see that in the data there's different there's different visualizations we can use stat column we've got this clustered column we've got these bubbles and of course for this particular video we're gonna do the heat map so I'm gonna select heat map and now after that's changed you notice that things have changed here if I if I had to default here it had height and category time we won't go over those but for when I do the heatmap it changes to value right and so the only value I really need in here is the the data value for the megawatts right so we're adding something where it can count or sum or average and that's something that should have a numerical value in there so cap has the capacity megawatts that does so I'm gonna select that let me close this field list this is basically something that we can add we can kind of put in here too but let's not have this there so you can see that it's kind of built it in already this tour thing we don't really need to worry about this because we're not going to do a tour what this happens is you can put different scenes here one two three four five and you can actually kind of almost make a slideshow out of this but this is not the extent of this video we just want to show you how to create a heat map so I can move the the earth around a little bit so I can get a better view of the United States here I can kind of make this a little bit smaller just click the plus sign a minus sign to get into that zoom in zoom out and what I can also do is I can have if I wanted to flatten out the earth I can flatten it and make it kind of a little bit more let's round it less curvy right so let's kind of make this a little bit smaller and so I have my little heat map here if I wanted to put some additional things like some some text here I can add a text box and I can call this megawatt usage in the US all right click create I can make this a little bit bigger and put this up here maybe maybe put this one down a little bit here right maybe make this a little bit smaller so there's there's a lot of things you can do here in terms of just kind of initially getting that set also who knows earlier I also had a bar chart you can actually also put a bar chart in here if you wanted to if I click on that to teach art you can have that here and I can move this over here and it gives you some additional data points and you know if you wanted to in addition put a little stacked column chart here you can you can move the the scroll bar here it gives you a little idea of how many counties are included in there and I can close this to make this a little bit bigger so it gives you a lot of different ways you can see your data not only can you see your data with a column chart you can see your data mapped out in a nice little heat map and it'll give you some idea of which areas have most usage also on the kind of nice here is we have some map labels if we notice that if I click map labels here it'll label out different parts of the country right okay Oregon Idaho Nevada etc so there's a lot more that you can actually go into this but this is just how you can create a basic heat map so you don't have to keep this in Excel you can also take this out and put it into our PowerPoint slide what you can do is just click on that do a screen capture and go into something like PowerPoint let's go into PowerPoint so here I'm here in PowerPoint it created a screen capture I just go back building home and click paste and you see I have my map here let's make this a little bit smaller all right it kind of made it a little bit big so I can just put that in here make that a little bit smaller there and basically it's just a screen capture let's make this zoom it back in and we have our heat map now that's kind of a nice feature you can just save it to the clipboard and copy it into PowerPoint so if we wanted to change things back we can go into our layer plane or Larry pane and maybe we want to do bi-state I don't know why we wanted to do bi-state you don't really get that good good visualization if we do it by state but but we'll go by county here in this particular example and it gets a nicer visualization of the different parts of the United States where megawatt capacity is utilized so that's an introduction on how to create a geographic heat map using the 3d mapping feature in Excel 2016 it's called power map in Excel 2013 so I hope that helps thanks for watching Oh [Music]

Channel: Doug H

Views: 12,620

Rating: 4.9587631 out of 5

Keywords: heat maps, learn excel, basic, beginners, excel youtube channel, windows, heat mapping, postal code, desktop, msoffice, tutorial, country, computer, tip, formula, easy, latitude and longitude, geocode, simple, 101, microsoft, how-to, gis, map, data visualization, state, heat map, system, training, learn, dough, doug, zip, data viz, excel 2016, power query

Id: pW9kqqHgZyQ

Length: 9min 5sec (545 seconds)

Published: Wed Oct 10 2018