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 written instructions

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.

Set Up the Data

To create your own waterfall chart, the first step is to set up your data.

In this example:

• Starting amount is entered in cell B3
• Month names are listed in cells A3:A14
• Monthly cash flow amounts are entered in cells B3:B14

Extra columns and rows will be added to the data, as described below.

Add Calculation Columns

Next, follow these steps to add the calculation columns:

• Insert 5 columns between the list of month names, and the list of cash flow amounts.
• Add these headings to the 5 new columns: Base, End, Down, Up, and Start

Add Rows to Data

Next, follow these steps to add 3 rows to the data range:

• Insert a row above the Start row. This will create spacing at the left of the chart
  • (optional) Type any value in cell B2. This is just a reminder that the row should not be deleted.
• In cell A16, type a new label: End
• In cell B17, type any value. This row will create spacing at the right side of the chart, and the value is a reminder to include the row when selecting data for the waterfall chart

Here is the revised data layout, with the rows added

Enter the Waterfall Chart Formulas

The next step is to enter the following formulas that will be used in the waterfall chart.

Start

The Start formula returns the starting value, from the net cash flow column.

• Put this formula in cell F3:   =G3

Base

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

• Put this formula in cell B4:   =SUM(B3,E3:F3)-D4
• Copy the formula down to B16

NOTE: All the Base results will be the same for now, until the other column formulas are added.

Down

Next, you'll create the Down formula. Down is a list of negative numbers in the net cash flow column

• Put this formula in cell D4:   = - MIN(G4,0)
• Copy the formula down to D15

Up

Next, you'll create the Up formula. Up is a list of positive numbers from the net cash flow column

• Put this formula in cell E4:   =MAX(G4,0)
• Copy the formula down to E15

End

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.

• Select cell B16
• Drag the End formula cell to the right, placing it in cell C16

Create the Waterfall Chart

To create the waterfall chart:

1. Select cells A1:F17 -- the heading cells, data cells, and spacer row cells.
• DO NOT include the column with the Net Cash Flow numbers.
2. On the Excel Ribbon, click the Insert tab
3. Click Column Chart, then click Stacked Column

To format the Base series, follow these steps

1. Right-click on the Base series, and click Format Data Series
2. In the Format Data Series pane, click on the Fill and Line tab (paint can icon)
3. Format it with no fill and no border, so it isn't visible in the chart.

To format the Down series, follow these steps

1. Right-click on the Down series, and click Format Data Series
2. Click on the Fill and Line tab (paint can icon)
3. Format it with Solid fill, and select red as the fill colour

To format the Up series, follow these steps

1. Right-click on the Up series, and click Format Data Series
2. Click on the Fill and Line tab (paint can icon)
3. Format it with Solid fill, and select green as the fill colour

To finish the chart formatting, follow these steps

1. Format the Start and End columns with grey fill colour
2. Click on any column, to select it
3. In the Format Data Series pane, click on the Series Options tab (column chart)
4. Under Series Options, reduce the Gap Width to a small amount, about 10%
5. Close the Format Data Series pane
6. To remove the Legend, click on it, then press the Delete key
7. Change the text in the default chart title, to "Cash Flow"

Pocket Price Waterfall Chart

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.

Get the Sample Files

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.

More Links

Jon Peltier's Toolbox Add-in

Panel Charts

Box Plot (Box and Whisker)