Quick Navigation
- 1Examining the Waterfall Chart
- 2Building the Data Table
- 3Filling in the Data Table
- 4Starting to Build the Waterfall Chart
- 5Formatting the Waterfall Chart
- 5.1Changing the Bridge Series to Line Connectors
- 5.2Hide the Spacer Bars
- 5.3Adding Data Labels
- 5.4Finishing the Connector Lines
- 5.5Final Formatting
- 6Waterfall Chart Example File Download
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:
=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:
=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:
Get the latest Excel tips and tricks by joining the newsletter!
Andrew 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!
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
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())
Thanks for bringing up the issue and offering your solution! I’m sure our fellow Mac users will appreciate it!
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?
Hi snaggletooth,
I don’t have a direct solution, but you might be able to adapt some suggestions from my article on adding percentages to bar charts.
Hi
This was an extremely useful article. I had a question: Is it possible to have 2 water-falls in one chart?
regards
Henna
That really is valuable.
But what about tables having data columns more than 4?
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.
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…
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
Thanks for the template 🙂
Cheers
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
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.
i want to know if you can have 2 waterfalls in one chart
Thank you
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!