Create a Waterfall Chart in Excel

We have a very famous waterfall here in Canada, and it creates gorgeous photos, like this one from my fall 2008 vacation.

niagarafalls

You can create waterfalls in Excel too -- Waterfall Charts. They might not be as spectacular as Niagara Falls, but can be useful for showing how values change. There are details here 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.

waterfall01

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, please watch this Excel video tutorial.

Link to Excel Waterfall Chart Utility video

_____________

14 comments to Create a Waterfall Chart in Excel

  • ikkeman

    I know, I'm nitpicking, but shouldn't the November bar be negative according to the data?

  • Raf

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

  • @ikkeman, thanks, I fixed the screen shots. Never change your numbers mid-experiment!

    @Raf, see Jon Peltier's article – Waterfall Charts that Cross the X Axis

  • Wow, I really like the use of this waterfall chart for cash flow. Great example, and the video was awesome.

  • Thanks Gregory! Glad you liked it, and thanks to Fakhar for suggesting this example.

  • Raf

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

  • Raf

    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!

  • Michael Pierce

    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.

  • @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.

  • Contextures Blog » Pocket Price Waterfall Chart in Excel

    [...] 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 [...]

  • Joe

    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.

  • murthy

    Hi - Thanks for the great tutorial. Can you please show us how to add data values for every column?

  • Derick

    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?

  • Lynda Maynard

    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

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>