I saw this calendar heatmap of bike rides at Via Velox a few weeks ago. It’s not a really new approach to visualizing daily data–Nathan Yau at Flowing Data created something similar a few years ago using vehicle fatalities.

What I liked about the Via Velox version is the bar chart along the right side and the column chart at the bottom. I also like the little design feature of numbering every 5 weeks with the intervening dots.

There are 4 parts to creating this calendar heatmap in Excel:

  1. The heatmap itself, which requires conditional formatting a little number formatting trick;
  2. The bar chart, which requires more conditional formatting and the same number formatting trick;
  3. A column chart below, in which I use sparklines and a merge cell trick; and
  4. A formula to the left to get the numbers and dots set up.

Overall, this approach is fairly straightforward and easily replicable to other years. You can create this visualization in any “modern” version of Excel (2010, 2013, 2016 on PC; and 2011, 2016 on Mac).

Instead of writing out the steps one by one, I created (my first) step-by-step video tutorial, which you can watch below or on YouTube directly.

Also, instead of giving you the template for this visualization, I’m selling it in the PolicyViz Shop for (a meager) $5. I’m trying this approach as a way to build financial support for the podcast, which will allow me to bring the same great content and not have to seek out on-air sponsorship. The Excel file includes the 2015 version for which I have the data, but also templates for 2016-2020.

August 29, 2017
2 Comments
15165