Enhance a detailed Choropleth Map in Microsoft Excel with additional features
The recent article Faster Choropleth Maps with Microsoft Excel provided a faster version of the VBA code to update a detailed Choropleth Map in Microsoft Excel.
Leonid Koyfman, a faithful reader of Clearly and Simply liked this article. Leonid already contributed his invaluable ideas and insights here before (Excel Multiple Value Filters with Invert Selection). He had a couple of very interesting ideas for the fast Choropleth Map and he is kind enough to share them with us:
- Let the user filter the data by value bin and thereby highlight the bins of interest on the map
- Show tooltips when hovering over the map to display the name of the county and the unemployment rate in percent
- Let the user switch the level of detail: color the map by county or by state
Today’s article describes Leonid’s enhancements and includes a link to the Excel workbook for free download.
Enhancement 1 – Filter by Value Bins
A checkbox in the map legend for each bin lets the user filter the data and highlight the corresponding counties to the bins of interest. Additional icons above the legend allow to select all, deselect all or invert the selection with one click.
The main steps of the implementation of filters:
- add checkboxes to the legend of the map
- define a range on worksheet [control] as the target range for the checkboxes
- add 3 icons for select all, deselect all and invert selection
- add a cell to the worksheet [control] to let the user define the fill color of the shapes which are not included in the filters (could be white, could be light grey or whatever you’d prefer)
- in the VBA we need a new module handling the clicks on the new icons. The subs are pretty easy to understand, please have a look for yourself. The idea and the implementation is along the lines of what Leonid already contributed in this post way back in 2011: Excel Multiple Value Filters with Invert Selection.
- finally in the sub which updates the map we need one more array (varFilters). This array is initialized with the user’s filter selection and which will be used afterwards in an IF-statement to decide if the current shape will get a fill color from the color scale (if it belongs to a bin selected in the filter) or the default fill color, if it belongs to an unselected value bin.
Enhancement 2 – Tooltips
The Choropleth Map provides a very good visualization of the geographical distribution of the data, but it lacks a feature to also access the exact values of the counties or states. One of the best ways of showing additional information on graphs or maps are tooltips. Hovering with the cursor over the map displays a small text box and shows the name of the county and the unemployment rate.
The trick is to assign a hyperlink to each shape. The link has no target address, but the screen tip is used to automatically display a tooltip. After the user changed the data to be visualized, the VBA code loops through all shapes and assigns the name of the county and the unemployment rate to the shapes’ screen tips. Basically a very simple piece of code: a For Next loop and a .Hyperlinks.Add statement within the loop. I used this little trick also in one of the workbooks provided in the post Spice up your Choropleth Maps with Excel.
Enhancement 3 – Switch between Counties and States
The original intention of the whole project was a detailed map of the US by counties (more than 3,000 shapes) responding within an acceptable time frame. For a map by states, the template provided here based on the original implementation of Tushar Mehta would have been fully sufficient.
Having said that, an interactive feature to easily switch the visualization from county level to state level and back can be helpful for analyzing the data.
Implementing this feature is a piece of cake and does not even require any changes in the VBA. All we need is
- the data by states
- radio buttons to toggle between “by counties” and “by states”
- an update of the formulas to fill the named range “myValues” with the data according to user defined level of data: a simple formula using CHOOSE, INDEX and MATCH and you are good to go
Please be advised that the tooltips (see enhancement 2 above) will show the same value for each county of a state: the unemployment rate of the state, not the individual unemployment rate of this county.
That’s it.
Calling the VBA Subs
In order to optimize performance, there are now 2 main subs which are called from the different interactive controls on the dashboard: the first one will update the map and the tooltips (i.e. if the user changed the year or switched from counties to states) and one without updating the tooltips (i.e. if the user only changes the filters or the color scale).
The Performance
It comes as no real surprise that the additional lines of code for realizing the new features require additional run-time of the VBA. In other words, updating of the map takes longer than before, but I think the performance is still acceptable:
Updating the map after changing the visualized values (the year of the unemployment rates or switching from county to state level or vice versa) takes 8.8 seconds with the progress update in the status bar and 4.7 seconds without the status bar. If you only change the color scale or the filter settings, the VBA takes 5.4 seconds to update including the status bar updates and 3.3. seconds without the progress information on the status bar.
It is a matter of taste whether you want to accept the additional time for the status bar updates. To be honest, I do not have a recommendation for you. 4.7 seconds is already on the borderline to leave the user without any information on the progress. However, 8.8 seconds is almost twice the time. If your users know about the fact that the update takes a few seconds, I would probably go without updating the status bar. You can easily set the status bar update on worksheet [control]. You do not have to change the VBA. Please decide for yourself.
The Download Link
Here is the Microsoft Excel workbook for free download:
Download Choropleth Map US Counties Enhanced (Microsoft Excel 2007/2010 workbook, 2,562.3K)
More Ideas
There are many more things you could wish for. Here are just 2 obvious ones:
- Add a bar chart to show the exact values of the counties. Certainly not possible with a standard Excel bar chart, but Jon Peltier’s Accordion chart may be an alternative: Accordion Chart.
- Add an interactive feature to display the unemployment rate over the years after clicking on a county, like I showed here: Spice up your Choropleth Maps with Excel. You would lose the hyperlink tooltips, but the user would get even more information by one single click.
About Leonid
Leonid Koyfman is a BI professional and Database Architect. He is passionate about database design, dashboards and data visualization and an outstanding Xcelsius expert. Leonid has published several posts on Everything XCelsius, the Xcelsius Guru Network. Read more about Leonid in his introduction on Everything XCelsius and also have a look at his guest posts there:
Dynamic status update of Check Box components in Xcelsius: Part 1/4
Dynamic status update of Check Box components in Xcelsius: Part 2/4
Thank you, Leonid
A big time thank you very much goes to Leonid for all his great ideas and for contributing content to Clearly and Simply. This is greatly appreciated.
Thanks, Leonid!
What’s next?
Performance of VBA code is a very interesting topic and I am planning one or two more posts on this very soon. Next article, however, will be a post on the power of web page objects on Tableau dashboards.
Stay tuned.
Update on Monday, 10th of September, 2012
Enhancement 3 (see above) came very late to the party and Leonid and I added this feature last-minute before publishing. In the aftermath we discussed whether it is really the best option to show the name of the county in the tooltip, but the unemployment rate of the entire state (if “by state” is selected).
In general, there are 3 different options:
- The way described above: the tooltip shows the name of the county, but the unemployment rate of the state. The advantage: the tooltip provides the name of the county and the unemployment rate of the state and this corresponds with the color of the map. The disadvantage: it may confuse the user because the level of detail does not match in the tooltip (county name, but unemployment rate of state).
- Another option would be a tooltip showing the name and the unemployment rate of the county. This is not recommended, since the value shown in the tooltip would not be in line with the color of the county.
- Third option is a tooltip displaying the name and the unemployment rate of the state. All counties of a state have the same tooltip, but it is very straight forward and corresponds exactly to the selected level of detail and the visualization.
Option 3 as an alternative to the version above does not require many changes. All you have to do is change the named formula “myRegions” to switch between county names and state names depending on the user’s selection (radio buttons). You could do this with a CHOOSE function, an IF clause or an INDEX formula.
Here is the alternative workbook for free download:
Download Choropleth Map US Counties State Tooltips (Microsoft Excel 2007/2010 workbook, 2,487.7K)
Leave a Reply