How to Create Waterfall Charts in Excel

Formatting the Waterfall Chart

Changing the Bridge Series to Line Connectors

Now, we can begin to turn our strange stacked column chart into a proper waterfall chart. First, we are going to change all of the Bridge series into line charts. Select the top bar in the first column of the chart. Check to make sure it is highlighting the Bridge column data in your table. Right-click the bar and choose Change Series Chart Type.

Change Series To Line Chart

In the Chart Type dialog that appears, choose the first chart in the Line category and click OK.

Change Chart Type

The result will be that the first Bridge row is now a connecting line between the two columns of Q1 and Q2 data.

First Line Chart

Repeat the process for the other Bridge series, clicking on the bottom bar on the second column, and so on. You can always make sure you are selecting the right series on the chart by looking at what row is highlighted in your data table above. When you are finished changing the lines, your chart will look something like this:

All Line Charts

Hide the Spacer Bars

Now we need to hide the spacer bars from view. Click on the spacer bar on the chart (and double-check that the correct row is selected).

Spacer

Then, click on the Format menu tab under Chart Tools. In the Shape Styles section, choose Shape Fill and click No Fill. Repeat for the other spacer bar (if it is in use with your data).

No Fill

Adding Data Labels

Next, we’ll add data labels to the bars. Each bar needs slightly separate formatting. Click the top bar in the first column and make sure it is a >=0 Label row. Right Click and select Add Data Labels.

Add Data Labels

Right now, it is showing decimal places and it is also showing zeroes. We want to hide both and move the numbers down on top of the other bar, so double-click on one of the new labels.

Data Labels

In the Format Data Labels dialog that appears, in the Label Options section, choose Inside Base under Label Position.

Format Data Labels Inside Base

Then choose the Number section. We are going to create a special format for the numbers that makes the zero values disappear. Further, since this is the >=0 Label, we only need to format the positive numbers. (For more details about Excel’s number formats, check here). Choose the Currency category, specify 0 decimal places, and then modify the format to include three semi-colons at the end. The Format Code field should look as follows:

$#,##0;;;

Format Data Labels Number

Click Add, then Close.

Repeat this process for all 4 label data series. There are different number formats to use for each data series.

For the +Y >=0 Label:

$#,##0;;;

For the +Y<0 Label:

-$#,##0;;;

For the -Y >=0 Label:

;$#,##0;;

For the -Y <0 Label:

;-$#,##0;;

Pay careful attention to the minus signs and the location of the semi-colons. Don’t forget to click Add and then Close each time you make a change.

Now we can add the internal label for the total FY column. Right-click the FY bar and select Add Data Labels. Double-click the label you just created. In the Format Data Labels dialog that appears, in the Label Options section, choose Inside End under Label Position. Then choose the Number section and set the format to Currency, 0 digits. Click Close.

Finally, we need to make the bars for the label data series invisible. As we did for the spacer bar, click on the label bar on the chart (and double-check that the correct row is selected). Then, click on the Format menu tab under Chart Tools. In the Shape Styles section, choose Shape Fill and click No Fill. Repeat for the other label bars.

When finished, the chart should look something like this:

Chart Labels Finished

Finishing the Connector Lines

The last thing we need to do before choosing colors and format is turn the bridge lines into proper connectors. Unfortunately, this must be done individually for each of them.

Select one of the connectors, right-click it (this can be a bit hard, since they are small) and choose Format Data Series.

Format Lines

In the Format Data Series dialog that appears, choose the Line Color section. Change the setting to “Solid Line” and make sure the color is black.

Format Data Series Line Color

Choose the Line Style section. Change the Width to 0.25 pt. Change the Dash Type to Square Dot (the third option on the list).

Format Data Series Line Style

Repeat this process for all the connecting lines. When you are done, the chart should look something like this:

Line Connectors Finished

Final Formatting

We’re in the home stretch… There is just some final formatting to do to line everything up, make it easier to read, and make the waterfall pop!

The Series Legend is useless, so let’s get rid of it. Click on the legend and press Delete.

Now, let’s color-code the gains and losses. I chose a green for the gains and a red for the losses, which is pretty standard. Click on the bar for the gain (purple in the last picture). Under Chart Tools, click the Format menu tab. Under Shape Styles, choose the color you want from the Shape Fill drop down.

To better hide the connecting lines, it’s also good to give the bar an outline. Under the Shape Outline drop down choose black.

Repeat the process for the rest of the gains and losses. You’ll need to color code the +Y-Axis and -Y-Axis bars individually, but they should match in color. You can also adjust the color on the final total FY bar.

When you are finished, the chart should look like this:

Color Coded Bars

I prefer to move the X-Axis labels down below the chart to make it easier to read the bars. Just right-click the labels and choose Format Axis to bring up the Format Axis dialog.

Format Axis

Under Axis Labels, choose Low.

Axis Labels Low

Click Close to exit the dialog.

There is a lot of white space on the chart, so let’s close that up to make it more readable. First, we’ll adjust the bar widths… Double-click on one of the chart bars to bring up the Format Data Series dialog. Change the Gap Width to something smaller, like 35%. Click Close.

Gap Width

Next, let’s fix the Y-Axis formatting. Right click on the Y-Axis and choose Format Axis.

Format Y Axis

Most of the bar labels fit within the 350 to -350 range, so let’s set those as the max and min of the chart view. Under Axis Options, change Minimum to Fixed and set it at -350. Change Maximum to Fixed and set it at 350.

Axis Min Max

While we’re here, go to the Number section and change the Category to Currency and set it at 0 decimal places. Click Close.

At this point, we’re pretty close! Your chart should look something like this:

Chart Formatted

As a final step, you can adjust the text size, bold setting, and color to make it more readable. Just click to select an element, and change the font settings under the Home menu tab in the Font section. I brought everything up to font size 14 and made the text bold for readability. I also make the FY total white to make it easier to see.

With that, you should have an effective, easy to read waterfall chart!

Example Chart

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. You can read more of his writing on his personal blog at NapkinMath.io.

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!

Other posts in this series...

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 *