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.

Instructions

Step 1 - Find and copy a map

1. I found a map at the Wikimedia commons website.
2. Press with right mouse button on on the image you want to use and press with left mouse button on "Copy".
3. Paste it to your favorite image editing software.

Step 2 - Copy each state/country/territory

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. Press with mouse 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.

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

Step 4 - Select a transparent color

1. Double press with left mouse button on picture. This takes you to the "Format" tab on the ribbon.
   
   
2. Press with left mouse button on "Color" button.
3. Press with left mouse button on "Set Transparent Color".
4. Press with left mouse button on a "white" area on the picture.

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

Step 5 - Organize pictures

Press 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.
   
   
2. Press with right mouse button on on a picture.
3. Press with left mouse button on Group | Group.
4. Size handles appear when you select the group.
   
5. Press and hold on one of the handles then drag with the mouse to resize the group.

Step 7 - Create an Excel defined Table

I geted 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. Press with mouse on any cell in your data set.
2. Press shortcut keys CTRL + T and a dialog box appears.
3. Press with left mouse button on checkbox if you data set contains header names.
4. Press with left mouse button on 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 %:

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:

Formula in table column %3:

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

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. Press with left mouse button on "Data Validation" button.
4. Select "List".
5. Source: United states of America,Population, Area, Population / area.
6. Press with left mouse button on OK.

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

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.

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

1. Copy code below. (CTRL + c)
2. Press with right mouse button on on sheet name, see image above.
3. Press with left mouse button on "View Code".
4. Paste code below to code window. (CTRL + v)
   
5. Exit VB Editor and return to Excel.

'Event code is rund 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

Get the Excel file

US-map.xlsm

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

Recommended links

• Create a Map chart in Excel
• How to make a killer map using Excel in under 5 minutes with PowerMap plugin