Creating a Waterfall chart

Excel 2016 2010 2007 2003
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 2013

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:

D: = IF (C6 > C5, C5, C6), E: = IF (C6 > C5, C6 - C5, 0), and F: = IF (C6 > C5, 0, C5-C6)

   2.   Add rows with empty "Y" data, if necessary (in this example 6, 8, 9, 12, 14, 15 and 17) and then add two columns for continuous line (see in step 7).

Waterfall chart data

   3.   Select the data range (in this example D5:F18).

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

Column Charts in Excel 2013

Choose Stacked Column:

Stacked Column Charts in Excel 2013

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


   6.   On the Format Data Series task pane, in the Fill group, select No fill option:

Format Data Series in Excel 2013

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:
  2. In the Select Data Source dialog box, click the Add button:
    Select Data Source in Excel 2013

  3. In the Edit Series dialog box, choose the line points (in this example H5:H18):
    Edit Series in Excel 2013
  4. Right-click in the chart area and choose Change Series Chart Type... in the popup menu:
  5. In the Change Series Chart Type dialog box, choose Combo and then Custom Combination:
    Custom Combination Excel 2013

    Choose other parameters like you want.

  6. Edit data source for add "X" points (repeat steps 1-3, only in step 2 click the Edit button):
    Edit Series in Excel 2013
  7. Format this line.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more