Home > Charts> Waterfall

Create an Excel Waterfall Chart

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

What is an Excel Waterfall Chart?

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.

• 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. Follow the steps below, at this link
• Excel 2013 (and earlier versions): There is NO built-in Waterfall chart type. Instead, you can follow the 2013 instructions below, to create one, based on a Column chart.

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.

• Note: For Excel 2013 and earlier, go to the instructions further down the page.

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.

• The starting balance is entered in cell B3, with a "Start" label in cell A3
• Below that, the month names are entered in column A
• The amount gained or lost for each month is entered in column B
• Optional: You could create a SUM formula in row 16, to calculate the final amount, after all the monthly transactions. For example: =SUM(B3:B15)

Create the Waterfall Chart

Next, follow these steps to select the data, and build the chart.

• First, select all of the label cells, and the cells with the numbers. In this example, cells B3: C16 will be selected.
• Next, on the Excel Ribbon, click the Insert tab
• Then, in the Charts group, click on the button that shows a Waterfall chart icon
• The pop-up tooltip has the title, Insert Waterfall, Funnel, Stock, Surface, or Radar Charts

• Next, in the list of chart types, click on the Waterfall chart command
• The pop-up tooltip has the title, Waterfall Chart, and information about this chart type, what it is designed to do, and suggestions on when to use it
  • Tip: When you point to a chart type, a preview appears on the worksheet.

• Finally, to create the chart from the selected data, click the Waterfall chart command
• The waterfall chart is added to the active worksheet, in the centre of the Excel window.
  • Tip: You can drag the chart to a different location on the worksheet.

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.

Chart Title

Either delete the chart title, if you don't need it, or change its text to something meaningful.

• First, click on the chart title, to select it - handles will appear on the chart title's border
• Next, follow one of these steps:
  • To delete the title, press the Delete key on your keyboard
  • Or, to type a static title, use the cursor to select the existing text in the formula bar, and type new text for the chart title
• Note: For most chart types, you can link the Chart Title to a worksheet cell, but that doesn't work for a Waterfall chart. So, delete the chart title, then add a Text Box to the chart, and link it to a worksheet cell.

Total Amounts

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:

• Click on a Total column, to select the entire data series
• Click on the Total column again, to select just that column
• Right-click on the Total column, and click the Set as Total command in the pop-up menu.

The column will change to green fill colour, to show that it is a total amount.

Chart Style

Instead of keeping the default chart style, with numbers shown on each column, you can apply one of the other built-in styles.

• Click on the chart, to select it
• At the right side of the chart, click the Paintbrush button (Chart Styles)
• Scroll through the list of chart styles, and click on a style, to apply it to your chart

Create a Waterfall Chart (2013)

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.

Example 1

Video: Waterfall Chart 2013

--Step 1-- Set Up the Data

--Step 2 -- Enter Waterfall Chart Formulas

--Step 3 -- Create the Waterfall Chart

More Examples

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.

Step 1 - 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 Blank Columns

Next, follow these steps to add blank columns - these will get formulas added, in the next section:

• 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 3 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 Formula

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

• Put this formula in cell F3:   =G3

Base Formula

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 Formula

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 Formula

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 Formula

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

Now that the data range is ready, follow these steps 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

Next, 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.

Next, 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

Next, 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

Finally, 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"

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.

• =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)

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.

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

Cluster Stack Chart

Panel Charts

Box Plot (Box and Whisker)