Author: Oscar Cronquist Article last updated on March 04, 2020

heat map using pictures

I made a heat map calendar a few months ago and it inspired me to write this article. The heat map calendar changes the background color of each cell unlike the technique used here where I change the brightness of each picture based on numbers from a data set.

The drop-down list below allows you to change statistics, it contains three items: Population, Area and Population/area.

The map changes automatically when you change the value in the drop-down list, the workbook contains Event code that is run when the value in the drop-down list changes.

This article was written before Excel had map charts. I recommend that you check out map charts if you own Excel 2013 or a later version.

Instructions

Step 1 - Find and copy a map

800px Blank map of the United States

  1. I found a map at the Wikimedia commons website.
  2. Right-click on the image you want to use and click "Copy".
  3. Paste it to your favorite image editing software.

Step 2 - Copy each state/country/territory

heat map using pictures1

This step, step 3 and 4 are somewhat tedious if you have many images to copy.

  1. Select the "Magic Wand" tool. I made this in paint.net.
  2. Tolerance : 0%.
  3. Click on a state.
  4. Copy the selected area (Ctrl + c).

Step 3 - Paste to Excel

  1.  Start Excel.
  2. Paste the picture to a worksheet.
  3. Select the picture and name it using the name box located almost at the top left corner, see image below.

heat map using pictures2

The name is important, we will use it in a macro to change the brightness.

Step 4 - Select a transparent color

  1. Double click picture. This takes you to the "Format" tab on the ribbon.
    heat map using pictures3
  2. Click "Color" button.
  3. Click "Set Transparent Color".
  4. Click a "white" area on the picture.

Repeat step 2 - 4 and copy all states to Excel.

Step 5 - Organize pictures

800px Blank map of the United States1

Click and hold on a state and then drag to the location you want. You will se something like the image above when all states have been copied to the worksheet.

Step 6 - Create a group (optional)

Make a group of all pictures and you will be able to resize the entire map in one single step.

  1. Select all pictures. Tip! Use the "Selection pane" on the "Format" tab.
    heat map using pictures5
  2. Right click on a picture.
  3. Click Group | Group.
  4. Size handles appear when you select the group.
    map chart size handles
  5. Click and hold on one of the handles then drag with the mouse to resize the group.

Step 7 - Create an Excel defined Table

heat map using pictures6

I downloaded data from wikipedia and created the Excel Table above. An Excel Table has a few great features, one of them is that they use structured references which makes them really easy to reference and work with.

Here is how to convert a data set to an Excel Table:

  1. Click on any cell in your data set.
  2. Press shortcut keys CTRL + T and a dialog box appears.
  3. Click checkbox if you data set contains header names.
  4. Click OK.

The data set changes it appearance and is now formatted differently, this tells you that you now have an Excel Table.

Table column Column1 contains a single value. The brightness property is ranging from 0 to 1. 0 - darkest and 1 brightest. It is hard to see pictures with a brightness ranging from 0.5 to 1 so I am using values from 0 to 0.5.

We need to create values between 0 (zero) and 0.5  based on the data in one state compared to the maximum and minimum value in the same column, the formulas are shown below.

Formula in table column %:

=1-([@Population]/MAX([Population])*0.5+0.5)

Excel Tables uses structured references which are different than regular cell references. [@Population] is a reference to a value in column Population on the same row as the formula. [Population] is a reference to all values in column Population.

The formula above calculates a value between 0 and 0.5 based on the ratio of a given state's population compared to the largest population of the values in column "Population". The formulas below calculate a ratio using the values in column "Area sq km" and "Pop / km".

Formula in table column %2:

=1-([@[Area sq km]]/MAX([Area sq km])*0.5+0.5)

Formula in table column %3:

=1-([@[Pop / km]]/MAX([Pop / km])*0.5+0.5)

What we now have done is indexing values, I have made a post about indexing data before:
Compare your stock portfolio with S&P500 in excel

Step 8 - Create a drop down list

heat map using pictures7

A drop-down list allows you to control which values the user can choose from, they are easy to create:

  1. Select cell D26.
  2. Go to tab "Data" on the ribbon.
  3. Click "Data Validation" button.
  4. Select "List".
  5. Source: United states of America,Population, Area, Population / area.
  6. Click OK.

There is also a hidden formula in cell F26. The following formula calculates a value that the event code below uses.

=MATCH(D26,{"United states of America","Population","Area","Population / area"},0)*2

These steps demonstrate how to hide the value in cell F26.

  1. Select cell F26.
  2. Press CTRL + 1 to open the "Format Cells" dialog box.
  3. Select Custom, see image below.
  4. Type ;;;
  5. Press OK button.

heat map using pictures8

The value in cell F26 is still there but you can't see it. The worksheet looks cleaner with this setup.

Step 9 - Change picture brightness for each picture

map chart event code

  1. Copy code below. (CTRL + c)
  2. Right click on sheet name, see image above.
  3. Click "View Code".
  4. Paste code below to code window. (CTRL + v)
    map chart event code2
  5. Exit VB Editor and return to Excel.
'Event code is executed when a cell is changed in worksheet "Map".
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and declare data types
Dim r As Single, c As Integer, Arr() As Variant

'Check if cell's address is D26
If Target.Address = "$D$26" Then

    'Populate variable Arr with values from Excel Table "Table1" in worksheet "Table"
    Arr = Worksheets("Table").Range("Table1").Value

    'Iterate through values in a given column based on the hidden value in cell F26
    For r = LBound(Arr, 1) To UBound(Arr, 1)

        'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
        With ActiveSheet.Shapes.Range(Arr(r, 1)).PictureFormat

            'Set brightness from value in array variable based on row and column number
            .Brightness = Arr(r, Range("F26"))
        End With
    Next r
End If
End Sub

Download Excel file


US-map.xlsm

If you like maps, check out this post: Use a map in an Excel chart

Recommended links