Create an Excel Waterfall chart to show how positive and negative amounts affect final total amount, based on starting value. Get the sample workbook, watch the video and try the step-by-step written instructions
A waterfall chart will let you see the changes that occur between a starting point and an ending point. You can use a Microsoft 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 a clear picture of which months had positive and negative results.
Different Versions of Excel
The steps that you follow to create a Waterfall chart will depend on which version of Excel you're using.
Make a Waterfall Chart (2016)
In Excel 2016, and later versions, there is a built-in Waterfall chart type, so it only takes a few seconds to create one from your data.
This animated screen shot shows how to use the built-in waterfall chart type. There are written steps below the screen shot. As you can see - it's quick and easy!
Create Waterfall Chart in Excel
First, follow the steps below, to set up the data that you want to show in the Waterfall chart. When the data is ready, then you can create the Waterfall chart, and make a few formatting changes, if necessary.
Set Up the Data
In this example, the chart will show monthly gains and losses, over the course of a year.
Create the Waterfall Chart
Next, follow these steps to select the data, and build the chart.
Format the Waterfall Chart
The default Waterfall chart that Excel created has red and blue columns, to show increases (blue) and (decreases). It also has a generic title - Chart Title.
You can make any formatting changes to the chart that you'd like, and I recommend the following, at least.
Either delete the chart title, if you don't need it, or change its text to something meaningful.
If any of the amounts in your chart represent totals, you can change a setting, so Excel formats them differently.
To format a Total column, follow these steps:
The column will change to green fill colour, to show that it is a total amount.
Instead of keeping the default chart style, with numbers shown on each column, you can apply one of the other built-in styles.
In Excel 2013 and earlier versions, there isn't a built-in Waterfall chart type. For those versions, you can follow the instructions below, to build one yourself.
--Step 1-- Set Up the Data
--Step 2 -- Enter Waterfall Chart Formulas
--Step 3 -- Create the Waterfall Chart
Ex 2 -- Pocket Price Waterfall Chart
Ex 3 -- Slides: Excel Waterfall Chart
Video: Waterfall Chart 2013 Example 1
Watch this short video, to see how to create a waterfall chart from your data, and the 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.
Add Blank Columns
Next, follow these steps to add blank columns - these will get formulas added, in the next section:
Add 3 Rows to Data
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.
Now that the data range is ready, follow these steps to create the waterfall chart:
Next, to format the Base series, follow these steps
Next, to format the Down series, follow these steps
Next, to format the Up series, follow these steps
Finally, to finish the chart formatting, follow these steps
Ex 2: Pocket Price Waterfall Chart
This variation on a waterfall chart is a Pocket Price Waterfall chart, and you can get one in the sample file section below.
That is different in this chart?
Instead of just two highlighted columns (start and finish), like a regular waterfall chart has, a Pocket Price Waterfall chart has several highlighted columns.
Formulas for 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.
The Base column is the reverse, showing the sum if the cell in column G is NOT empty.
Completed Pocket Price Waterfall Chart
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.
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: July 22, 2023 4:19 PM