How to create waterfall charts in Google Sheets

Waterfall charts, eh?

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:

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.

Case 1: Simple Waterfall Chart

Headcount Waterfall Chart

In this case, all the bars are above the x-axis.

Step 1: What does our waterfall chart data look like?

The data for our simple waterfall chart looks like this initially:

headcount waterfall data

What happens if we simply create a chart from the data table above?

Well, we end up with a standard column chart, which doesn’t show what’s happening as clearly as the waterfall chart. We can’t change the color of the bars, since they’re all part of the same series:

Standard column chart

We can do better than that!

Step 2: get the data into the right shape

Create a new data table for the waterfall chart.

Directly adjacent to the original data, make a copy of the row labels, then add four new columns: Base; Endpoints; Positive; Negative, as shown below:

Waterfall table setup

Step 3: Formulas for our waterfall chart

Add the following formulas to the table (click to enlarge):

Formulas for simple waterfall chart

I’ve added color coding to distinguish the different parts of the table.

In the first and last rows, rows 2 and 8 in this example, I’ve put 0 in the Base column and the count value in the Endpoints column. The Positive and Negative columns are left blank.

For the middle rows, rows 3 to 7 in this example, put the following formula in Base:

=if(B3>0,sum(B$2:B2),sum(B$2:B3))

Leave the Endpoints column blank.

Put this formula in the Positive column: =max(B3,0)

Put this formula in the Negative column: =-min(B3,0)

Drag these formulas down.

The completed table should look like this now (click to enlarge):

Simple waterfall chart data

Step 4: Create a stacked column chart

Highlight this new table and create a chart: Insert > Chart...

Make sure you select the stacked column chart:

Stacked column chart

Step 5: Make the base transparent

Set the Base column color to none:

Waterfall chart in Google Sheets

Step 6: Format the chart for presentation

Now it’s simply a matter of selecting suitable colors for the other series, formatting axes and titles.

You should also remove the legend as the series labels are essentially meaningless.

The final chart looks like this:

Headcount Waterfall Chart

Feel free to make a copy of the Simple Waterfall Chart Template (File > Make a copy...).

Case 2: Complex Waterfall Chart

In this scenario, let’s look at what happens if we have negative columns or columns that span the x-axis. For example:

Complex waterfall chart

It doesn’t look too different from the simple one above, so what’s the big deal?

Well, you’ll notice we have positive-value columns that cross the x-axis (e.g. Revenue in above chart), negative-value columns that cross the x-axis (e.g. Cost of Sales in above chart), as well as positive- and negative-value columns above and below the axes.

So there are more variations for our formulas to handle, which makes it more complex than the first example above.

Step 1: Complex waterfall data

This time our dataset is more complex:

Data for complex waterfall chart

Step 2: Use MIN and MAX formulas to shape the data

Naturally, so are the formulas.

I embarked on this project trying to create one single formula that I could drag across and down my columns and rows that would just fill in the values. I got bogged down in seriously wacky IF statements and decided the ROI wasn’t worth it (I still think it’s possible tho!). So I created an in-between version with special formulas for the first and last row, and then general formulas for the middle rows.

The formulas I created were hideous love-childs of Array Formulas, IF formulas, SIGN formulas, ROW formulas, etc… Suffice to say, sub-optimal.

So I googled around to see how other people had solved this problem and found a far more succinct, understandable set of formulas from Excel visualization guru Jon Peltier. These are the formulas I’ve included in the waterfall chart template below.

Assuming the data from Step 1 above is in range A1:B8, add the following formulas to the following cells:

Header-row:

D1: Label
E1: Endpoints
F1: Base
G1: Positive Cols, Above
H1: Positive Cols, Below
I1: Negative Cols, Above
J1: Negative Cols, Below

End-rows:

D2: =A2
E2: =B2
F2 to J2: Leave blank

Middle-rows:

D3: =A3
E3: Leave blank
F3: =max(0,min(sum($B$2:$B3),sum($B$2:$B2))) + min(0,MAX(sum($B$2:$B3),sum($B$2:$B2)))
G3: =max(0,min(sum($B$2:$B3),B3))
H3: =min(0,G3-B3)
I3: =max(0,J3-B3)
J3: =min(0,MAX(sum($B$2:$B3),B3))

Your final data table should look like this:

Waterfall chart data

Step 3: Create a stacked column chart

Highlight the new data table and Insert > Chart... (Step 4 above)

This will add a waterfall chart to your page, from where you can follow steps 5 and 6 of the Simple example above to finish the chart.

Feel free to make a copy of the Complex Waterfall Chart Template (File > Make a copy...).

Complex waterfall chart

How-to create waterfall charts automatically with Apps Script

As you saw in the example above, the formulas are pretty finicky for a generic, catch-all scenario when we have positive and negative bars, some of which may also cross the x-axis.

Arguably a better way to solve this problem is to write a small macro, using Apps Script, to do all the heavy lifting, wrangle the data and insert the chart directly into our Sheet.

For example, imagine we have the following data that we want to display as a waterfall chart:

Complex waterfall chart data

It’ll take some time to manually figure out which bars cross the x-axis and what the base bars need to be for each one. However, once I’ve written a small macro, it’s takes only a few seconds each time to create the chart.

I’ve added the following code to my Script Editor (for a primer on Google Apps Script and where the Script Editor is, check out my beginner guide to coding with Apps Script):

View this code on GitHub.

The onOpen() function adds a custom menu to the Sheet, so you can run the waterfallChart() function from the Sheet.

You run the function by highlighting the waterfall chart data and then selecting Waterfall Chart > Create Chart from the custom menu:

Apps Script chart data

This runs the chart function, wrangles the data, pastes the new data into your Sheet and finally creates a draft chart for you:

Finished waterfall chart with Goole Apps Script

Steps to setup and use this apps script waterfall chart template:

1. Open the view-only version of this file: Embedded Waterfall Chart with Apps Script

2. Create your own copy: File > Make a copy...

3. Open the script editor where the code lives: Tools > Script editor...

4. In the menu bar, choose onOpen in the Select function drop-down menu bar (shown by the red arrow 1 in the image below), and then click the triangle (shown by the red arrow 2 below) to run this script:

Apps Script Run On Open

5. Go back to your Google Sheet and you should now have a new menu option, called Waterfall Chart.

6. Highlight your waterfall chart data in columns A and B, then click Waterfall Chart > Insert chart....

This will create new table of data and waterfall chart.

Here’s the full process again:

Waterfall chart built with apps script

Links to the Google Sheet templates

File > Make a copy...

Let me know your thoughts/questions/comments below!

2 thoughts on “How to create waterfall charts in Google Sheets”

  1. Hi Ben,

    Thanks for this. It has been very helpful.

    I have one issue though, with WF charts that go into negatives (your last example). I have an issue with labels, because they show just a part of the amount (if the bar is halfway across the axis) or a negative number (if the bar is below the axis).

    Is there a way to amend this, or manually change the labels? (I haven’t been able to do so at the moment.

    Best regards,

    1. Hey Pedro,

      Unfortunately you can’t modify the data labels on the chart or do anything about the partial numbers being shown. You can add data labels to the start/end columns and display the correct numbers.

      The alternative workaround is to add the amounts to the labels on the x-axis, and you can make these dynamic so they will automatically change if your values change.

      So, in the waterfall chart table, in the labels column, replace this:

      =A2

      with this:

      =A2&":"&char(10)&B2

      (Assuming your original data table has headers in A1 & B1, and the first row of data is row 2).

      Here’s a screenshot of the formulas (my data is in row 12 in this screenshot): Dynamic labels for waterfall chart

      and this is what the final output looks like:Waterfall chart with dynamic labels

      Hope that helps!

      Ben

Leave a Reply

Your email address will not be published. Required fields are marked *