ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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 imageAUTHOR

      Robbie C Wilson 

      5 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 

      5 years ago

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)