Inserting a geographic heat map in excel is the best way to show where things are. You can use it for showing sales by district or even making a report on your favorite team’s support in various cities. It does help that I have been taking GIS classes but you do not need any crazy expensive program to make this work. The add-ins here will allow you to create a basic heat map with just the resources available inside of Excel, and after that if you want more data you can always load up ArcGIS or something similar.
A step by step guide:
1) First off we have to get the resource files from MrData.com . When prompted for a username and password simply use test/test as a username password combo.
2) Now click the green up arrow in the top right corner, or hit ctrl+M to activate the menu bar at the top of your excel window and select: Add-Ins/MrData Map Tools for Excel. This will pop open a new tab on your spreadsheet with all sorts of geographic goodies we can use
3) Select either Bing Maps(US Only) or Google Maps depending on what you want to do. There are options here for Streetview, directions, satellite imagery and even elevation data. Once you have selected an option it will be available under this new tab at the top of your spreadsheet as well as some other functions that weren’t included in our past steps.
Now we’re going to use this new data to make a heat map. We can either use the Bing Maps List of US Cities and States or we can go ahead and import our own shapefile (which is what I did).
4) Open up ArcMap, or any other GIS software you have on hand. If you aren’t familiar with what a shapefile is I suggest reading some more about it before continuing. Make sure to add in some local shape files for places where your team has shown strong support such as home games, away games anything like that would be beneficial for showing some variety in your heatmap. Now we need to export this file into something excel will read. To do that open ArcMap again and select: File/Save As/Select File Type:Shapefile/Select Save as type CSV- Point (Comma delimited) and save this file as location.csv
5) Now we’re going to import the shapefile into excel using: Data/Get External Data tab. Select your csv then click on match table and point it directly at the location.csv that we just made in ArcMap. After you choose all of those options it will ask if you want to create a new worksheet, say yes because we’ll go ahead and do some more steps inside of this same one so it is best not to switch tabs after doing so many steps. Your map data should now appear under your spreadsheet, simply highlight everything by clicking the first and last cell(s), go to the data tab and select “Text to Columns” like in the image below. Now do the same thing with this new column (location) by highlighting everything within it, click on Data/ Get External Data/External Data Range. This time select your csv file again but choose only rows of data by clicking where it says “Only row of interest”, like in the image below.