Creating a Waterfall chart

The waterfall chart is normally used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns.
Waterfall chart Excel 2010

To create a simple waterfall chart, do the following:

    1.    Add three columns with "Y empty" data, "Y plus" data and "Y minus" data (you can add a column for empty data and a new column with independent data for every unique color in you waterfall chart), where:

C: =IF(B5>B4,B4,B5), D: =IF(B5>B4,B5-B4,0), and E: =IF(B5>B4,0,B4-B5)

    2.    Add rows with empty "Y" data, if necessary (in this example 5, 7, 8, 11, 13, 14 and 16) and then add two columns for continuous line (see in step 7).

Waterfall chart data

    3.    Select the data range (in this example C4:E17).

    4.    On the Insert tab, in the Charts group, choose the Column button:

Charts

Choose Stacked Column. Remove the legend.

    5.    Right-click in the any of first series column and choose Format Data Series...:

popup

    6.    In the Format Data Series dialog box, on the Fill tab, select No fill option and then click OK:

Format Data Series

You can then make any other adjustments to get the look you desire.

    7.    To add the continuous line, do the following:

  1. Right-click in the chart area and choose Select Data... in the popup menu:
    popup
  2. In the Select Data Source dialog box, click the Add button:
    Select Data Source
  3. In the Edit Series dialog box, choose the line points (in this example G4:G17):
    Edit Series
  4. Right-click in the chart area and choose Change Series Chart Type... in the popup menu:
    popup
  5. In the Change Series Chart Type dialog box, choose Scatter with Straight Lines.
  6. Edit data source for add "X" points (repeat steps 1-3, only in step 2 click the Edit button):
    Edit Series
  7. Format this line.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.