Data mapping is a critical way to visualize information. You can quickly and easily analyze your sales data in a geographic context, including sales territories, product distribution or delivery areas, and so on. The map brings a tangible dimension to analysis and strategizing efforts that might otherwise seem abstract, especially if you’re trying to make plans for the longer term.

It can be particularly useful to create a heat map to aid in these efforts. You can create this type of visualization in Microsoft Excel, but doing so comes with considerable limitations. Using the dynamic heat map feature in eSpatial will provide you with a much clearer picture of your data and help turn those numbers into plans of action.

What are heat maps and how can you use them to your advantage?

The simplest way to define a heat map is as a visualization of differences in density within a large geographical area. Density, in this case, refers to a quantitative data point you seek to examine – often, it means population.

A color scale assigned to the map, in that example, indicates greater or lesser population density, usually through darker and lighter colors. But for a sales team, metrics like the number of recurring customers or prospective customers would make more sense.

Heat maps generally fall into one of two categories:

  • Hotspot heat maps point out areas of high or low density without using many fixed geographic boundaries – i.e., looking at the whole U.S. without focusing specifically on differences in density between states.
  • Regional heat maps (or chloropleth maps) serve the same essential purpose but do focus on density disparities between defined regions. These could be council districts in a city, counties in a state, states or provinces in a nation and so on.

To glean the greatest possible insight from a map crafted using any heat map tool, you’ll need to analyze two or more data sets.

Knowing how many of your customers are purchasing on a frequently recurring basis as opposed to just a few times a year, for example, would be extremely helpful. Your heat map analysis in that case would involve annual sales figures and your primary customer database. You might ultimately find that frequent buyers are more common in certain areas than others, which will help you better allocate your team’s time and resources.

How to create a heat map in Excel

Create a heat map in excel in a few simple steps

A heat map in Excel can work for basic jobs. Here’s how to put one together.

1.Input and select data

Enter the data you aim to analyze into the cells of a new Excel sheet. Categorize rows and columns as needed. (For example, if you wanted to examine sales in each month of several calendar years, your leftmost column would denote the month row-by-row, whereas the headings of other columns would be different years.)

Select a data set by highlighting the appropriate cells.

2.Apply conditional formatting

In the Home section of Excel’s main menu, click the button labeled Conditional Formatting, located in Styles. Choose Color Scales from the drop-down and select the gradient that makes the most sense to you. (Many people choose the “green=highest/red=lowest” scale.)

Excel will automatically color the cells based on the values and gradient. If you want to change this, click on More Rules in the Color Scales menu. You can format certain cells within the data set and ignore others (e.g., focus on only above- or below-average values), change the scale type by specifying minimum and maximum value parameters, and more.

3.Install and use the Geographic Heat Map extension

If using a version of Excel from 2013 or later, you have the option to add an actual map (in the regional style) to the project you’ve just created.

In the Insert menu, click Store (above My Apps in the Add-Ins tab). Enter the word “map” into the search bar and Geographic Heat Map will come up. Click Add to install it. When it loads, click Get Started, then hit Select Data and highlight the range of data you want to examine (including headings).

Finally, choose the geographic map options available in the drop-down, the metrics being looked at in columns and rows, pick a color scale, add a title and hit Save.

Create a heat map in eSpatial - step by step

Using eSpatial is not much more complicated than a tool like Excel or a free heat map generator. More importantly, it allows you to pinpoint density variances in an actual geographical context. This can help you conduct more thorough analysis.

1.Upload your data

Select the Add Data function from eSpatial’s control panel, and then click Upload New Data. You can import any files in the CSV or Excel (XLS) format, take data from the software’s built-in library or draw it from Salesforce.

You must always upload boundary data from the eSpatial library if you’re making a regional heat map, regardless of what your other data sets are.

2.Choose your map style

Once the data is ready, your next step depends on whether you’re making a hotspot or regional heat map:

  • For a hotspot map, return to the control panel and click Style. This takes you to the Style menu. Select Heat Map, and then you’ll choose the data to be used, color scale, transparency percentage and radius.
  • If making a regional map, choose Analyze from the control panel. You’ll be given options for analyzing your data set. Click Regional Heat Map, and then choose the Points and Region data you want to look at (e.g., client accounts and U.S. states).

3.Create and tweak

Click Create if making a regional heat map – or simply exit the Style panel of your hotspot heat map – and you’ll see your newly generated map! You can easily change your heat map once it’s finished: adjust the color range, removing “color noise” around the edges (in a hotspot map) and more.

Which heat map tool is better?

Truth be told, this will depend on what you’re trying to accomplish with your heat map. It’s more a case of “different” rather than “better” and ideally, you’ll use Excel and eSpatial together. (The latter is designed to import XLS data, after all.)

With that said, eSpatial allows for much more detailed analysis due to its use of individual points on the maps you create. Additionally, the flexibility to choose between a hotspot and geographical heat map, based on the metrics you choose to examine, is an invaluable asset. Visualizing your data with our cutting-edge platform offers immense strategic and bottom-line benefits.

Find out for yourself by signing up for a free seven-day trial!

Eoin Comerford
Written by

Eoin Comerford

Mapping Expert Eoin is an eSpatial mapping expert with more than 10 years of experience in the field. He specializes in using mapping to help sales and marketing professionals target revenue growth, make cost reductions and improve customer service.