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...).
Ok, waterfall charts are real. And useful. They don’t actually involve falling water in any capacity, but they do 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.
This year I’ve focussed on deepening my coding skills, so I’ve finally been able to give d3 a proper go. And let me tell you, it’s brilliant. It’s exciting to hook up a data source to a custom chart that changes dynamically, and be able to see it on a live website, which other people can view.
In this post, I’m going to discuss the steps I took to create this d3 visualization of the GitHub API.