How to create a thematic or Choropleth map in Excel 2007 and Excel 2010
Creating a thematic map in Excel 2007 and Excel 2010 to illustrate a theme across a geographical region
Hi and welcome to my latest hub on Excel. Today, we will look at creating a Thematic (or Choropleth) map. Using a thematic map, you can show how themes or variations across geographical areas, for example, you could illustrate how population density, rainfall, voting preferences, or average income vary across the states in the United States of America.
In today’s hub, I will be creating a thematic map showing the annual rainfall for each of the states in America (excluding Alaska and Hawaii). The process of creating the map includes a number of steps which we will work through in detail:
- Understanding and adapting the thematic chart we import to suit our needs so that we can re-use the map with different data in the future
- Next, we will import the data (rainfall data) into Excel
- To link the table to the shapes that represents each state we will be using a macro with Visual Basic code
The overall goal of this hub is as with all of my Excel hubs, to create something that requires as little manual work as possible to maintain or to update the map once we have completed it. The Visual Basic I use (written by myself) in this hub will take data from a range and also the shapes that make up the map and link the data for each state to each shape that represents the same state automatically.
The data for the annual precipitation for the states of the US, I obtained from the following site:
I used the map of the US for my thematic map from this site:
Adapting the original thematic map to work with the imported data in Excel 2007 or Excel 2010
The original map which is provided courtesy of the U.S. Census Bureau can be seen below.
- First, we prepare the new worksheet by ensuring that the column widths are identical to the original worksheet
- Next, copy just the map itself to the new worksheet
- Next we delete the key as we will be creating a new key of our own
Note: pay particular attention to the column widths. If the widths are not identical there will be gaps between the shapes which will make the map look “odd”. You can see several gaps between states on the figure below.
How to change the order and names of shapes in a workbook in Excel 2007 and Excel 2010
The next challenge is to ensure that the shapes are given names that are memorable and that those names are all in alphabetical (or reverse alphabetical) order.
Note: This will make your chart significantly easier to use should you decide to re-use it with data from another source. In addition, the Visual Basic code that we will use to colour our shapes relies on the shapes being in the same order as the data.
To determine the name and the order of all our shapes for our states we need to use the Selection Pane. To access this:
- First select a shape
- Navigate to the Drawing Tools / Format tab
- Choose the Selection Pane button from the Arrange Group
Now we can see all of our shapes in the Selection Pane, we need to rename them so that each shape has the name of the shape it represents
- To rename shapes, click on the name in the Selection Pane and then rename it and press Enter
Note: If you don’t press Enter after renaming a shape, when you click away from the Selection Pane, you will lose the edits and it will revert to its original name.
Next we need to re-order the shapes in alphabetical (or reverse alphabetical) order.
This part is the most manual part of the whole process. To move a shape:
- First select it
- Right click and select one of the following options depending on whether you want to move it up or down:
How to move a shape to a new place in the order in Excel 2007 and Excel 2010.
First Menu Option
Second Menu Option
Bring to Front
Bring to Front
Shape is moved to the very top of the list
Shape is moved one place higher
Send to Back
Send to Back
Shape sent to the very bottom of the list
Shape moves one place lower
To learn more about renaming, re-ordering and also grouping and ungrouping shapes, I have a hub that goes into all of these topics in far greater detail that can be found here:
Importing the data for a thematic map into Excel 2007 or Excel 2010 from an external data source
This part is relatively straightforward as long as you follow a few simple rules.
Firstly: if you are going to re-use your map but use different data sources, I would recommend that you reuse the same worksheet for the data and place the data in the same cells (you can always move old data to a new column in the same worksheet just in case you need it in the future).
Secondly: and most importantly, ensure that the order of the data labels of the imported data (in our case states of the USA) match exactly the order of the shapes in your map otherwise Excel will apply the data to the wrong states!
Writing the Visual Basic code required to colour code states in a thematic chart based on imported data in Excel 2007 and Excel 2010
The exciting part of the thematic chart is that the individual states will illustrate their status by their colour. So the drier states in our example will be coloured brown, orange and yellow, while the wetter states will be coloured green or blue.
We achieve this with Visual Basic code.
The Visual Basic code that we will use for our thematic map is below (I have added comments in red to illustrate what each line does).
Here is my code without remarks so you can copy it into Excel:
y = 1
For Each cell In Range("B2:B49")
If cell.Value <= 10 Then
Sheets("Thematic_Map").Shapes(y).Fill.ForeColor.RGB = RGB(153, 102, 51)
ElseIf cell.Value <= 19.9 And cell.Value >= 11 Then
Sheets("Thematic_Map").Shapes(y).Fill.ForeColor.RGB = RGB(255, 192, 0)
ElseIf cell.Value <= 29.9 And cell.Value >= 20 Then
Sheets("Thematic_Map").Shapes(y).Fill.ForeColor.RGB = RGB(255, 255, 102)
ElseIf cell.Value <= 39.9 And cell.Value >= 30 Then
Sheets("Thematic_Map").Shapes(y).Fill.ForeColor.RGB = RGB(146, 208, 80)
ElseIf cell.Value <= 49.9 And cell.Value >= 40 Then
Sheets("Thematic_Map").Shapes(y).Fill.ForeColor.RGB = RGB(0, 176, 80)
ElseIf cell.Value <= 70 And cell.Value >= 50 Then
Sheets("Thematic_Map").Shapes(y).Fill.ForeColor.RGB = RGB(0, 176, 240)
y = y + 1
All work with Macros and Visual Basic happens on the Developer tab. If you don’t have that tab enabled, follow these instructions to add it:
For Excel 2007:
- Press on the Excel button
- Choose Excel Options
- Select Show Develop tab in the Ribbon
- Click OK
If you are using Excel 2010:
- Browse to the File menu
- Choose Options
- Click the Customize Ribbon tab
- On the Main Tab select Developer as shown below
- On the Developer tab, select the View Code button in the Controls Group
- Select the Insert menu and then choose Module
- Paste in the code above
- Name your code (mine is called Rainfall) and then select File and then Save
To see and run your new code, you can access them from the Macro button on the Developer tab. It should now appear in the list of Macros stored in that workbook.
While writing the code above for this hub, I learnt a great deal about how to code. First of all I learnt the basics, which I have used as the basis for this hub. It contains an introduction to VB and also some basic commands you will need to know to write code, which you can find here:
The second hub covers loops, IF statements and working with cell ranges in Visual Basic. This hub introduces the key concepts you will need to know to go from basic scripts to more complex scripts such as the one we used today for our thematic map. To learn more about loops, ranges and IF statements:
Running a macro containing Visual Basic code in Excel 2007 and Excel 2010
Now, in order to run the macro we can either:
Run it from the Macros button:
- First we click the Macros button
- Select the macro and click Run
Or we can assign it to the shapes so that it will run when any one of the shapes in our thematic map is clicked. To do this:
- Click on any shape in the map
- Press the Control key and A to select all the shapes at once
- Select Assign Macro
- Choose the macro you want to run
- Now when we click on any of the states in our map, the macro will automatically run.
The figure below shows us how our map looks before and after running the macro.
A thematic or Choropleth map allows you to illustrate a theme or to show variations across a geographical region such as the US. In today’s example, we showed rainfall across the states in America. To achieve this:
- We imported and adapted a map containing shapes that represented all the mainland US states
- Next we imported data containing the rainfall for all the states in our map
- Then, we wrote some Visual Basic code and assigned it to a macro. This code matched the rainfall data for each state to the shape for each state and changed the fill colour of the shape to reflect that states rainfall
- Finally, we linked the macro to all the shapes in our map so that if any shapes are clicked it will update them all automatically should the data change
I do hope that you have enjoyed reading this hub as much as I enjoyed writing it. The code I wrote for this hub represents the first code I have written from beginning to end so it has been quite a journey! This for me represents everything I love about writing about Excel. When you start a project you are never really sure where it will lead and what you will learn along the way. Many thanks for reading, please feel free to leave any comments you have below.