If 2015 was a year of huge change, then 2016 was more incremental, building on the foundations of 2015. I still tried lots of different things – different gigs, different projects, different tools – but I’ve found creating apps and solving data problems on the Google platform is my sweet spot of skills, experience and enjoyment.
On this site
Investing so much time and effort into this site really started to pay off over the course of 2016.
The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).
However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.
We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image:
As with the waterfall charts in Google Sheets, they’re not one of the out-the-box charts available to us, so we have to manually create them with a crafty workaround. Thankfully, they’re relatively simple to create, certainly simpler than the waterfall chart.
For all of these examples, we’ll use this fictitious real-estate dataset:
Here, I’m imagining the real estate agency collects data relating to their sales funnel, and they want to display it in a funnel chart format.
Click here to open up the Google Sheet template and make your own copy (File > Make a copy...).
Update December 2017: Google have recently added Waterfall Charts to the native charts in the Chart Tool of Google Sheets, obviating the need for you to manually create your waterfall charts (or use apps script) per my original post.
Now you simply highlight your data, click Insert > Chart and under the Chart type picker choose “waterfall”, as shown in the following image:
The original post that follows was first published in late 2016, and I’m leaving it here for anyone who wants to look under the hood at how waterfall chart data is constructed and how to do that using apps script.
In this post, we’ll look at how to create a waterfall chart in Google Sheets.
Waterfall charts are real. And useful. They show the cumulative effect of a series of positive and/or negative values on an initial starting value.
The following waterfall chart shows the headcount changes for a department, visually depicting the cumulative effect of the additions and deletions to the start value:
It shows the number of staff in our department at the start of the year (left grey bar), the number of people added from other departments or as new hires (green bars), the number of people who left (red bars) and finally the balance which is the headcount at the end of the year (right grey bar).
The waterfall chart above is relatively easy to create in Google Sheets but does still require some data wrangling to set it up. Notice that all of the bars are above the x-axis (Case 1), which makes the data set up vastly simpler than the case when we have a mix of bars above and below the x-axis, or spanning the x-axis (see Case 2 below).
I’ll show you how to create both of these cases, starting with the easier, positive-bar case.
After creating the simple and complex versions manually with formulas, I’ll show you some Apps Script code to automate the majority of the process and massively speed up creating complex waterfall charts.
I love animated charts where the animation is central to the storytelling; where it adds value, rather than being simply fancy decoration.
When it’s done well, it can be extremely powerful, mesmerizing and compelling.
One of the best examples I’ve seen is Bloomberg’s animated temperature visualization. I love how powerfully this chart conveys its message, and I’ve been semi-obsessed with it ever since I saw it.
So, over the past few weeks and since my last experiment with the Google Visualization API, I’ve been chipping away at this project to create an animated temperature chart within the Google environment. It’s finally in a state I can share here, however it’s what you might call an MVP still…
Here’s my animated temperature chart built with the Google Visualization API connecting to Washington D.C. data in a Google Sheet:
You can see this animated chart in all its glory here.
My Twitter feed is mostly populated with data-related resources and people, so I almost always find something new and shiny going on there to distract me.
Sometimes this can be a good thing though (and sometimes, it can be the best thing ever).
Take last Friday. I found myself procrastinating, browsing my home feed when I saw this tweet for the upcoming Microsoft Data Science summit:
What caught my eye was not the actual tweet, but the sumptuous weather visualization that I recognized as one of Edward Tufte’s famous creations.
I clicked through to the article, which linked to Tufte’s original chart (here) and a fantastic tutorial about re-creating the chart using R, from Brad Boehmke (which is actually the chart shown in the tweet above).
It looks at daily average temperature over a period of time and captures the historic min/max range, the normal range (95% confidence interval), the 2014 temperature line and picks out the highs and lows. It renders all of this information in a single, beautiful column/line combination chart.
Curiousity piqued, I wondered whether this would be possible with Google Sheets?