This Excel training video looks at using Power Map to visualize locations of auto accidents by type. Chris Dutton shows you how to make adjustments to the Power Map interface settings to better see labels and data layers of the heat map by accident type.
- View Offline
- Alright, so the time has come to actually build some Power Maps using our data. Let's start in the Auto Accident Locations tab, go into the insert menu, and find the Power Map option here. If you don't see the Power Map option, go ahead and check out support.office.com for more information. You may not be running a compatible version of Excel and even if you are, you may need to activate or download some add-ins in order to let this work. So, in the Map drop-down, I'm going to choose Launch Power Map, and this takes me to the Power Map interface.
So, I'm going to make a few adjustments right off the bat. I don't need the Tour Editor visible. So, I'm going to disable that view, and I'd like to look at Map Labels and a Flat Map. At this point, I can zoom in to the data, and then change the angle of my view so that I'm looking directly overhead. As you can see, there are already data points populated here and that's because Excel has recognized that there were latitude and longitude coordinates already in my data set. So, if you hover over any of these data points, you'll see that they have a unique latitude and longitude applied.
So, my Geography is already set here. Next up, I'll just click the Next button in the Layer pane. And, now this is where I determine what data I actually want to map out here. So, select Auto Accidents and it will add a legend for me which I'll actually close out and it will default to showing a stacked column here. I can change these options to show a bubble or a heat map which is what I want. So, I'm looking at the sum of auto accidents by coordinates as a heat map. So, the areas in red indicate higher frequencies of accidents.
Areas of blue and green indicate lower frequencies which is exactly what I want. Last but not least, I can change my Layer name to Auto Accidents and then go into my settings and change some of the layer options just to change the look and feel. So, what I'll do is change the heat map Opacity to 80%. That way, I can better see some of the map labels beneath the heat map. I'll also change the color scale to 80% as well. I'll leave the rest of the default settings as is. And, there you go, I've got my Auto Accident heat map.
Now, let's return to Excel, and do the exact same thing for Bicycle Accidents and Pedestrian Accidents. So, with the Bicycle Accident Location tab selected, I'm going to select all of my data here from A1 through D515, choose the map drop-down again, and then select Add Selected Data to Power Map. This will add the Bicycle Accident data into the same view so that I have two layers now. I've got my Auto Accident layer and Layer 2 which is my Bicycle Accident layer. So, what we can do is turn off Auto Accidents since that one's all set, zoom back in, and adjust the formatting for Bicycle Accidents to match the Auto Accident map.
So, all we need to do here is go into Geography which is already set. Again, it's recognized the latitude and longitude. Choose next, in this case I want to plot out Bicycle Accidents, close the legend, select heat map, rename the layer Bicycle Accidents, and then last but not least go into my Layer options, change the Opacity to 80 and the Color Scale to 80. So, there you go. Now we have an Auto Accident layer and a Bicycle Accident layer. Last but not least, we'll jump back to Excel, do the same exact thing for Pedestrian data.
So, I'll select all of my data from A1 through D326, select Map, Add Selected Data to Power Map. I'll turn off my Bicycle Accident layer, zoom right in, and then same exact process here. Here's my Pedestrian data. Again, latitude and longitude are already checked. I'll choose Pedestrian Accidents, close the legend, view them as a heat map, rename my layer Pedestrian Accidents, and then last but not least go into my Layer options and change the Opacity and Color Scale.
So, now I've got three layers in my heat map to show each accident type.
- Reviewing the dataset
- Standardizing location data
- Creating a new data source in a PivotTable
- Creating new Power Map images
- Populating Power Map images with INDIRECT
- Linking to map images with HYPERLINK