- 1Examining the Waterfall Chart
- 2Building the Data Table
- 3Filling in the Data Table
- 4Starting to Build the Waterfall Chart
- 5Formatting the Waterfall Chart
- 5.1Changing the Bridge Series to Line Connectors
- 5.2Hide the Spacer Bars
- 5.3Adding Data Labels
- 5.4Finishing the Connector Lines
- 5.5Final Formatting
- 6Waterfall Chart Example File Download
Filling in the Data Table
We’ll fill in the data table in two steps. The first column (Q1) will have one set of formulas, and then the rest will have another. These are the formulas in the first column:
=B1 =MAX(0,B2) =IF(AND(B20>0,B2>=0),B2,0) =IF(AND(B20>0,B2<0),ABS(B2),0) =MIN(0,B2) =IF(AND(B20<0,B2>=0),-1*ABS(B2),0) =IF(AND(B20<0,B2<0),B2,0) =B20 =B2
The next column is as follows:
=C1 =IF(AND(B20>0,B20+C2>0),MIN(B20,B20+C2),0) =IF(AND(B20>0,C2<0),MIN(B20,ABS(C2)),0) =IF(AND(C2>0,B20+C2>0),MIN(C2,B20+C2),0) =IF(AND(C20>0,C2>=0),C2,0) =IF(AND(C20>0,C2<0),ABS(C2),0) =IF(AND(B20<0,B20+C2<0),MAX(B20,B20+C2),0) =IF(AND(C2<0,B20+C2<0),MAX(B20+C2,C2),0) =IF(AND(B20<0,C2>0),MAX(B20,-1*C2),0) =IF(AND(C20<0,C2>=0),-1*ABS(C2),0) =IF(AND(C20<0,C2<0),C2,0) =B16 =C20 =B20+C2
The columns after the first and second can be copied across. All that needs to be changed are the Bridge pair values. The final data table (using the example data) should look like this:
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.
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