One of my colleagues recently had a problem creating this type of graph in excel. For those of you that are not aware, a waterfall / bridge graph looks like floating squares (examples from wikipedia):
This type of graph is useful as it allows you to see the positive and negative impact of different variables.
As part of his reporting system, my colleague has to use this type of graph regularly and in the past used a column type of chart in excel, with an extra series of data in the graph (on top of the original and plotted on the 2nd vertical axis) formatted in the same colour as the background with no line, to make the squares appear as if they are floating…
However, one of the (many) limitations of excel is that when the squares go below the horizontal axis, (i.e. below zero), excel canot cope correctly and the floating squares appear incorrectly. To fix it, here’s what you need to do:
DO NOT USE A COLUMN CHART!!!!
Instead create a table of data similar to the example below, remembering to separate the values that are cumulative (will be full columns in the eventual chart) and the values that will become your negative and positive floating bricks.
The “Actual data” row (Row 2) is the current value of each factor you wish to plot. This of course has to be entered manually (or taken from another excel sheet, as you prefer)
The “Change in value” row (Row 3) is calculated by simply subtracting the new value from the old one. So for example, in cell C3, the formula is “=C2-B2” and the formula in C4 is “=D2-C2” etc.
In the Cumulatives row (Row 4) simply copy across those values you wish to eventually appear as full columns, in this case cell B4 is “=B2”
The Before row (Row 5) is basically the value at which you want your floating brick to start. So if the previous value finishes at 2000, that is where your new one will start.
The After row (Row6) is a sum of the before value, plus the change in value. So for C6, the formula is “=SUM(C5+C3)”. Incidentally, you can then add formulas to Row 5 so that for exampl C6 = D5, therefore ensuring that your start point matches the end point of your previous block.
Having just reread the above, it seems a little complicated, however, I have uploaded the example, so hopefully, you will be able to follow it!
Once you have created your data table, insert a LINE CHART (yes I did say line chart!!!) To do this, I highlighted lines A4:G7 and clicked on Insert > Line and chose the 1st option.
The graph will appear something like the one below:
At the minute it doesn’t look anything like a waterfall chart, but don’t worry, it will! Click on the graph and go to Chart Tools > Format > Series “Cumulatives”.
The chart will update slightly, with little square shapes on the two plotted points. Right click on one of these and choose “Change series chart type” and change it to a column chart (the first one). Then repeat this for the Labels (plotted) series.
Once you have done this, the chart should look like the one below:
Now with the Labels (plotted) series still selected, click on Chart tools > Format > Format selection. In the pop up menu that appears, choose “Secondary axis” on the series options tab and press the close button. You should now be able to see a second vertical axis on the graph. Then click on Chart tools > Layout > Data Labels > More Data Label Options…”
In the pop up menu that appears, deselect “Value” and instead select “Category name” and click the close button. Your graph should update with some data labels matching your current X axes titles at the bottom. However, we need to add the real values (and the correct values for the X axis), so right click on the graph, and choose select data. Ensure that the “Cumulatives” series is selected, and then click the Edit button for the Horizontal (Category) Axis labels. Select cells B1:G1. Then select the Labels (plotted) series, and click on the same Edit button. This time select cells B2:G2. The graph should then look like so:
Adjust your second Y axis so that the zero line is in the same position as the other Y axis by double clicking on the axis itself and then choosing a minimum value of -0.2 and a maximum value of 1. Click the close button and then you can delete the axis entirely.
Now all we need to do is add the floating bricks, and this is actually the easiest part (well I think so anyway!)
Left click on one of the remaining two lines. Then choose Chart tools > Layout > Up/Down Bars > Up/Down Bars
Excel will automatically add the floating bars, and adjust them so that positive values are in white, negative values are in black as shown below:
Now all that remains is the formatting. I’ve deleted the legend, formatted both of the line series so that they have no markers and no lines and adjusted the up and down bars so that they appear red and green (red being negative).
And hey presto, a perfectly functional waterfall chart even when the values go below the zero line! Feel free to play with the values in the “Actual data” row (Row1), to see the chart automatically update. Feel free to use, edit, adjust or not as you wish