How to create a waterfall chart in Google Sheets

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:

New native waterfall charts in Google Sheets

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.

Original article:

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:

Headcount Waterfall Chart

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.

Templates are available for all three methods, with links at the end of each section and at the end of this post.
Continue reading How to create a waterfall chart in Google Sheets