Visually Display Composite Data — How to Create an Excel Waterfall Chart

Have you ever needed to present a chart that displays component data that roll up to a total? If all of the values are positive, Excel’s stacked column chart works beautifully; but it breaks down when your data includes both positive and negative values.

The answer is an Excel waterfall chart! A waterfall chart solves the problem by allowing each data point to begin where the previous data point ends. This is an excellent choice for illustrating cumulative numbers or running totals, even when some values are negative.

Unfortunately, Excel does not include a native waterfall chart, but you can create one with only a few simple formulas.

Step 1: Organize Your Data
1. In Column B, enter your source data.
2. In Column C, create a running total (C3=B3+C2).

You might be tempted now to insert a stacked column graph. This would work if all of your data were positive, but those values that cross the x-axis will foil the graph. To solve this problem, you’ll need to trick Excel into producing the graph that you want to see, and the trick will require some additional formulas.

Step 2: Add Padding and Value Formulas
1. In Cell D3, type:

=IF(AND(C2>0,C3>0),MIN(C2:C3),IF(AND(C2<0,C3<0),MAX(C2:C3),0))

Copy this formula down Column D. This formula checks whether the bar for this data point will be either entirely above the axis (C2 and C3 both are positive) or entirely below the axis (C2 and C3 both are negative). In either case, the bar will need padding to separate it from the axis. If both are positive, the padding is positive; and if both are negative, the padding is negative.

2. In Cell E3, type:

=IF(D3=0,IF(C3>0,C3,C2),IF(D3>0,ABS(B3),0))

Copy this formula down Column E. This formula returns the portion of the bar to be graphed above the x-axis.

3. In Cell F3, type:

=IF(D3=0,IF(C3<0,C3,C2),IF(D3<0,-ABS(B3),0))

Copy this formula down Column F. This formula returns the portion of the bar to be graphed below the x-axis.

Now you have all the formulas and data that you’ll need.

Step 3: Create the Chart

1. Highlight the values in Columns D:F and, from the “Insert” tab, add a stacked column chart:

It doesn’t look much like a waterfall chart yet, but it’s the right start. Series1 is the padding, which you’ll hide, and Series2 and Series3 together are your data.

2. Right-click on one of the Series1 value bars and choose “Format Data Series.” Change the fill to “No fill” and the border to “No border.”

3. Right-click on one of the Seres3 value bars and choose “Format Data Series.” Change the fill and border colors to match those of Series2.

See how each bar begins at the same level that the previous bar ends?

4. Select the legend and press the delete key. When you use this method for a waterfall chart, the legend will only cause confusion.

5. Right-click the graph and choose “Select Data.” Change the horizontal (category) labels to the state names.

6. For a visual representation of positive and negative values, right-click on the positive values (Delaware, New York, Rhode Island, and Virginia) and change their colors.

Be sure to select each individual data point, not the entire series. For values that cross the x-axis (in this case, Virginia), select and change both the upper and lower portions.

7. Here is the final chart! Now you can make any additional format and label changes that you would like. For example, in my final chart, the first image you saw, I added directional arrows to further illustrate my chart simply by adding them under the “Insert Shapes” tab.

Next Steps
Enhance your next financial statement summary, budget variance report, or business forecast with one of these gems. You have all you need to create a waterfall chart. Of course, once you’ve mastered the technique, try exploring advanced options such as multiple values, subtotals, and compound values.