How to Create Waterfall Charts in Excel

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.

Data Entry

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:

Data Table Structure

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.