I made a heat map calendar a few months ago and it inspired me to do this post. The heat map calendar changes background color of each cell unlike the technique used here where I change the brightness of each picture.

 

heat map using pictures

Here is how I did it:

Step 1 - Find and copy a map

  1. I found a map at the Wikimedia commons website
  2. Copy the map and paste it to your favorite image editing software.

Step 2 - Copy each state/country/territory

heat map using pictures1

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

Step 3 - Paste to excel

  1.  Start excel
  2. Paste the picture
  3. Select the picture and name it using the name box

heat map using pictures2

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 your regions to excel.

Step 6 - Organize pictures

heat map using pictures4

Step 7 - Create a group (optional)

Make a group of all pictures and you will be able to resize the entire map in one 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. Resize the entire group

Step 8 - Create a table

I downloaded data from wikipedia and created this table:

heat map using pictures6

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.

Formula in table column %:

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

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)

These formulas index values from 0 to 0.5. I have made a post about indexing data before: Compare your stock portfolio with S&P500 in excel

Step 9 - Create a drop down list

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

heat map using pictures7

There is also a hidden formula in cell F26:

=MATCH(D26,{"United states of America","Population","Area","Population / area"},0)*2
  1. Select cell F26
  2. Press CTRL + 1
  3. Select Custom
  4. Type ;;;
  5. Press OK

heat map using pictures8

Step 10 - Change picture brightness for each picture (vba)

  1. Right click on sheet name
  2. Click "View Code"
  3. Paste code below
  4. Exit VB Editor
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Single
Dim c As Integer
Dim Arr() As Variant
If Target.Address = "$D$26" Then
    Arr = Worksheets("Table").Range("Table1").Value
    For r = LBound(Arr, 1) To UBound(Arr, 1)
        With ActiveSheet.Shapes.Range(Arr(r, 1)).PictureFormat
            .Brightness = Arr(r, Range("F26"))
        End With
    Next r
End If
End Sub

Download excel *.xlsm file

US map.xlsm

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

Functions in this post:

MATCH(lookup_valuelookup_array[match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order