- 1.Examining the Waterfall Chart
- 2.Building the Data Table
- 3.Filling in the Data Table
- 4.Starting to Build the Waterfall Chart
- 5.Formatting the Waterfall Chart
- 5.1.Changing the Bridge Series to Line Connectors
- 5.2.Hide the Spacer Bars
- 5.3.Adding Data Labels
- 5.4.Finishing the Connector Lines
- 5.5.Final Formatting
- 6.Waterfall Chart Example File Download
When you want to see how different parts of a total contribute to the final calculation, a waterfall chart (sometimes called a cascade chart or a bridge chart) can be a very useful visualization tool. Unfortunately, Excel doesn’t have a built-in waterfall chart option. With a bit of creativity, however, it’s possible to build one using a modified stacked bar chart. This tutorial will show you how to build your own waterfall chart, complete with different colors for positive and negative values and connecting lines…
Examining the Waterfall Chart
Normally, we’d start by looking at the data set we are going to use, but a waterfall chart is not a standard chart type in Excel. To understand what we need to do to make one, it’s useful to examine what goes into making a waterfall chart work in Excel.
The first thing to note is that the chart is divided into the positive Y-axis and the negative Y-axis, and there are separate bars for each side. This is because the waterfall has to step from piece to piece on its way to making up the total at the end. If we provided positive and negative values in a single data series, Excel would layer them on top of each other along the X axis. We’ll need to break down our data later to match these two sides of the axis.
In addition to the split Y-axis, the waterfall chart is broken up into different elements that make up the data bars, the connecting lines, invisible spacer bars that off-set the parts of the waterfall, and the data labels.
All of these pieces will require separate lines in a data table in order to make up the complete chart. If it seems like a lot of work, don’t worry… This design will automatically do most of the data crunching, leaving us to choose the important things, like colors and formatting.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- Save a Broken Chart with Switch Columns/Rows in Chart Tools
- Build a Better, Cleaner, More Professional Line Chart
- How To Add an Average Value Line to a Bar Chart
- How to Add a Vertical Line to a Horizontal Bar Chart
- How to Add Totals to Stacked Charts for Readability
- How to Show Percentages in Stacked Bar and Column Charts
- Building Charts with Multiple Series and Custom X-Axis
- How to Create Waterfall Charts in Excel