Create a heat map to visualize geographical concentrations of data

 

by Kara Hiltz

Application: Microsoft Excel 2016/365

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201806/heatmap.zip

There are a lot of great ways to use the new 3D maps feature in Excel 2016 and Excel 365. We’ll show you how a heat map can provide a great visualization for geographic data so you can compare values in seconds and see the “hot spots” on your map.

To create a heat map using the 3D maps feature, we’ll:

  • Prepare our data by making sure the data range includes a way for Excel to identify geographic locations.
  • Create a 3D map using the heat map formatting option.
  • Tweak the map to make it easy to view and user-friendly.

There are so many options for transforming Excel data into meaningful visual representations. The new 3D maps featured in Excel 2016 and Excel 365 take data presentation to a whole new level when you tie data to geographical locations. As shown in Figure A, you can create a heat map that allows others to quickly compare high concentrations of data with low concentrations of data.

We’ll show you how to create a heat map for geographical data that will impress your coworkers and supervisors. The best part is that it won’t take much time out of your already busy day.

Download: If you would like to follow along with us, just download and extract the file OnlineSales.xlsx from the URL given at the beginning of this article.

IME18601A

A:

Our heat map shows where in New York State we’ve gotten the highest amount of online sales income.

Prep your data

When it comes to preparing data for a 3D map, you’ll want to keep the data basic. You’ll also need to include a column of geographic location data; Excel can use city/state, Zip codes, or longitude and latitude to pinpoint locations on its map.

In our example, we have only two columns of data, as shown in Figure B. The first column identifies the Zip code from which an online order was placed (and this column is formatted as the Special number format for Zip Code).The second column shows the total dollar amount of the order. The heat map we’ll create will give us a visual representation of which areas in New York State have generated the highest amount of income through online sales.

Caution: We found that Zip codes can get tricky when it comes to using them for the 3D map feature—especially Zip codes with leading zeros. Even when the Zip codes were formatted as Zip codes, the 3D map feature had a hard time identifying them correctly. We’re hoping Microsoft will fix this issue in a future update. Otherwise, we just had to avoid leading zeroes, which may not always be possible.

IME18601B

B:

Our data uses Zip codes to identify geographic locations of our online sales.

Create your 3D heat map

Our data is ready; it has a way for Excel to match our sales totals to locations. Now it’s time to access the 3D maps feature.

To add your data to a 3D heat map:

  1. Select any cell within your data range.
  2. Go to the Insert tab and, in the Tours area, click on the 3D Map button.
  3. In the Launch 3D Maps window, click the New Tour button at the bottom of the window. (If this is the first time you’ve opened 3D Maps in the workbook, Excel may take a few moments to load the program.)The 3D Map program opens in a new window and displays the default map Excel created based on your data, the Field List[...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage