Waterfall chart using Error bars

Excel 2013 2010 2007 2003
The Waterfall chart is usually used for understanding how an initial value is affected by a series of intermediate positive or negative values. As we have shown, Creating a Waterfall chart can be created using a combination of Excel Bar charts. This tip will show you how to streamline this process using the Error Bar chart. The Waterfall chart is typically used for understanding how an initial value is affected by a series of intermediate positive or negative values.
Waterfall chart Excel 2016

To create the waterfall chart using Error bars, do the following:

   1.   Add two columns with X Errors data and Y Errors data:

Waterfall chart data Excel 2016

D5: = B6 - B5 and E5: = C5, E6: = C6 - C5

   2.   Select the data range (in this example, B5:C11).

   3.   On the Insert tab, in the Charts group, choose the Scatter button:

Scatter Charts in Ecxel 2016

Choose simple Scatter.

   4.   Under Chart Tools, on the Design tab, in the Chart Layouts group, click the Add Chart Element icon, select Error Bars list, and then choose Series 1 X Error Bars:

More Error Bars Options in Excel 2016

   5.   In the Format Error Bars task pane:

Format Error Bars in Excel 2016
  • In the Direction group, select Minus
  • In the End Style group, select No Cap
  • In the Error Amount group, select the Custom option and click the Specify Value button:
    Custom Error Bars in Excel 2016

    In the Custom Error Bars dialog box, choose the Y Errors data range (in this example, E5:E11). See Adding error bars for more details about Error bars.

   6.   On the Format Error Bars task pane, right-click in one of the Error Bar Options and choose Format Error Bars... in the popup menu:

Format Error Bars popup in Excel 2016

In the Format Error Bars pane:

  • In the Display Direction group, select Minus
  • In the Display End Style group, select No Cap
  • In the Error Amount group, near the Custom, click the Specify Value button and then, in the Custom Error Bars dialog box, choose the X Errors data range (in this example, D5:D10).

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.