Create an Excel Waterfall chart to show how positive and negative amounts have affected the total amount, based on a starting value. Get the free workbook, watch the video and see the step-by-step written instructions
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.
There isn't a Waterfall chart type in Excel 2013 and earlier versions, but you can follow the instructions below, to create one.
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 this example:
Extra columns and rows will be added to the data, as described below.
Next, follow these steps to add the calculation columns:
Next, follow these steps to add 3 rows to the data range:
Here is the revised data layout, with the rows added
The next step is to enter the following formulas that will be used in the waterfall chart.
The Start formula returns the starting value, from the net cash flow column.
The Base is a calculated amount for a series that will be hidden in the completed chart.
The base amount creates a starting point for the Up and Down series in the chart
NOTE: All the Base results will be the same for now, until the other column formulas are added.
Next, you'll create the Down formula. Down is a list of negative numbers in the net cash flow column
Next, you'll create the Up formula. Up is a list of positive numbers from the net cash flow column
End is the final column in the chart. Its formula is already in the worksheet, and just has to be moved to a different position.
To create the waterfall chart:
To format the Base series, follow these steps
To format the Down series, follow these steps
To format the Up series, follow these steps
To finish the chart formatting, follow these steps
A variation on a waterfall chart is a Pocket Price Waterfall chart, and you can get one in the sample file section below.
Instead of just two highlighted columns (start and finish), like a regular waterfall chart has, a Pocket Price Waterfall chart has several highlighted columns.
To create those highlighted columns, I created a new formula in the End column, cells C4:C14, to check the Net Cash Flow column (G). If that cell is empty, show the sum of the previous amounts.
=IF(LEN(G4)=0,SUM(B3,E3:F3)-D4,"")
The Base column is the reverse, showing the sum if the cell in column G is NOT empty.
=IF(LEN(G4)=0,"",SUM(B3:C3,E3:F3)-D4)
Here is the revised waterfall chart, with a highlighted column for each quarterly total, ending in September.
In the sample workbook, I used the same formulas with some faked Pocket Price data, to create the Pocket Price waterfall chart. There aren't any green columns, because all of the values are negative amounts, bringing down the price.
Read more about the Pocket Price Waterfall chart on my Contextures Blog.
Waterfall Chart: To follow along with the video and written instructions, get the sample waterfall chart file. The file is zipped, and in xlsx format. The file does not contain any macros.
Pocket Price Waterfall Chart: To see the Pocket Price Waterfall chart and data, get the sample Pocket Price Waterfall chart file. The zipped file is in xlsx format, and does not contain any macros.
Last updated: October 23, 2021 11:51 AM