Amendment #2 to Choropleth Maps with Excel: a Map Template for Brazil including an alternative approach
Once more a Choropleth Map Template arrived (see also Paulo’s Portugal Map).
This time Gabriel Eiras Villa kindly contributed a choropleth map of Brazil:
Download Choropleth Map Brazil (Microsoft Excel 2003, 129.5K)
But Gabriel has even much more to offer and share. He had a brilliant idea of how to simplify Tushar Mehta's approach:
Gabriel suggested to use one color (e.g. black) and simply change the fill color transparencies of the shapes according to the values of the regions instead of defining a color gradient table and assigning different shades of grey.
Gabriel’s approach has a couple of advantages compared to Tushar's original implementation:
- There is no need for organizing a color gradient table with different colors (e.g. grey shades). Simply format all shapes in black (or any color you may choose) and run the macro.
- Thus, it is very easy to switch the coloring from shades of grey to any other color. Simply select all shapes, assign a new fill color and run the macro.
- The workbook needs less cells and less calculations, has a higher performance and a smaller file size.
- Gabriel’s workbook uses by far less VBA code. Tushar’s code is already very lean and efficient (31 lines of code), but Gabriel’s implementation is even leaner (9 lines of code):
Option Explicit
Sub UpdateMap()
Dim myCell As Range
Application.ScreenUpdating = False
For Each myCell In _
Range("MapShapeToTransparency").Columns(1).Cells
Sheets(1).Shapes(myCell.Value).Fill.Transparency = _
Application.WorksheetFunction.VLookup(myCell.Value, _
Range("MapShapeToTransparency"), 2, False)
Next myCell
Application.ScreenUpdating = True
End Sub
Download the workbook for free here:
Download Choropleth Map Brazil with transparencies (Microsoft Excel 2003, 122K)
Muito obrigado, Gabriel.
Gabriel has a blog of his own: Don’t talk about life. His blog is not fully dedicated to visualization or data analysis, but Gabriel provides a couple of posts on Microsoft Excel. The blog is in Portuguese and unfortunately my knowledge of Portuguese is limited to obrigado and desculpe. However, if you speak Portuguese, I recommend to check it out here.
If anyone out there wants to contribute to our little collection of choropleth maps with Excel here on Clearly and Simply: you are most welcome to send in your maps. You will find an email-link on the left column of this blog.
Leave a Reply