As a controller you’re always asked to explain variances in the monthly results against for example budget or a forecast, and a great way of doing this with a chart is creating what’s called a waterfall chart (or sometimes called stair chart).

Using Excel it’s really easy to do. Just start by creating something like this where you have the variances up in the up column and the variances down in the down column.

Column A contains whatever explanation you want to have on each variance (or for the start and the end value whatever you wan them called). Column B is the start or the end value, and column C and D are the values you want to go up or down.

Column E is called blank and that’s the blank area under each up or down bar. The formula in cell E3 is =B2-D3+C2+E2 and that will calculate where the up or the down bar should start to make the chart look nice. Drag the formula down and we’re ready to create the chart.

Both the 2D and the 3D can be used, but I will use the 3D chart in this example because I like the look of the 3D charts. Make sure you don’t select the 100% stacked charts because that wont work at all for this purpose.

Now you got something that looks like this. First this to do is to make sure up is colored green and down is colored red (if down is bad that is). Right click on the bars and select a new color for them under Format Data Series > Fill and Border Color. Also right click on the bars for column E (the blank series) and select no fill as color for fill color. That will hide that series so it’s not showing on the chart, but it’s still there.

Since the blank series that we just hid are on top of our up and down bars we need to move that series to the bottom. That’s done by clicking in the chart and selecting Design > Select Data.

Highlight the Blank series and move it to the top using the arrow buttons. This will place the blank series at the bottom of the chart with both the up and down bars on-top of it.

We’re almost done with our chart, all we need to do now is to move the bars closer to each other. Highlight any of the series and select Format Data Series. Drag the arrow for Gap Width under Series Option to No Gape to move th bars as close to each other as possible.

Now your waterfall chart is ready to present to your boss or the board.

Here’s the excel sheet I worked in to create this blog post: Waterfall_Chart