Another option to display a Choropleth Map of the United States by County for one user-selected state in Microsoft Excel
The previous post presented three different options to implement a US Choropleth Map by County per State in Microsoft Excel. One approach showed the entire map of the United States and highlighted the selected state (option 2), whereas option 3 filtered the data and only plotted the counties of the selected state.
It was again Leonid Koyfman (who else?), who provided me with an interesting idea for another alternative: Leonid suggested in this comment to combine options 2 and 3 and show both maps in one view. This requires more real estate on the dashboard, but it also combines the advantages of both approaches: it provides a zoomed map of the selected state and keeps the context of this state on the map of the entire USA.
Since the technique will certainly be most useful on a dashboard showing more than just the maps, I created an example visualization with two additional views:
- On the left side of the dashboard the two maps (zoomed selected state and USA) are displayed
- At top right of the visualization a table lists 10 selected counties of the current state: the 10 counties with the highest or lowest unemployment rates. A small new VBA routine assigns the according bin color to each of the 10 counties in the narrow column right to the table
- The band chart at bottom right visualizes minimum, maximum and (unweighted) average of unemployment rates of all counties in the selected state over time
This is just one example how the technique of visualizing maps for a user selected state could be integrated in a more complex dashboard.
If you want to use this on your own dashboard, you may want to play around with the size of the maps and where they are located. Size and position of the zoomed state map are defined by the named range “myMapView”. You can adjust the zoomed map by simply changing the cell reference of this name. As for the map of the USA, you have to group all shapes of the US map first, then resize and reposition this group and finally ungroup it again.
The major disadvantage remains, though: the zoomed map of the selected state makes the distortions visible. These distortions are inevitable in the process of converting SVG-files to freeform shapes in Excel. Well, at least I couldn’t find a way to avoid them. The problem depends on the size of the selected state: Texas and California, for instance, still look good as a zoomed map. Rhode Island and Connecticut definitely don’t. A crucial downside, no doubt about it. Unfortunately I do not have a solution for this. A map would be needed which will not be distorted in the ungrouping process, but I couldn’t find a better map than the one used here.
If you are interested, here is the link to download the zipped Excel workbook:
Download US Choropleth County Map per State (zipped Excel workbook, 2.4 MB)
Many thanks go to Leonid for the idea.
Stay tuned.
Leave a Reply