# How to Create Waterfall Charts in Excel

## Building the Data Table

Start with your basic data entry. In this example, we have quarterly financial figures (Q1, Q2, etc.) that add up to a total fiscal year (FY). Leave a column blank so we can label the parts of the data table later.

We need to systematically build out the different pieces of the chart as separate data series. To make it easier to understand, I’ve build a layout and color-coded the different series:

The Total is just that, a data series to hold the blue total bar at the far right of the final chart.

The +Y/-Y Spacer are the invisible bars that prop up the various floating pieces of the waterfall chart.

The +Y/-Y Loss and +Y/-Y Gain are two categories that make it easier to color-code the positive and negative figures on separate data series.

The +Y/-Y Label are the data series that control the floating data labels for each bar in the waterfall chart.

There is a Bridge data series for each connecting line that goes between the bars in the chart.

Finally, there is a Running Total that keeps track of the data from bar to bar.

## 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!

## 15 thoughts on “How to Create Waterfall Charts in Excel”

1. Exceluser says:

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. snaggletooth says:

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())

1. Thanks for bringing up the issue and offering your solution! I’m sure our fellow Mac users will appreciate it!

3. snaggletooth says:

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. Henna says:

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. BARUT says:

That really is valuable.
But what about tables having data columns more than 4?

6. Ashar says:

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.

7. Ayaz says:

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

8. Artima C says:

Thanks for the template ðŸ™‚
Cheers

9. Shawn says:

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.

10. Rosa Campos says:

i want to know if you can have 2 waterfalls in one chart

Thank you

11. Victor says:

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!