Further explorations into choropleth maps in Excel

I’ve been trying to improve my Excel choropleth map spreadsheet from my first post.

The first thing I tried was to try to update it using some maps created on ClearlyandSimply.com. So I’ve created two new versions, one for Europe and one of the World. Both using the maps from ClearlyandSimply.com, with a few small alterations to the code so that on hovering over the map it tells you the country not the abbreviation. I did try to make my own UK maps of Constituency boundaries and Counties but using this technique but no matter how hard I tried I couldn’t get it to work. If you do get it to work, please let me know. Note that in both the Europe and World map the data may not be accurate and is only there for illustrative purposes.

At some point my aim is to look into either actual open source GIS software or mapping in R using something like this but I like these Excel ones because they are easy to share around at work and play with for people with limited coding skills (like me).

After reading this post it looks like there are a few additions I could make to my spreadsheets.

  1. Highlight the shape that has been selected
  2. Have tables/charts which also update when the country has been selected
    1. I already have tables that update so it can’t be that hard to add charts that update too.
  3. Select the country to look at from a list of countries.

For the first point I just want to add a shadow to the shape that I have selected. This is done by updating the VBA on the data sheet from

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("shapelist")) Is Nothing Then
   Sheets("Map").Range("TableValue").Value = Target
   Sheets("Data").Range("A1").Select
   Sheets("Map").Select
End If
End Sub

To

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("shapelist")) Is Nothing Then
   Country = Application.WorksheetFunction.VLookup(Target, Range("Data_Lookup"), 2, False)
   Sheets("Map").Range("TableValue") = Country
   Sheets("Data").Range("A1").Select
   Sheets("Map").Select

   'Reset all other shapes
   Dim shape As Range
   For Each shape In Range("Shapelist")
      With Sheets("Map").Shapes(shape.Value).Shadow
       .Visible = msoFalse
      End With
   Next shape

   'Change selected shape
   With Sheets("Map").Shapes(Target).Shadow
    .Type = msoShadow25
    .Visible = msoTrue
    .Style = msoShadowStyleOuterShadow
    .Blur = 2
    .OffsetY = 3
    .RotateWithShape = msoFalse
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .Transparency = 0.25
    .Size = 115
   End With

   'Make sure the shape is at the front
   Sheets("Map").Shapes(Target).ZOrder msoBringToFront
End If
End Sub

Upon clicking on the shape this:

  1. Gets the country name from the data
  2. Updates the table on the map sheet to give the KPIs for that country
  3. Resets all of the shadows for the countries
  4. Sets the shadow for the selected country with the settings I wanted
  5. Brings that country to the front

I have now added in a chart which updates when you click on the desired shape. I’m not going to do it the same way as on clearly and simply because it doesn’t make sense to have a list of 183 countries as that is too many. Adding the chart required no extra VBA, just the addition of an extra chart control sheet with some simple formulas.

WorldMapChart

The ranking to create this chart makes a unique rank. That is if you have three numbers that are all the same, e.g. 1, 1, 1, then their ranks will all be different, e.g. 1, 2, 3. This is done using the countif function.

Download the updated World map here Mapping the World v2 or the updated version of the UK postcode map here Mapping UK Postcode Areas v2 both have the added chart feature.

2 comments

  1. Hi Richard,

    Many thanks for the UK map in excel, I've been looking for something similar for ages!

    I've been trying to find some way of running macros when clicking on the shapes but the hyperlinks stop them running and there doesn't seem to be an easy solution. The best I can come up with is a way to stop the hyperlinks flicking to a different sheet and back:

    - Remove the selection_change code from the Data sheet.
    - Cut/Paste the data table to the maps sheet (AA4) so it is off screen.
    - Zoom out a bit & freeze panes at around Z40 & zoom in again.
    - Change the code that sets the hyperlinks to point to SubAddress:="Map!AB" & row
    - Add selection_change code (below) to the Map sheet.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("shapelist")) Is Nothing Then
    With ThisWorkbook.Worksheets("Map")
    .Range("AB2").Value = .Range("AB3")
    .Range("AB3").Value = Target
    .Range("TableValue") = Application.WorksheetFunction.VLookup(Target, Range("Data_Lookup"), 2, False)
    .Shapes(Target).Shadow.Visible = msoTrue
    .Shapes(.Range("AB2").Value).Shadow.Visible = msoFalse
    .Shapes(Target).ZOrder msoBringToFront
    End With
    End If
    Application.ScreenUpdating = True
    End Sub

    The hyperlinks will still work & select the appropriate cell but the screen will not move 🙂
    I also tweaked the code to store the current & previously selected areas so it only has to turn off one shadow, not loop through them all. (You may need to pre-set all shadows correctly then hide them all as the code above only toggles the visibility.)

    Thanks again for making this, I really didn't want to make, add & name all those different shapes to a sheet myself!

    Rob

Leave a Reply

Your email address will not be published. Required fields are marked *