Waterfall charts are very common. You see them everywhere. They let you visualize a series of Business events, drivers or activities to explain 2 data points. An example below is a 2013 Revenue waterfall for a Business. It explains where the incremental revenue in 2013 was generated from.
There are tonnes of excel tutorials out there, but here is one for google spreadsheets.
There are multiple ways to achieve the same result. I like to use a stacked column chart. The live spreadsheet can be downloaded here. Steps:
1. First get the basic data for your waterfall. Mine looks like the one below. Notice the start is 2012 Revenue, the end is 2013 Revenue. And all other rows are either +ve or -ve numbers.
2. Reorganize your data into rows and columns below to plot your stacked column chart.
This is probably the most important step and I'll explain in detail. For our stacked chart, we need 2 bars. One a base bar which will control the height, and then the second bar to plot the positive and negative values for each row. Bar 1 and Bar 6 are the initial and final bars in the waterfall.
Bar 2 to 5 control the height of each waterfall element.
Negative values are trickier, since in a waterfall chart, negative values are displayed as bars starting at the same height as the previous bar, and going down. We achieve that using 2 simple formulaes for the Base and Bar 2-5.
3. Select data from Columns C-J and plot a column stack chart
4. Now some formatting tricks to make the waterfall appear.
a) Select the Base bar and change the fill color to None
b) select the Bar 5 and change it's color to red, since it represents a drop/decrease
c) Change other colors as desired. I like to show growth/incrementals in green
d) Remove the legend
5. Viola! Waterfall chart is ready. Simple right. There is obviously some extra work needed in terms of reorganzing the data into multiple columns to plot the chart but it works
Did you find this helpful? Feel free to share your comments. And if there are better ways of achieving the same, do share them across. Thanks!
There are tonnes of excel tutorials out there, but here is one for google spreadsheets.
There are multiple ways to achieve the same result. I like to use a stacked column chart. The live spreadsheet can be downloaded here. Steps:
1. First get the basic data for your waterfall. Mine looks like the one below. Notice the start is 2012 Revenue, the end is 2013 Revenue. And all other rows are either +ve or -ve numbers.
2. Reorganize your data into rows and columns below to plot your stacked column chart.
This is probably the most important step and I'll explain in detail. For our stacked chart, we need 2 bars. One a base bar which will control the height, and then the second bar to plot the positive and negative values for each row. Bar 1 and Bar 6 are the initial and final bars in the waterfall.
Bar 2 to 5 control the height of each waterfall element.
Negative values are trickier, since in a waterfall chart, negative values are displayed as bars starting at the same height as the previous bar, and going down. We achieve that using 2 simple formulaes for the Base and Bar 2-5.
Value for Base: =if(B3 >= 0 , sum($B$2:B2), sum($B$2:B2) + B3)
Values for Bar2-5: =if($B3 >= 0, $B3, -$B3)
Values for Bar1,6: =$B33. Select data from Columns C-J and plot a column stack chart
4. Now some formatting tricks to make the waterfall appear.
a) Select the Base bar and change the fill color to None
b) select the Bar 5 and change it's color to red, since it represents a drop/decrease
c) Change other colors as desired. I like to show growth/incrementals in green
d) Remove the legend
5. Viola! Waterfall chart is ready. Simple right. There is obviously some extra work needed in terms of reorganzing the data into multiple columns to plot the chart but it works
Did you find this helpful? Feel free to share your comments. And if there are better ways of achieving the same, do share them across. Thanks!
No comments:
Post a Comment