Create a Waterfall Chart in Excel

Did you know that you can create waterfalls in Excel — Waterfall Charts?  We have a very famous waterfall here in Canada, which you can see in the photo below from our fall vacation, a couple of years ago.

niagarafalls

Excel Waterfall Charts

Excel’s waterfall charts might not be as spectacular as Niagara Falls, but they can be useful for showing how values change.

There are details below, for creating a simple waterfall chart, and a video that shows the steps.

Net Cash Flow

For example, in a small business, the net cash flow might be a positive number one month, and a negative number the next.

In the Waterfall Chart shown below, the red columns represent a negative number, that brings the cumulative cash total down. Green columns are shown for months with a positive cash flow.

  • The starting value for each red column (negative) is at its top, and the cumulative value for that month is the amount at the bottom of the red column.
  • The starting value for each green column (positive) is at its bottom, and the cumulative value for that month is the amount at the top of the green column.
  • The grey columns (Start and End) compare the original and final amounts, after all the monthly values have been included.
Waterfall Chart for Monthly Net Cash Flow
Waterfall Chart for Monthly Net Cash Flow

Set Up the Excel Data

Excel doesn’t have a Waterfall Chart Type, but you can create one by arranging your data in columns, then adding and formatting a stacked column chart.

In the screenshot below, columns have been added after the dates, to create the series for the waterfall chart. The Base column is used as a foundation for the “floating” green and red columns.

The formulas are shown below the table, so you can see how each column is calculated.

waterfall02

Create the Waterfall Chart

After you set up the data, follow these steps to create the chart:

  1. Select cells A1:F17, and insert a Stacked Column chart.
  2. Format the Base series to have no fill and no border, so it’s invisible.
  3. Reduce the gap width between the columns
  4. Format the columns with the colours you’d prefer
  5. Remove the Legend.

Download the Waterfall Chart Sample File

On the Contextures website, go to the Create an Excel Waterfall Chart page, and you’ll see the formulas used in the waterfall chart data columns. You can also download the sample Excel Waterfall Chart file, to see how it works.

Waterfall Chart Utility

If you need to make more than a couple of waterfall charts, or other custom charts, take a look at Jon Peltier’s time-saving Excel Chart Utility.

It’s very reasonably priced, and will quickly pay for itself, in time saved, aggravation avoided, and possible prevention of hair loss. 😉

Watch the Waterfall Chart Video

To see the steps for setting up your data, and creating an Excel Waterfall Chart, you can watch this Excel video tutorial.

_____________

14 thoughts on “Create a Waterfall Chart in Excel”

  1. What if the waterfall has some negative numbers (at any point)? How would you solve it? I do not know any solution…

  2. @Debra – thanks a lot… actually once I saw this article but I didn’t realize that it is possible without this chart utility (I didnt read properly). I hope one day it will be possible to achieve easier without so many complications!

  3. Debra – thanks a lot… actually once I saw this article but I didn’t realize that it is possible without this chart utility (I didnt read properly). I hope one day it will be possible to achieve easier without so many complications!

  4. In this example, both the Start and End value could be placed in the same column, correct? That way, they would be plotted as one series and you could manipulate them together. They only need to be separated if you want to do something different to each.

  5. @Raf, you’re welcome, and I hope it’s easier one day too.

    @Michael, you’re right, both Start and End could be in the same series. And even if they’re in the same series, you could select a single column and format it with a different colour.

  6. […] good news was that it looked similar to other waterfall charts that I’ve made. However, instead of two highlighted columns, to show start and finish, it had […]

  7. Otherwise very great but it doesn’t work with large negative values (i.e. in case “Base” gets negative). The chart looses the bridging points.

  8. Hi Debra,
    A very nice tutorial, but I’m still at a loss as to the utility of this.
    It’s more work (not difficult, but some effort) to create this than a simple line chart, but I don’t see how this visualization communicates any more information than a line chart does.
    The net balance points are the same, the vertical scale between points are the same. Having red and green colours for positive/negative has the same communicative value of a line that slopes up or down.
    Is this another case of aesthetics over function?
    What am I missing?

  9. Sometimes it’s not even about what is the best or easiest way to show the data, but about what those higher up on the company food chain have their hearts set on. And they don’t want to hear that Excel doesn’t have their favorite chart…

Leave a Reply to Contextures Blog » Pocket Price Waterfall Chart in Excel Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.