Excel Waterfall Chart

Excel waterfall chart (bridge chart) shows how a start value is raised and reduced, leading to a final result. To recognize between positive and negative values, you can use color-coded stacked column charts. This chart type is popular in the financial sector and sales.

Before we take a deep dive into the details, we want to clear up a few things.

Excel 2013 does not support the waterfall chart by default (as a built-in chart type). In this case, you have to invest more time to create the chart.

In Excel 2016 and above, the waterfall chart is ready to use when your Office package is installed.

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.

Table of contents:

  1. How to create a waterfall chart in Excel 2016 and above
  2. Add Subtotals to Waterfall chart
  3. Using the ‘Set as Total’ command
  4. Build a Waterfall Chart using UDT Add-in
  5. How to create a bridge chart manually

How to create a waterfall chart in Excel 2016 and above

  1. Select data that you want to display
  2. Insert Tab, click on Recommended Charts
  3. Select the Waterfall Chart icon to insert a new chart
how to create a waterfall chart in excel using recommended charts 01

Okay, it was quick. Take a closer look at the chart. It is a basic implementation.

basic waterfall chart example

You can not use calculated fields, and it’s a bit tricky to use subtotals. So you have to apply some customization to finalize the chart.

Add Subtotals to 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 in the chart and make the graph easy readable.

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

insert a subtotal manually

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

Using the ‘Set as Total’ command

Steps to create Subtotals for Waterfall chart:

  1. Right-click the data point.
  2. Select the ‘Set as Total’ from the options.
set as total

Result:

the waterfall chart looks much better

There is much room for improvement!

Build a Waterfall Chart in Excel using UDT Add-in

How to insert a complex waterfall chart quickly without manual data entry?

First, let’s see another example, and now we have more than one subtotals that we want to calculate using Excel.

Select the range, then click on the Waterfall or Vertical Waterfall icon. If you want to calculate subtotals, please leave blank the cells.

udt waterfall chart tools

Click on the Waterfall Chart icon on the ribbon. Next, 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 2010 and newer versions!

automated waterfall charts with a single click

How to create a bridge chart (for ALL Excel versions)

You may want to insert a waterfall chart, but you are using an outdated Excel version. No problem! Use the following step-by-step tutorial and build a chart in a few steps.

Create a helper table! You need to insert the following columns: ‘Cumulative,’ ‘Start/End,’ ‘Before,’ and ‘After.’

Use 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
helper table for excel bridge chart

Press the Control key, hold down and select four columns like in the picture below. Next, insert a stacked column chart.

insert a stacked column chart

Result:

prepare bridge chart

Right-click on the Chart and choose ‘Change Chart Type.’ Use line charts for the Before and After series.

use line charts

Select the line charts and add new chart elements, Up / Down Bars.

waterfall up down bars

To get the bars closer, use the Format Data Series task pane. Under the ‘Series Options,’ add 100% to gap width.

add 100 to gap width

Hide the line series! Under the ‘Series Options’ apply ‘No line.’

apply no lines to hide series

Use blue for the start and end bar, apply green for the positive bars, and red for the negative bars.

final chart

Download the practice file!

Additional resources: