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.

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

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

### Step 4 - Select a transparent color

1. Double click picture. This takes you to the "Format" tab on the ribbon.

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 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.

2. Right click on a picture
3. Click Group | Group
4. Resize the entire group

### Step 8 - Create a 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.

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

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

### 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
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
```