|| Enhancing Excel Charts | Links | Books | Alphabetical Index ||
Waterfall (or Bridge) charts are used to display the cumulative effect of sequentially introduced positive or negative values. Colour coded floating columns show the impact of each category or element and whether it adds to or detracts from the final value. Often the last column is shown as a complete column in order to show the final cumulative value.
They are increasingly used in Finance for tasks such as in a year-end expenditure forecast to show the impact of each month on the final expected outturn.
In the example (right) the annual worldwide change in consumption is 205. America is adding 160 units while Asia is reducing the total by 40. If all of the values were positive a pie chart might be a better visual aid.
Excel does not have a standard chart type to produce Waterfall Charts, but it is possible to adapt a regular line chart to display the required effect. A small table of calculations will be needed to modify the initial values and generate a chart.
The charts are made more complicated by the possibility of values crossing over the (Y) value axis at 0. This method can cope with positive and negative elements of category without any special adaptation.
|Figure 1. Source data||Figure 2. A partially completed chart|
Some simple calculations are required to produced the data used by the chart. Once they have been set up, they can be reused in other projects. Note that the first row (4) has different formulas to the the subsequent rows. Once formulas have been added to row 5 they can be copied down all further rows.
|Create data labels||Col D||=A4 & " " & TEXT(B4,"#,##0;(#,##0)")|
|'Before' values (1st row)||E4||0|
|'Before' values (other rows)||E5:E10||=F4|
|'After' values (1st row)||F4:F10||=SUM(B$4:B4)|
Highlight the range D3:F10 and start the Excel Chart Wizard. Choose a simple line chart. Right-click on either one of the chart lines to select it and choose Format Data Series from the short menu. On the Options table, tick the box for 'Up-Down Box'. This will add the bars which join the points on the two line series. You may wish to right-click on both one of the up and one of the down bars in order to format them and changes their colours to red or green. The chart should now look similar to figure 2 above.
Right-click on each of the chart lines and format the data series. On the Patterns tab, set both the Line and the Marker to 'None'. The lines should then disappear from the chart, leaving only the up-down bars. You may however choose not to hide the 'After' line in order to more clearly display the cumulative values and to emphasize whether the value is rising or falling.
Some waterfall charts may include additional bars to show the start position, the final outcome total or possibly a sub total for a particular group of the values (e.g. after 3 monthly periods). This requires the data table to be expanded with an additional column (G) and possibly an extra row (11). In this example an outturn value is being added which represents the sum total of the values for Monday to Sunday (i.e. -47). See figure 3 below.
Right-click on the chart and choose the Source Data option from the short menu. Click the [Add] button to create a new series. Specifiy the cells containing a Name (G3) and Values (G4:G11). The 'Category X labels' range will also need to be increased by one row. Note that when you do this the appearance of your chart may change radically and the up down bars may vanish. This can be fixed by right-clicking on the new outturn column and changing the Chart Type to a simple column.
|Figure 3. Expanded Source data|
|Figure 4. Final chart with outturn column|
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: xl_waterfall.htm||Page last updated: Feb14||© MeadInKent.co.uk 2014||CMIDX S7 P7 Y|