How to Create a Waterfall Chart in Excel

Step-by-step

A waterfall (or bridge) chart shows how a starting value is built up or worn down by a series of positive and negative changes. The first and last columns are totals, and the floating columns in between show each rise or fall. It’s ideal for monthly cash flow, sales analysis or product earnings.

1. Set up the table

Start with a start value and the sales change for each month (some up, some down), followed by an end row. Add three helper columns: Base (B), Negative (C) and Positive (D), with the monthly change in column E.

2. Split each change into negative and positive

The negative column shows falls as positive numbers, and the positive column shows rises. One way to write them, in row 5:

• Negative (C5): =IF(E5<=0,-E5,0)
• Positive (D5): =IF(E5>=0,E5,0)

Copy both down the table.

3. Calculate the invisible base

Each floating column sits on an invisible base. In B6:

=B5+D5-C6

Copy it down, including the end row.

4. Insert a stacked column chart

Select A4:D18 (leaving out the sales-change column) and go to Insert > Column Chart > Stacked Column.

5. Hide the base

Click the Base series, right-click and choose Format Data Series. Under Fill & Line, set No fill and No line. Delete Base from the legend. It now looks like a waterfall chart.

6. Colour and style it

• Colour the start and end columns blue (select each one, then Format > Shape Fill).
• Colour the negative series red and the positive series green. A bevel shape effect adds depth.
• In Format Data Series, reduce the Gap Width to between 0% and 20%. The video uses 0%.

7. Add data labels

Select a series and go to Chart Design > Add Chart Element > Data Labels > Center. Delete any zero labels and format the rest. Your waterfall chart is ready.