You can use an Excel Waterfall chart to show the cumulative effect of positive and negative amounts, based on a starting value
You can use an Excel Waterfall chart to show the cumulative effect of positive and negative amounts, based on a starting value.
For example, show the monthly net cash flow amounts in a waterfall chart, and quickly see which months had positive and negative results.
Excel doesn't have a Waterfall chart type, but you can create your own version, by carefully rearranging your data, and using a Stacked Column chart type.
Follow the instructions below, to create your own waterfall chart, and download the sample waterfall chart file to see how it works. The file is zipped, and in Excel 2007 format.
Or, to save time and effort, you can invest in the Excel Waterfall Chart Utility to make the job easier. It's described at the end of this page.
Watch this short video, to see how to create a waterfall chart from your data. Written instructions are below the video.
To create your own waterfall chart, the first step is to set up your data. In the screen shot below:
The next step is to enter the formulas that will be used in the chart. In the screenshot above, values are typed in the yellow cells, and other coloured cells contain formulas. The formulas are listed below the screenshot.
B2: =" "
D4: = - MIN(G4,0)
Copy the formulas in B4, D4 and E4 down to row 15
To create the waterfall chart:
If you don't have the time or patience to arrange your data and create your own Excel Waterfall chart, Excel charting guru, Jon Peltier, offers a time-saving Excel Chart Utility, which includes a Waterfall chart builder, along with 7 other custom chart types. You can relax, while the Peltier Tech chart utility does all the work.
Just select your data, click the Waterfall Chart command on the Ribbon, set a few options, and click OK, and your waterfall chart is ready. A few columns with formulas are added in your workbook, to provide the data for the waterfall chart.
The Waterfall Chart builder even works if the total values are negative, as you can see in the example below.
It's a good investment, and the Peltier Tech Excel chart add-in will quickly pay for itself, because you won't have to waste your time fiddling with data arrangements and chart settings.
In addition to the 8 custom charts, there are useful charting tools.
There is also a data tool, and there are a few general tools, to make your Excel work easier.
You can see how well the Excel Waterfall Chart builder works, in this short video.
Note: These are affiliate links, and I'll earn a small commission if you purchase the chart utility through these links.
Last updated: October 26, 2015 3:54 PM