Clearly and Simply

Tag: heat maps

  • Multicolored Choropleth Maps with Excel

    The Pop, Soda or Coke Visualization with Microsoft Excel

    Multicolored Choropleth Map - click to enlargeThe previous post discussed Choropleth Maps in general, briefly described how to implement them with Microsoft Excel and provided a couple of templates for free download.

    The post was focused on the classic version of a Choropleth Map, visualizing the measurements of a statistical variable in different geographical regions on a map by color intensity: the greater the measurement, the darker the color.

    The classic version is limited to one variable. This follow-up post describes how to enhance a Choropleth Map in order to visualize more than one variable by using several colors and includes the visualization of “The Great Pop vs Soda Controversy” as an example for free download.

    (more…)

  • Choropleth Maps with Excel

    A Set of Choropleth Map Templates for Microsoft Excel

    Choropleth Map Templates - click to enlargeThe dashboard of Lithuania at a glance used a county based map of Lithuania to visualize the geographical distribution of the population by color intensity: the darker the color, the higher the value.

    Very often, this type of geographical visualization is called thematic map, heat map or statistical map. The less known, but correct expression however is Choropleth Map.

    The idea of how to create Choropleth Maps with Microsoft Excel – as brilliant as it is simple – is the brainchild of Tushar Mehta, Microsoft Excel MVP. I simply “borrowed” his idea and code and put it to effective use on the Lithuanian Dashboard.

    Many readers of Clearly and Simply have been interested in this technique, but unfortunately I was not allowed to provide an unlocked workbook of the Lithuanian Census Dashboard (see comments on Lithuania at a glance). That’s why I thought it might be a good idea to write this post including a couple of templates for Choropleth Maps with Microsoft Excel for free download.

    (more…)

  • There is more than one way to heat a map

    2D Tabular Heatmaps with Microsoft Excel

    NYT Speakers XL Replica - click to enlarge Inspired by a NY Times chart, Juice Analytics recently had a post and a discussion on bubble chart heat maps: Bubble, bubble toil and trouble. Chris Gemignani wrote:

    “The first tool we tried, simply on principle, was Excel 2003. As expected, making a NY Times quality bubble chart in Excel 2003 is a hard problem.”

    Juice Analytics is one of my favorite blogs on visualization and I learned a lot from the blog and website. But in this case I do not agree at all. And it seems as if I am not alone.

    What had to come, came. Some of us – including myself – could not let this rest.

    • I used Fabrice Rimlinger’s famous Sparklines for XL (free download here) and created a replica of the NY Times chart. Fabrice was kind enough to publish this on his blog (Yes, we can) and his own version with an improved visualization using bar charts (Stick to the classics?).
    • Two days later my friend and Excel MVP Chandoo showed Visualizing Search Terms on Travel Sites, a bubble-chart solution with plain old Excel (no VBA).
    • Last, but not least: Andreas Lipphardt of xlCubed was ahead of his times and had a post on creating heatmap tables with Excel based on bubble charts already in August 2008.

    Conclusion: Yes you can. It is not a hard problem to create quality heat maps with Microsoft Excel.

    But let’s take one step back. What if you don’t want to use the size of the bubbles for visualization? What if you want to create a classic heat map, i.e. the higher the value, the darker the fill color of the cell and vice versa? Following a definition like the one on Wikipedia:

    “A heat map is a graphical representation of data where the values taken by a variable in a two-dimensional map are represented as colors.”

    Can you create a classic 2-dimensional tabular heatmap with Microsoft Excel as well?

    Yes, you can. And there is more than one way to skin the cat. This post shows the different options and includes all examples for free download.

    (more…)