How to create Waterfall chart

Excel 2016 2013 2010 2007 2003
The waterfall chart, also known as the cascade or bridge chart, shows the positive or negative change of the initial value under the influence of various factors, such as time. The main purpose of this chart is to show the viewer how the value has increased or decreased over a certain period or under the influence of certain factors:
Waterfall chart in Excel 365

Some time ago, Excel added the ability to use a pre-configured waterfall chart template. There are certain advantages to using this chart type. However, at the moment, the pre-configured template still does not provide the full scope for chart formatting, using multiple data series and combining it with other chart types.

To create a simple waterfall chart like the one above, do the following:

Create additional data

   1.   Add four new columns with the following formulas:

  • Column E (Total) with the total values (starting and final balance),
    • E3: = C3,
    • E9: = C9,
  • Column F (Increase) with only positive values:
    • F4: = IF (C4 > 0, C4, 0),
  • Column G (Decrease) with only negative values:
    • G4: = IF (C4 < 0, -C4, 0),
  • Column H (Empty) with the data that will be hidden in the chart (sum of all positive values in the previous row minus the negative value in the current row):
    • H4: = H3 + E3 + F3 - G4:
Waterfall chart data in Excel 365

Create a column chart

   2.   Select the data range.

In this example, E2:H7, E9:H9.

Note: To select several data ranges, hold Ctrl and choose the ones you need.

   3.   On the Insert tab, in the Charts group, choose the Insert Column or Bar Chart button:

Insert Bar or Column Chart in Excel 365

From the Insert Column or Bar Chart dropdown list, choose Stacked Column:

Stacked Column chart in Excel 365

Excel creates a simple stacked column chart with not really correct data (don't worry, it will be modified later):

Simmple stacked column chart in Excel 365

Reorganize the data series

   4.   Select the chart data by doing one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data button in ribbon Excel 365
  • Right-click on the chart plot area and choose Select Data... in the popup menu:
    Select Data button in popup menu Excel 365

   5.   In the Select Data Source dialog box, select the Empty data series, then click the Move Up button to move it to the first position:

Select Data Source in Excel 365

Add the axis labels

In the Select Data Source dialog box (see step 5. above), under Horizontal (Category) Axis Labels, click the Edit button.

   6.   In the Axis Labels dialog box, select the data for the axis labels:

Axis Labels in Excel 365

Format the first (Empty) data series

   7.   Right-click on the first (Empty) data series columns and choose Format Data Series... in the popup menu:

Format Data Series in Excel 365

   8.   On the Format Data Series pane, on the Fill & Line tab:

  • In the Fill group, select No fill,
  • In the Border group, select No line:
Empty Data Series in Excel 365

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

Simple waterfall chart in Excel 365

Add the connector lines

Connector lines are not necessary for the cascade charts. You can add a new data series to display connector lines using different ways, such as:

  • Add a line step chart,
  • Add a scatter chart for subtotal, then display Error Bars for them:

Add the connector lines points

   9.   To add the connector lines points, do the following:

   9.1.   Add new data to define the points where connection lines need to be added:

Column I with the Subtotals, where I3: = C3, I4: = I3 + C4, etc.:

Additional data for waterfall chart in Excel 365

   9.2.   Right-click on the chart area and choose Select Data... in the popup menu, or on the Chart Design tab, in the Data group, choose Select Data (see step 4. above).

   9.3.   In the Select Data Source dialog box, under Legend Entries (Series), click the Add button:

Add in Select Data Source Excel 365

   9.4.   In the Edit Series dialog box, choose the line points (in this example, I3:I7), then click OK twice:

Edit Series in Excel 365

After closing the dialog boxes, Excel adds the new data series as columns:

Additional data series in the chart Excel 365

   10.   To change the new data series chart type, do the following:

   10.1.   Right-click on the new data series and choose Change Series Chart Type... in the popup menu:

Change Series Chart Type in popup menu Excel 365

   10.2.   In the Change Series Chart Type dialog box, on the Combo tab, for the new data series, select the Scatter chart type:

Change Series Chart Type in Excel 365

Create the connector lines using Error Bars

   11.   To create the connector lines, do the following:

   11.1.   Select the data series with the connector line points, then do one of the following:

  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element dropdown list:
    Add Chart Element button in Excel 365

    In the Add Chart Element dropdown list, choose the Error Bars list, then select More Error Bars Options...:

    More Error Bars Options in Excel 365
  • Click the Chart Elements button:
    Chart Element button in Excel 365

    In the Chart Elements list, choose the Error Bars list, then select More options...:

    Chart Elements - Error Bars in Excel 365

See more about other options for Adding Error bars.

   11.2.   Delete the Vertical Error Bars by selecting them (see how to select different elements in the chart) and doing one of the following:

  • Right-click on any vertical error bars and select Delete in the popup menu:
    Delete Error Bars in Excel 365
  • Click the Delete key on the keyboard.

   11.3.   On the Format Error Bars pane for the Horizontal Error bar (see how to select the Horizontal Error bar), on the Error Bar Options tab:

  • In the Direction group, select Plus,
  • In the End Style group, select No Cap,
  • In the Error Amount group, select the Fixed Value option:
Format Error Bars pane in Excel 365

Excel adds the connector lines to the chart:

Connector lines for Waterfall chart in Excel 365

Format data series marks and lines

   12.   To hide scatter data series, right-click on any data point and choose Format Data Series... in the popup menu:

Format Data Series in popup menu Excel 365

On the Format Data Series pane, on the Fill & Line tab, under Marker, in the Marker Options section, select None:

Marker Options in Excel 365

   13.   To format connection lines, right-click on any error bar, and choose Format Error Bars... in the popup menu:

Format Error Bars in popup menu Excel 365

On the Format Error Bars pane, on the Fill & Line tab, select the Solid line option, and choose the Color and Width:

Format Error Bars lines in Excel 365

Notes: To see a more organic chart, use the same line width for the data series borders and for the error bars:

Simple Waterfall chart in Excel 365

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

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.