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:
- How to create a waterfall chart in Excel 2016 and above
- Add Subtotals to Waterfall chart
- Using the ‘Set as Total’ command
- Build a Waterfall Chart using UDT Add-in
- How to create a bridge chart manually
How to create a waterfall chart in Excel 2016 and above
- Select data that you want to display
- Insert Tab, click on Recommended Charts
- Select the Waterfall Chart icon to insert a new chart
Okay, it was quick. Take a closer look at the chart. It is a basic implementation.
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.
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:
- Right-click the data point.
- Select the ‘Set as Total’ from the options.
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.
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!
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
Press the Control key, hold down and select four columns like in the picture below. Next, insert a stacked column chart.
Right-click on the Chart and choose ‘Change Chart Type.’ Use line charts for the Before and After series.
Select the line charts and add new chart elements, Up / Down Bars.
To get the bars closer, use the Format Data Series task pane. Under the ‘Series Options,’ add 100% to gap width.
Hide the line series! Under the ‘Series Options’ apply ‘No line.’
Use blue for the start and end bar, apply green for the positive bars, and red for the negative bars.