We have a very famous waterfall here in Canada, and it creates gorgeous photos, like this one from my fall 2008 vacation.
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.
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.
Create the Waterfall Chart
After you set up the data, follow these steps to create the chart:
- Select cells A1:F17, and insert a Stacked Column chart.
- Format the Base series to have no fill and no border, so it's invisible.
- Reduce the gap width between the columns
- Format the columns with the colours you'd prefer
- 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.