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?
- How to create a waterfall chart in Excel 2010 or Excel 2013?
- Build a Waterfall Chart using an Excel Add-in
How to create a waterfall chart in Excel 2016, Excel 2019, or Microsoft 365?
Steps to create a waterfall chart:
- Select the range that contains two columns (labels and values).
- Select the Insert Tab.
- Under the Charts Group, choose the Waterfall Chart icon to insert a new chart.
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.
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.
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:
- Select the bar that you want to convert to a subtotal
- Right-click on the selected bar
- Select the ‘Set as Total’ command
There is much room for improvement!
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
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.
The result looks like the picture below:
#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 choose ‘Change 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.
#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.
#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.
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.
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.
