Waterfall Chart

Excel waterfall chart (bridge chart) shows how a start value is raised and reduced, leading to a final result.

Before diving into the details, we want to clarify that Excel 2013 does not support the waterfall chart by default (as a built-in chart type). In this case, you must invest more time in creating the chart. In Excel 2016 and above, the waterfall chart is ready to use when your Office package is installed.

You can use color-coded stacked column charts to recognize positive and negative values. This chart type is popular when you want to create P&L reports or dashboards. This tutorial is a part of our chart templates series.

Table of contents:

How to create a waterfall chart in Excel 2016, Excel 2019, or Microsoft 365?

Steps to create a waterfall chart:

  1. Select the range that contains two columns (labels and values).
  2. Select the Insert Tab.
  3. Under the Charts Group, choose the Waterfall Chart icon to insert a new chart.
insert a waterfall chart excel

Your waterfall chart is ready, but take a closer look at the details. The default chart is a very basic implementation. For example, you can not use calculated fields. Furthermore, subtotals are missing by default. To improve the chart, you have to apply additional customizations.

default chart

Add Subtotals to the Waterfall chart

In the example, you want to add a calculated field to summarize the data between January and June.

What are subtotals? Subtotals are visual checkpoints (milestones) in the chart and make the graph easily readable.

Insert a new row and calculate the subtotal using the =SUM(F3:F8) formula. The chart will reflect quickly.

adding subtotal

To change the inserted bar to subtotal, you can apply the ‘Set as Total’ function.

Using the ‘Set as Total’ command

Steps to create Subtotals for the Waterfall chart:

  1. Select the bar that you want to convert to a subtotal
  2. Right-click on the selected bar
  3. Select the ‘Set as Total’ command
how to set as total

There is much room for improvement!

improved waterfall chart

How to create a Waterfall chart in Excel 2010 or Excel 2013?

Sometimes you need to insert a waterfall chart but have an outdated Excel version. No problem with that; use the following step-by-step tutorial and build a chart.

#1 – Create a helper table

First, create a helper table and insert the following columns: ‘Cumulative,’ ‘Start/End,’ ‘Before,’ and ‘After.’ After that, use the following formulas to calculate the values:

  • In cell D3, apply the =SUM($C$3:C3) formula.
  • Start value (E3) = D3, End Value (E16) = D15
  • Before series: =D3 and copy the formula down
  • After series: =D4 and copy the formula down
create a helper table

Press the Control key, hold down and select four columns like in the picture below.

#2 – Insert a stacked column chart

Next, locate the Insert Tab on the ribbon and insert a stacked column chart.

insert a stacked column chart

The result looks like the picture below:

waterfall chart fundamentals

#3 – Change Chart Type and create a combo chart

Now we have three stacked column series, but we need columns only for the Start / End values, so change the chart type. Right-click on the Chart and chooseChange Chart Type.’ Use line charts for the Before and After series. Next, we will use the combination chart to create the waterfall chart. Do not forget to leave the ‘Secondary Axis’ checkbox unchecked.

change chart type

#4 – Add Up / Down Bars to Waterfall Chart

We need to apply a little trick to create “bars” from the line chart. First, select the line chart series. Next, select the “Chart Design” Tab and add new chart elements, Up / Down Bars.

add up and down bars

#5 – Format the chart

To get the bars closer, decrease the gap between the data points. Use the ‘Format Data Series’ task pane. Then, under the ‘Series Options,’ add 100% to the gap width.

modify gap width

Finally, hide the line series. Then, under the ‘Series Options’, apply ‘No line’. The last step is to apply blue for the start and end bars, green for the positive bars, and red for the negative bars.

waterfall chart for excel 2013

Bridge Chart Add-in for Excel

If you have special requirements, use our advanced chart add-in. UDT fully supports the horizontal and vertical waterfall chart. You can build your bridge chart in seconds.

How to insert a complex waterfall chart quickly without manual data entry? First, let’s see another example that uses multiple subtotals that we want to calculate using Excel.

Select the range, then click on the UDT Tab. Next, select the Waterfall Chart icon, choose your style (horizontal or vertical), and click the icon. The add-in will calculate the subtotals, and you will get the result asap. Great, it is not? The chart is compatible with Excel 2013 and newer versions.

UDT-waterfall

Download the practice file.

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.