- 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
Building the Data Table
Start with your basic data entry. In this example, we have quarterly financial figures (Q1, Q2, etc.) that add up to a total fiscal year (FY). Leave a column blank so we can label the parts of the data table later.
We need to systematically build out the different pieces of the chart as separate data series. To make it easier to understand, I’ve build a layout and color-coded the different series:
The Total is just that, a data series to hold the blue total bar at the far right of the final chart.
The +Y/-Y Spacer are the invisible bars that prop up the various floating pieces of the waterfall chart.
The +Y/-Y Loss and +Y/-Y Gain are two categories that make it easier to color-code the positive and negative figures on separate data series.
The +Y/-Y Label are the data series that control the floating data labels for each bar in the waterfall chart.
There is a Bridge data series for each connecting line that goes between the bars in the chart.
Finally, there is a Running Total that keeps track of the data from bar to bar.
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