How to Make a Dynamic Geographic Heat Map in Excel

By Onur Yilmaz of Someka.net, edited by Jon Wittwer

Data visualization is a trending topic in recent years as we collect and analyze more and more data. Geographic Heat Maps provide a very useful technique for visualizing data associated with countries, states, and cities.

If you have tried the Power Map feature in Excel, known now as 3D Maps in newer versions of Excel, you may have been amazed at how easy it is to create a heat map for standard geographic regions. If you delve even deeper, you can learn how to create heat maps for custom boundaries.

There may be times when you'd like to create a geographic heat map without being limited to the functionality of the built-in 3D Maps feature. In this article, we will share how you can do this with custom shapes, formulas, and a little bit of VBA code. The result is a great-looking heat map that looks like this:

In this article we will go over every step to create your own Geographic Heat Map Generator in Excel for USA states. After you get the idea, you can implement the same for other cities and countries or even situations where the shapes might not even be geographical.

If you don't have enough time to make one for yourself, you can take a look at the Ready-To-Use Heat Map Generators in Excel on Someka.net

Ok, let's get our hands dirty!

The development of this tool consists of 4 main parts:

1. Create/Gather Visual Assets
2. Set up the Data Table and Name Shapes
3. Create the Legend and Color Scale
4. Automate with VBA

1. Create/Gather Visual Assets

First, we will need to find, create, or import separate shapes for each of the states in the USA. A full USA map doesn't work for this task. The shapes should be free-form and editable.

You can Google "editable powerpoint USA map" or something similar. These shapes are generally provided in powerpoint templates. You can find both free and premium maps. (For example: http://www.presentationgo.com/presentation/usa-editable-powerpoint-map/) After you get the shapes, copy or insert them into a blank Excel workbook. The shapes may be grouped together, so don't forget to "Ungroup" the shapes after you get them into Excel.

An alternative is to draw the shapes yourself in Excel using Insert > Shapes > Freeform: Shape. If you insert an image of a map into Excel, you can use the image as an aid in tracing boundaries as you create the shapes. You could also use Photoshop, Illustrator or other image editing software. Drawing your own shapes will just take longer.

After you have the USA states inserted into Excel, editable and ready to be painted, try changing the fill color of a couple of the shapes one at a time to make sure that works.

You will have something similar to this:

2. Set up the Data Table and Name Shapes

Create a data table similar to the one in the image below. It is important to include the "Order" column because we will use the number to link the data table to the shapes in the map.

The Shape and Text Box columns show the names that will be used for naming each of the shapes and textboxes in the next step.

Name each shape

Now we will need to "Name" each shape one by one. This is the most tedious part. We need to name these shapes because we will loop through them automatically with a VBA macro to change the fill color. It will take effort, but the results are rewarding.

• Select each shape (state) and name it using the name box to the left side of the formula bar. In the image of the shapes above, you can see that the selected shape is named "Shape 1394." Change that to the correct name found in the Shape column of the data table.

• Use a naming convention that is easy to loop through with VBA. For example: usmap1, usmap2, usmap3.

Create text boxes for each shape

• Create a text box (Insert > Shapes > Textbox) for each state and place the text box over each state (or for small states, place the text box nearby and use a leader line to point to the state). In the end you will have as many text boxes as your state shapes. Tip: Format the first text box the way you want (using whatever placeholder text you want), and then copy and paste the text box.

• Name the text boxes ustext1, ustext2, ustext3, etc. the same way you did for the states (select the textbox, use the Name Box to name it, etc.)

• The text box names should correspond with the state names as shown in the data table.

Creating and naming the shapes and text boxes is the most tedious part of this process. But, now you have 50 state shapes and 50 corresponding text boxes placed on each state. We are ready to proceed to the next part.

3. Create the Legend and Color Scale

Now we will define our legend and color scale and write formulas that our macro will use to update the colors and text values in the map.

Define your Legend

The legend is used to define the data ranges used for the color scale in the heat map. There isn't a limit to the number of divisions in your legend. However, using too many different colors may be confusing.

In this example, we will use the following 5 divisions for population in our legend:

Define the Color Scale

You can choose any color scheme you want, but it is common to use different shades of the same color (for a monochromatic color scale).

Important: After editing the fill color of the cells in your legend, name each of the cells color1, color2, etc. by selecting the cells in the Color column and using the Name Box to enter the names. If named properly, when you select cell K3, the name color1 should appear in the Name Box.

4. Automatically Update the Map Using a Macro

Before we create the VBA macro that updates our map, we will create a small helper table that the macro will use. We need to create formulas that will return the data value, text value, and color based on a chosen Order number.

The following image shows the helper table and the formulas that are used to return each of the values.

When you (or the macro) updates the Order # to 5, the formulas create the names of the shape and text boxes using text concatenation (&). The population and state abbreviations are grabbed from the data table using VLOOKUP. The color is determined using the MATCH function, which compares the data value to the Min values in the Legend.

Named cells make VBA macros more robust, so we will name the cells in our helper table as follows: N3:actorder, N4:actstate, N5:actstatevalue, N6:actcolorcode, N7:acttext, N8:acttextvalue.

After creating your helper table, manually change the number in the Order cell to make sure the formulas are working properly.

Create the Macro

Now we are finally ready to create the macro that will automatically loop through each shape and text box to update our heat map.

To create the macro, open the VBA window (Alt+F11) and insert a module and copy/paste the following code into the module. If you aren't familiar with how to create a macro using VBA, then you may want to review the article Create a Macro at support.office.com. You can use the macro recorder to record a macro named "Paint" which might just involve selecting a cell in the spreadsheet. You can then open the VBA editor to replace the code in your recorded macro with the following.

Sub Paint()
  Dim i As Integer
  For i = 1 To 7
    'First, paint the state shapes
    Range("actorder").Value = i
    ActiveSheet.Shapes(Range("actstate").Value).Fill.ForeColor.RGB = _
    Range(Range("actcolorcode").Value).Interior.Color

    'Second, update the text boxes
    ActiveSheet.Shapes(Range("acttext").Value).Select
    Selection.Text = Range("acttextvalue").Value
    
    '(Optional) Format the text boxes
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.Transparency = 0.3
    Selection.ShapeRange.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
    Selection.ShapeRange.TextFrame2.TextRange.Font.Shadow.Visible = False
    Selection.ShapeRange.TextFrame2.MarginLeft = 2.5
    Selection.ShapeRange.TextFrame2.MarginRight = 2.5
  Next i
  ActiveSheet.Range("pntOrder").Select
End Sub

Add a Button to run the "Paint" macro

In the Developer tab, go to Insert > Button. When prompted to assign a macro to the button, choose the "Paint" macro you just created.

If you are lucky, the macro will work perfectly the first time. If it doesn't work the first time and the debug tool doesn't help, check that all of the cells referenced in the macro are named correctly (including the color1, color2 ... cells).

Remember, if you run into problems or you don't want to spend time creating your own heat map generator, you can get a ready-to-use USA Geographic Heat Map Generator Template via Someka.net.

Geographic heat maps are great tools to visualize country/state data. You can use them in your presentations, reports and they look really cool! You are not limited to just USA maps. With the same logic explained above you can build different geo heat maps for other countries as well.

Editor Comments (by Jon Wittwer)

In the process of editing this guest post by Onur, I created a very simple generic example using custom shapes. The point was to show that you don't need to be limited to standard geographic boundaries. Although this post requires some intermediate Excel knowledge, I was very pleased with how simple it was to create my own heat map using Onur's process.

If you'd like to analyze the example file, you can download the file I created below.

Download the Example File (.xlsm)