What Is a Waterfall Chart and Why Would I Need One

Last month your revenue was $40,000 and this month it’s only $30,000? What happened? In Excel, you could print a nice report that shows each revenue stream for last month and this month, so you can compare the amounts.

Waterfall01

You could even create a bar chart to compare the different revenue streams.

Waterfall02

The bar chart lets you see the differences for each stream, but maybe you’d like to see how each revenue stream contributed to the overall change in revenue. A waterfall chart will let you see the changes that occur between a starting point and an ending point. In Excel, you can create a waterfall chart by building a column chart, and making some changes to it.

Create a New Table

The first step in building a waterfall chart is to create a table that calculates the individual changes, and a running total. In the example below:

  • June and July revenues are at the far right.
  • F3 is the total for June.
  • C9 is the total for July.
  • Column D shows the difference, where amounts have gone down.
  • Column E shows the difference, where amounts have gone up.
  • Column B is the running total, from the June start, to the July end.

Waterfall03

Add a Column Chart

To start the waterfall chart, select the range in the thick border (A1:F10), and insert a clustered column chart. Waterfall04

Hide the Running Total

To focus on the revenue stream changes, you can hide the series for the running total:

  • Click on a dark blue column, to select the Run Ttl series
  • On the Ribbon’s Format tab, for Shape Fill, select No Fill

To remove the Run Ttl series from the legend:

  • Click on the legend to select it
  • Click on Run Ttl, then press the Delete key

Waterfall05

Format the Waterfall Chart

Next, you can add a bit of formatting to make the column chart look more like a traditional waterfall chart. To widen the columns:

  • Right-click on any column, and click Format Data Series.
  • In the Series Options category, set the Gap Width to 0%.
  • Close the dialog box.

Waterfall06

To lighten the gridlines:

  • Right-click on a gridline, and click Format Gridlines.
  • In the Line Color category, select Solid Line.
  • From the Color drop down list, select a light shade, such as the lightest grey.
  • Close the dialog box.

In this revenue chart, up is good, and down is bad, so you can change those series colors to red and green. If the End series is red, change it to a different color, to avoid any confusion. To change a series colors:

  • Click on a column, to select its series
  • On the Ribbon’s Format tab, for Shape Fill, select the color you want

And here’s the finished waterfall chart. I also added data labels with a custom number format, to show up and down arrows.

Waterfall07

Waterfall Chart Utility

The example shown here is a very simple waterfall chart, with only a few changes. If you need to make lots of waterfall charts, or more complex charts, you should invest in Jon Peltier’s Excel Chart Utility. To use the utility for the revenue stream data, you could set up a simple table that showed the start and end amounts, and the change in each revenue stream. These are just links to the original table, that had monthly revenue and the change amounts.

Waterfall08

When you install the Chart utility, it adds a Waterfall command to the Ribbon. Select a cell in the table, then click the Waterfall Chart command, to open the dialog box.

Waterfall09

You can change the sort order, and the option to use values as data labels, or go with the default settings. Then click OK, and the chart is created and formatted instantly. Much quicker and easier than doing everything manually!

Waterfall10

So, to save yourself some time and headaches, take a look at Jon Peltier’s Excel Chart Utility. Use it to create waterfall charts, and other custom charts. It’s very reasonably priced, and will quickly pay for itself, in time saved.

________________

You may also like...

2 Responses

  1. Chandoo says:

    very good article. Recently, Aaron, a reader of my blog contributed another article on making waterfall charts using excel. In this you can find how to add connectors between columns as well.

    http://chandoo.org/wp/2009/08/10/excel-waterfall-charts/

    Of course, nothing like the flexibility of using Jon’s utility.

  2. I use Peltier’s waterfall utility extensively. It’s freakin magical. I’m not being cheaky either.

    Anyone who has gone through the pain of creating complex waterfall charts should absolutely take this for a test-drive.

Leave a Reply to Mike Alexander Cancel reply

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