ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

How to create a thematic or Choropleth map in Excel 2007 and Excel 2010

Updated on August 17, 2013

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.

Thematic or Choropleth map in Excel 2007 and Excel 2010 showing annual rainfall across the United States.
Thematic or Choropleth map in Excel 2007 and Excel 2010 showing annual rainfall across the United States. | Source

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:

http://www.currentresults.com/Weather/US/average-annual-state-precipitation.php

I used the map of the US for my thematic map from this site:

http://www.clearlyandsimply.com/clearly_and_simply/2009/06/choropleth-maps-with-excel.html

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.

Original map provided by the U.S. Census Bureau .
Original map provided by the U.S. Census Bureau . | Source
  • 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.

Ensure that the column widths are set exactly the same as the original spreadsheet to avoid gaps between states.
Ensure that the column widths are set exactly the same as the original spreadsheet to avoid gaps between states. | Source

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

The Selection Pane in Excel 2007 or Excel 2010 showing the order and names of all shapes on the current worksheet.
The Selection Pane in Excel 2007 or Excel 2010 showing the order and names of all shapes on the current worksheet. | Source

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
Result
Bring to Front
Bring to Front
Shape is moved to the very top of the list
 
Bring Forward
Shape is moved one place higher
Send to Back
Send to Back
Shape sent to the very bottom of the list
 
Send Backward
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:

http://robbiecwilson.hubpages.com/hub/Renaming-Reordering-and-Grouping-shapes-in-Excel-2007-and-Excel-2010

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

Visual Basic code which links a thematic chart to the data it displays in Excel 2007 or Excel 2010.
Visual Basic code which links a thematic chart to the data it displays in Excel 2007 or Excel 2010. | Source

Here is my code without remarks so you can copy it into Excel:

Sub Rainfall()

Dim y

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)

End If

y = y + 1

Next

End Sub

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

How to turn on the Developer tab in Excel 2007.
How to turn on the Developer tab in Excel 2007. | Source

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

Turning on the Developer tab in Excel 2010.
Turning on the Developer tab in Excel 2010. | Source
  • 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:

http://robbiecwilson.hubpages.com/hub/Visual-Basic-for-Excel-2007-and-Excel-2010-A-Beginners-Guide

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:

http://robbiecwilson.hubpages.com/hub/Visual-Basic-Loops-IF-ELSE-and-ELSEIF-statements-and-creating-ranges-in-Visual-Basic-6-for-Excel-2007-and-Excel-2010

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.

Original chart (above), thematic chart (below), created in Excel 2007 or Excel 2010.
Original chart (above), thematic chart (below), created in Excel 2007 or Excel 2010. | Source

Conclusion

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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Thanks for your kind comment,

      Excel is a great piece of software. I have been using and writing about it for some time now and I am still finding things I didn't know about!

    • HolidayGiftIdea profile image

      HolidayGiftIdea 3 years ago

      I love using excel. There is nothing like learning new things on it. Thanks!

    Click to Rate This Article