Create an Excel Waterfall Chart
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
Excel Waterfall Chart
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.
Video: Create a Waterfall Chart
Watch this short video, to see how to create a waterfall chart from
your data. Written instructions are below the video.
NOTE: To make waterfall charts in just a few seconds,
get the Excel
Chart Utility. This animated gif shows how quick it is!
Set Up the Data
To create your own waterfall chart, the first step is to set up your
data. In the screen shot below:
- 5 columns have been inserted between the list of months, and the
column with Net Cash Flow amounts
- Base is a calculated amount for a series that will be hidden
in the completed chart. It creates a starting point for the
Up and Down series in the chart.
- End is the final column in the chart
- Down is a list of negative numbers in the net cash flow column
- Up is a list of positive numbers from the net cash flow column
- Start is the starting value, from the net cash flow column.
- A row was inserted above the Start row, and it will create spacing
at the left of the chart. An arbitrary value (2000) was entered
there, and any number within the range of net cash flow amounts
could be used instead.
- An End row was inserted at the bottom of the months list
- A row was inserted below the End row, and it will create spacing
at the right of the chart. An arbitrary value (2000) was entered
Enter the Waterfall Chart Formulas
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
B2: =" "
D4: = - MIN(G4,0)
Copy the formulas in B4, D4 and E4 down to row 15
Create the Waterfall Chart
To create the waterfall chart:
- Select cells A1:F17 -- the heading cells and data -- but don't
include the column with the Net Cash Flow numbers.
- On the Excel Ribbon, click the Insert tab, and click Column Chart,
then click Stacked Column
- Click on the Base series to select it, and format it with no fill
and no border, so it isn't visible in the chart.
- Select one of the Down series columns, and format the series with
red fill colour
- Select one of the Up series columns, and format the series with
green fill colour
- Format the Start and End columns with grey fill colour
- Select any column, and on the Excel Ribbon, click the Format tab
- Click Format Selection, and reduce the Gap Width to a small amount,
- Remove the Legend
Download the Sample File
To follow along with the video and written instructions, download
the sample waterfall chart file. The file is zipped, and in xlsx format.
The file does not contain any macros.