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
- I found a map at the Wikimedia commons website
- Copy the map and paste it to your favorite image editing software.
Step 2 - Copy each state/country/territory
- Select the "Magic Wand" tool. I made this in paint.net.
- Tolerance : 0%
- Click inside a state
- Copy (Ctrl + c)
Step 3 - Paste to excel
- Start excel
- Paste the picture
- Select the picture and name it using the name box
Step 4 - Select a transparent color
- Double click picture. This takes you to the "Format" tab on the ribbon.
- Click "Color" button
- Click "Set Transparent Color"
- Click a "white" area on the picture.
Repeat step 2 - 4 and copy all your regions to excel.
Step 6 - Organize pictures
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.
- Select all pictures. Tip! Use the "Selection pane" on the "Format" tab.
- Right click on a picture
- Click Group | Group
- Resize the entire group
Step 8 - Create a table
I downloaded data from wikipedia and created this 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 %:
Formula in table column %2:
Formula in table column %3:
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
- Select cell D26
- Go to tab "Data"
- Click "Data Validation" button
- Select "List"
- Source: United states of America,Population, Area, Population / area
- Click OK
There is also a hidden formula in cell F26:
- Select cell F26
- Press CTRL + 1
- Select Custom
- Type ;;;
- Press OK
Step 10 - Change picture brightness for each picture (vba)
- Right click on sheet name
- Click "View Code"
- Paste code below
- 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
If you like maps, check out this post: Use a map in an excel chart
Functions in this post:
MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order