The waterfall chart has been around a while and whenever using them the charts have always frustrated me because they were coloured manually. I decided to have a crack at the problem to produce a chart with 3 different colours, red for when there is a downward trend, green for when there is an upward trend and no colour to help emphasise the movement.
I have done some work in the past and these are the waterfall models I have linked to the Smallman.com
This is an example of the waterfall chart in the attached file.
The colours are added to the above chart automatically which takes some of the leg work out if there is a movement from month to month. Blue is the starting point, red is any negative movement, green represents any positive movement.
Here is some sample data from the above waterfall chart.
The text in blue is the actual data for each year, while the data in the variance column just shows the year on year movement.
The trick is to create 3 columns (I created a 4th for the label) and to use formula to trap the movement.
The White, Red and Green columns establish movement from prior year and each is a series on the chart. The label corresponds with the Value from the first sample data.
The White column shows last year actual minus Red plus Green.
The Red Column shows the variance represented as a positive number only if it was negative.
The Green column shows the variance if it was positive.
The label is the actual data.
The following Excel file outlines the technique with all of the formula for the self colouring waterfall chart.
