Clearly and Simply

Category: Choropleth Maps

  • Animated Maps in Microsoft Excel

    How to create an animated Choropleth Map (aka Heat Map, Filled Map, Thematic Map or Statistical Map) in Microsoft Excel

    1.006 words, ~5 minutes read

    In one of the recent articles (All Peaks of the Alps visualized in Excel), I tried to show how to combine different advanced techniques in Microsoft Excel (posted here or elsewhere) to create highly interactive Excel dashboards.

    I will tell you no lies: that one was a washout. The interest in that post and workbook was next to nothing. Now, you certainly expect I would have learned my lesson and try something else. I did learn something, but the stubborn old man I am, I can’t let it go yet. Thus, I decided to give the idea another shot.

    Animated Choropleth or Heat Maps have become quite popular, especially in the past few weeks. So, there is nothing more obvious than providing a template with an animated Choropleth Map in Excel, is it?

    Animated Maps in Microsoft Excel Intro

    Today’s post will briefly explain how to combine two advanced Excel techniques which have already been published here: a Choropleth Map and the animation of visualizations in Excel.

    As always, the article comes with the workbooks for free download: an animated map of Germany by districts (Kreise und kreisfreie Städte) and one of the United States by counties.

    (more…)

  • Optimized Choropleth Maps in Microsoft Excel

    How to create optimized Choropleth Maps in Excel with a higher resolution and without distortions using Excel 365

    Inspired by an idea of my internet friend and highly esteemed colleague Leonid Koyfman, the post US Choropleth Map by County per State – a 4th Option described and provided an Excel workbook with a Choropleth Map of the United States including a second map showing a magnified view of one selected state.

    Optimized Chroropleth Maps in Excel (Intro)The code and technique itself worked fine.

    However, there was one major disadvantage: during the process of creating the map in Excel, the shapes were slightly distorted. The zoomed map of one state still looked good for e.g. Texas or California, but definitely not for smaller states like Rhode Island or Connecticut.

    Today’s post provides a solution to overcome this issue: a way to create a Choropleth Map in Excel without distortions, displaying all counties accurately, no matter at which zoom level. As always, an example workbook is provided for free download.

    (more…)

  • Choropleth Maps of Italy in Excel

    A Collection of Choropleth or Filled Maps of Italy in Microsoft Excel

    Map and Flag of ItalyHeads-up: today’s post is dedicated to my friends and readers in Italy. You will certainly only be interested in this article, if you are working in Italy and/or with Italian data.

    If not, you can easily skip today’s post. It does not include any new ideas, it is only new wine in old wineskins: a collection of Choropleth Map techniques (already published here), applied to maps of Italy for different levels of detail.

    ClearlyandSimply is a special interest blog with only very few readers. Most of them are located in the United States, but when I am looking at the web traffic coming from Europe, Italy is always ranked among the top 3, way ahead of e.g. France or Spain.

    I do not exactly know why. I have a hunch, though:

    Back in 2014 and 2015, I helped my friend Marco Nicolucci creating Tableau visualizations of the history of the Serie A (Italian football, or calcio as you say) and the Rugby World Cup for La Gazetta dello Sport. As of today, both visualizations are still available on the Datamania site of La Gazetta:

    If the visualizations should no longer be available on La Gazetta dello Sport, you can also find copies on my Tableau Public Profile.

    So, maybe the publications on La Gazetta dello Sport drove some traffic from Italy my way.

    Also, maybe my Italian internet friend and highly esteemed colleague Roberto Mensa spread the word about my blog. You probably know Roberto. If not, you definitely missed something. Roberto is the guru for data visualization with Microsoft Excel, not only in Italy, but throughout Europe. Together with Krisztina Szabó and Gábor Madács from Hungary, he is running a website called E90E50. I highly recommend to check out the E90E50charts – Excel Charts Gallery, you will find truly amazing work there, all for free download.

    Well, wherever the traffic comes from, this post is for my Italian readers:

    A collection of 6 templates for creating Choropleth Maps of Italy in Microsoft Excel. All of them are ready to use. Simply download the workbook(s) you like, replace the dummies by your own data and you are good to go.

    (more…)

  • Geographical Flow Maps in Excel (Part 3 of 3)

    Final part of a 3 parts series on how to create a Geographical Flow Map in Microsoft Excel

    Flow and Choropleth Map in ExcelThis is the third and final part of our series on how to create a Geographical Flow Map in Microsoft Excel and it will be a relatively short article (at least by my standards).

    Why? Because the technique of how to visualize flows on a map in Excel was already covered in part 1  and part 2. Today’s post will simply enhance that visualization with a technique which has been covered in many blog posts here throughout the last 10 years: adding a so called Choropleth or Filled Map. Check out the category Choropleth Maps to see what has already been published about this topic on this blog.

    To cut a long story short, this article will only briefly explain the idea and how to configure the colors used on the Flow and the Choropleth Map. As usual, the example Excel workbook is provided for free download.

    (more…)

  • US Choropleth Map by County per State – a 4th Option

    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:

    US Choropleth Map by County per State Dashboard - click to enlarge

    • 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.