How to Create Waterfall Charts in Excel

Filling in the Data Table

We’ll fill in the data table in two steps. The first column (Q1) will have one set of formulas, and then the rest will have another. These are the formulas in the first column:

Data Table Column 1 Formulas

=B1

=MAX(0,B2)
=IF(AND(B20>0,B2>=0),B2,0)
=IF(AND(B20>0,B2<0),ABS(B2),0)

=MIN(0,B2)

=IF(AND(B20<0,B2>=0),-1*ABS(B2),0)
=IF(AND(B20<0,B2<0),B2,0)
=B20

=B2

The next column is as follows:

Data Table Column 2 Formulas

=C1

=IF(AND(B20>0,B20+C2>0),MIN(B20,B20+C2),0)
=IF(AND(B20>0,C2<0),MIN(B20,ABS(C2)),0) =IF(AND(C2>0,B20+C2>0),MIN(C2,B20+C2),0)
=IF(AND(C20>0,C2>=0),C2,0)
=IF(AND(C20>0,C2<0),ABS(C2),0)
=IF(AND(B20<0,B20+C2<0),MAX(B20,B20+C2),0)
=IF(AND(C2<0,B20+C2<0),MAX(B20+C2,C2),0)
=IF(AND(B20<0,C2>0),MAX(B20,-1*C2),0)
=IF(AND(C20<0,C2>=0),-1*ABS(C2),0)
=IF(AND(C20<0,C2<0),C2,0)
=B16
=C20

=B20+C2

The columns after the first and second can be copied across. All that needs to be changed are the Bridge pair values. The final data table (using the example data) should look like this:

Data Table Finished

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

15 thoughts on “How to Create Waterfall Charts in Excel

  1. Thanks for an awesome guide!!!

    The formula for B14 and B15 should be changed to the following

    =IF(AND(B20=0),-1*ABS(B2),0)
    =IF(AND(B20<=0,B2<0),B2,0)

    What I did is adding a = after B20. This will allow the chart to work for ending values of 0

  2. Worked like a charm except… I’m on a Mac using Excel 2011 and I found a ton of little horizontal lines all over the place. The culprit, it turns out is using “0” as the value_if_false in the =if functions. The fix, discovered from the interwebs, is to replace 0 with na(). For example,

    =IF(AND(C20>0,C2>=0),C2,na())

  3. So, I wonder how we might have the data labels show each period’s ending cash position rather than the change in cash. For example, period 1 has an increase in 10. Period 2 has a decrease of 2. In what I’m trying to do, the data label for Period 2 would be 8 rather than -2. Any thoughts on how this might work?

  4. Hi
    This was an extremely useful article. I had a question: Is it possible to have 2 water-falls in one chart?

    regards
    Henna

  5. Fantastic guide!. Some customization needed, but this is by far the best waterfall chart out there. Thanks a lot for the effort and making it available.

  6. Hello,
    Thank you very much for the wonderful insight… Please advise on how do i add a custom range of values for the data labels – i can do it manually for individual data points using insert text box… however, i did not find any option in charts in excel 2010.
    any help will be appreciated…

    1. Hello, Ayaz.
      You can add a custom range in a standard way: Right Click on the chart/Select Data…/Add, then select Series Name and range of Series Values. After that, click Ok two times. You will get one more range in the chart.
      Then you should Right Click on the range/Change Series Chart Type… and select Line type for the range (only for the range, not for the chart). Next add labels and make the line transparent. That’s all.

      But using a template (this or any other) you forced to do many operations with it manually. If you want to avoid such manual operations at all, you can use Waterfall Chart Studio to fast and easy creating the waterfall charts. More details you can find out following the link http://fincontrollex.com/?page=products&id=1&lang=en
      You can get charts like this

  7. Hi, I like the approach you took with this chart, however I can’t download the sample file?

    Ha sit been moved, if so can you re-direct me to where it is now?

    Thank you

    1. Hi Shawn! The Excel embed has changed its icons since this article was written. You can download the example by clicking on the download button in the bottom right of the embed. It now looks like an arrow coming out of a piece of paper. I’ve updated the article to reflect this change in appearance.

  8. Hi,
    Is it possible to setup multiple bars for each quarter with different bar colours by quarter? Example: Q1 has 4 bars – each representing 4 different clients. The same for Q2 etc. But bars are colour coded by their quarters i.e. All (client) bars in Q1 are in red, Q2 in blue etc.
    Would be grateful if someone can help?
    Million thanks!

Leave a Reply

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