Simple Waterfall Chart with Up-Down Bars
by Jon Peltier
Tuesday, December 16th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
My colleague Mike Alexander has reviewed my tutorials for building waterfall charts*, and thinks a line chart with up-down bars is easier than a stacked column chart. In several ways, Mike is right. Up-down bars can span from negative to positive values, while a column chart bar can only span numbers on the same side of the axis. This means you need fewer series to get the same visual effect. On the other hand, your formatting options are limited to two fill patterns in up-down bars (up and down), while each column in a column series can be individually formatted. In addition, if you make a horizontally oriented waterfall chart (or other stacked bar chart type), you are stuck with stacking of bars, because up-down bars only work in the vertical alignment.
*I’ve written about Waterfall Charts, “Fancy” Waterfall Charts, and Waterfall Charts that Cross the X Axis on my web site, all using the stacked column chart approach, and I’ve built demo and pro versions of a Waterfall Chart Utility.
If we can apply the K.I.S.S. principle to only half of the cases, it’s still a net gain. So I’ll use the example Mike sent to me, adjusted so much that he is unlikely to recognize it. The data is in A2:B9 in the table below, with an initial value and monthly changes. The chart data is in D2:H10, with the necessary calculations described below the table.
The month labels and the values have been switched left-to-right, so they will produce the desired category axis labels. The final value has been calculated in D10 as the sum of the initial value and the monthly values. There are three value columns: Column contains data for the columns at the beginning and end of the chart, and Starting and Ending show the values at the start and end of each month. The Column data includes the actual initial and final values, while the Starting column contains the #N/A error value. The formula in H3 is
=IF(ISNA(G3),0,G3)+D3
and this is filled down to H10. The formula in G4 is
=H3
which is filled down to G9.
The chart is made by selecting the range D2:H10 and creating a line chart. Since D2 and E2 are blank, Excel will use both columns D and E for category axis labels, with column D’s labels closest to the axis.
The first step is to select the column series. This series is hidden behind the Ending series, so select any series and use the up and down arrow keys to cycle the selection until it is selected. Then change the series to a column type.
The gap width of this series has been changed from 150 to 50 so thee columns are not so narrow. The next step is to add up-down bars to the line series. This is done in the Format Series dialog, on the Options tab, in Excel 2003 and earlier, or by clicking on the up-down bars button on the applicable Chart Tools tab in the Excel 2007 ribbon.
As described in my tutorial Stock Charts and Other Line Chart Tricks, the up-down bar feature connects the first and last line chart series in the chart with an up bar if the value increased from first to last, or a down bar if the value decreased. By default, up bars are filled white and down bars filled black. This is the same feature that produces candlestick-style stock charts.
The up-down bars can be filled with any appropriate color.
After creating and formatting the up-down bars, the line series are formatted to display no markers and no lines. In this chart, the category axis labels have been moved to the low position.
The up-down bars are narrower than the initial and final columns, because they also have a default gap width of 150. This is changed to 50 on the Options tab of the Format Series dialog in Excel 2003 and earlier. Due to an oversight in the Excel 2007 charting interface, the gap width of the up-down bars can only be changed using VBA in Excel 2007.
I’m in the process of designing an add-in which will provide a 2003-like charting interface to Excel 2007. It will ease creation and formatting of up-down bars and other chart elements. I’ve already described the error bar portion of this new interface in Error Bars in Excel 2007, and you can download the error bar utility from that post.
Mike Alexander has written a number of books related to data manipulation and presentation in Excel and Access. Among other books, Mike has authored Pivot Table Data Crunching for Microsoft Office Excel 2007 (with Bill Jelen), as well as Excel 2007 Dashboards and Reports For Dummies, which is among the most comprehensive Dummies books I’ve come across.
Related Posts:
- Candlestick Alternative: Individually Colored Up-Down Bars
- Simple Box Plots
- Waterfall Chart Utility
- Stock Charts in Excel 2007
- Add Percentages on the Secondary Axis
- Callout Labels with Error Bars
- Draw an Axis With Tick Marks But No Line
- Custom Error Bars in Excel Charts
- Add Individual Target Lines To Each Cluster in a Column Chart
- Error Bars in Excel 2007 Charts
Posted: Tuesday, December 16th, 2008 under Chart Types.
Comments: 15
Comments
Comment from Reina
Time: Tuesday, December 16, 2008, 3:54 pm
The explanation of waterfall charts is good on this website, except one thing. How do you know which values go in which cells so that the chart formats properly. Same with the formulas. I can make them in Excel by reproducing what I have learned, but I do not understand the WHY of it.
Thanks
Comment from Jon Peltier
Time: Tuesday, December 16, 2008, 4:59 pm
You have to have a sense for how the chart should look, and for how your data will move you toward that appearance.
In this case, you need the initial and final values to go into the endpoints of the Column series. You need the first value on a given day to appear in the Starting series, and the last value on a given day to appear in the Ending series. This depends on what data you start with. In this case, given the change in value, you get the starting value from the previous day’s ending value, then add the change.
Comment from Mike Alexander
Time: Friday, December 19, 2008, 11:05 am
Thanks for exploring this with us Jon. Adding the Initial and Final columns is a nice touch I hadn’t thought about.
I’m eagerly awaiting your new classic charting options add-in.
Comment from Jon Peltier
Time: Friday, December 19, 2008, 11:42 am
Mike -
Thanks for suggesting the topic. I’d considered writing about it, but you provided the kick in the pants.
“I’m eagerly awaiting …”
I probably shouldn’t have said anything, because it will take some time to get this ready :-(
Comment from savithri.v
Time: Wednesday, June 17, 2009, 12:02 pm
Dear Sir,
This refers to the explanation given in waterfall charts that crosses X axis.
I know it is preposterous on my part to even suggest the following to YOU, but how much ever I tried , I couldn’t see any links/ comments further for this chart which would have cleared my doubt
In the example that is used, if the formula for G column is as stated therein, then should not the value in cell G9 be -6.35 instead of -9.87 as required?
Could not the formula in column G be simply be ‘IF(AND(C20),C2,0)’ ?
As I understand it, for Green + & Green – columns, you have the following logic:-
1. If the deviation is > 0 (favourable), only then Green + or Green – column values have values
2. If deviation is > 0, for green + column, you check whether after effect in the current row is > 0, if yes, you plot current after effect
3. If deviation is >0, for green – column you check further whether the previous row effect (before the current deviation) is < 0, if yes, the idea as I understand it is to pick up previous row value. This is required as you need to show in green, the previous cum – value + the current row favourable effect
So instead of the formula in G3 as “IF(AND(C20),MAX(-B3,C2),0)”, can we not simply use IF(AND(C20),C2,0) ?
4, If deviation is +, Previous row cum is +, current row cum is + , Green + is current cum and green – is zero
If deviation is +, Previous row cum is -, current row cum is +, Green + is current cum and green – is previous cum
If deviation is +, Previous row cum is -, current row cum is – , Green + is zero and green – is previous cum
It is possible that the previous cum & current cum have same signs that is it is both + or both –
In such a case either green + or green – will automatically get skipped, because Green + will not pick in case current cum is – and green – will not pick if previous cum is +
But in a 3rd case, where there are opposite signs for previous cum & current cum then both green + & Green – values would get filled
This is essential because the deviation is making earlier negative cum, now a positive cum value, which means entire below 0 Axis values & above 0 X axis values have to be painted green
Pls bear with me for this loooong comment
Savithri
Comment from Jon Peltier
Time: Wednesday, June 17, 2009, 5:01 pm
These values are items in a stacked chart, meaning the difference between values. I suspect you have interpreted them as total values.
“If deviation is +, Previous row cum is +, current row cum is + , Green + is current cum and green – is zero”
Current cum = previous cum + gain. Green + is gain (the difference between previous cum and current cum).
“If deviation is +, Previous row cum is -, current row cum is +, Green + is current cum and green – is previous cum”
No, because the green negative (which is a difference) cannot exceed the gain (B3).
“If deviation is +, Previous row cum is -, current row cum is – , Green + is zero and green – is previous cum”
No, Green – is current cum minus previous cum.
Comment from savithri
Time: Wednesday, June 17, 2009, 8:44 pm
Hi Sir,
Yes I understand. The confusion was because of the value in C9, which should read as -4.87 instead of -9.87 as shown.
The cum affter effect of the deviation of volume effect is -4.87.
The previous after effect is -ve, current deviation ( price effect 6.35) is +, making the after effect also + 1.48.
Hence green – is picking up Max of -6.35 and – 4.87 which is -4.87 ( previous row after effect as I understand) .
This along with the value in green + is painting the entire stretch from a negative cum to positive cum ( which is the current favourable deviation) as green in the display
Savithri
Comment from savithri.v
Time: Thursday, June 18, 2009, 1:47 am
Sir,
This is further to what was sent in the morning:-
Your reply to
“If deviation is +, Previous row cum is -, current row cum is +, Green + is current cum and green – is previous cum”
is
“No, because the green negative (which is a difference) cannot exceed the gain (B3). ”
Sir, is it not true that, if the previous row cum is picked, it will never exceed the gain as in any situation, the gain has to be more than the previous row –ve cum, to make the current row cum +ve?
I tried couple of options with + & – If the value in C9 is read as -4.87 instead of -9.87, then everything works fine whether it is your ‘=IF(AND(C20),MAX(-B3,C2),0)’ formula or as bland as mine wherein I pick earlier cum value
My son goes around telling everyone who bothers to listen, that I’ll end up in the next couple of days, plotting a waterfall chart with start point as his previous year performance in school & end point as current year’s
Thanks a Million for all the guidance,
Savithri
Comment from Jon Peltier
Time: Thursday, June 18, 2009, 5:38 am
Savithri -
Gaaa! I see the error (I didn’t notice when I tested the same numbers yesterday). I’ve corrected these values. The formulas were always correct
Comment from Jon Peltier
Time: Thursday, June 18, 2009, 5:51 am
I think the comment I made for that case is what I shoud have applied to the next case.
Whatever, thankss for pointing out the typo in the example. The formulas were correct, but the typo in the table led to a misinterpretation of the protocol.
Comment from savithri
Time: Sunday, June 21, 2009, 2:43 pm
The more I commit errors, the more I am in awe of your logic behind building up the table.
I picked up the deviation values straight away for data label column for some deviation chart, ignoring your formulas. When I found that the data labels were not getting positioned correctly in the graph, I realised that even there, you had ensured a discipline whereby labels are displayed only above the X axis & on top of the stack.
I used all possible permutations & combinations to understand the reasoning behind your colour scheme & formulas & ended up with this:
Blank + & – columns ensure only the row deviation given by red or green is visible
Blank + is to make the patch above X axis from 0 upto cum after effect value invisible.
This implies that Blank + would have values only if both before & after effects are positive
Blank + would take the least value of before & after effects
Blank – is to make the patch below X axis from 0 down to cum after effect value invisible.
Blank – would have values only if both before & after effects are negative
Blank – would take the least ABS value of before & after effects
Red – & + to show negative deviations
Sum of ABS Value of Red + & Red + = Row deviation
Red – will have values only if the after effect is also negative
Red – will pick up either after effect or deviation, whichever is smaller number in ABS values
Red + will have values only if before effect is +
Red + will pick up before effect or deviation, whichever is smaller number in absolute values
Green – & Green + to show positive deviations
Sum of ABS Value of Green + & Green – = Row deviation
Green – will have values only if before effect is negative
Green – will pick up either before effect or deviation, which ever is smaller ABS
Green + will have values only if after effect is +
Green + will pick up after effect or deviation which ever is smaller ABS
Comment from savithri.v
Time: Tuesday, June 23, 2009, 1:27 pm
When the start point is a huge figure, the deviations in between are not that big a value compared to the start value, then the red & green deviation columns become miniscule in size. Just thought of telling you this…
Comment from Jon Peltier
Time: Tuesday, June 23, 2009, 1:58 pm
Savithri -
Thanks, yes, this is an issue. If you format the up-down bars with a fill color and no border, they can be resolved down to a smaller size. If you use Excel 2003 and earlier, you can manually change the gap width of the bars to help with visibility (Excel 2007 requires some VBA).
Comment from Matt
Time: Friday, February 19, 2010, 5:40 pm
Jon,
This was a great explanation. I had one problem and one question.
Problem = I can’t get the up-down bars to change width. I use Excel 2003 SP3 at work. Can you explain how to make the change?
Question = Is there a way to get the changes (column D) to show in the report as data labels. I tried the ways I know and all I can get are the starting and ending numbers as labels.
Love the site. Thanks again.
Comment from Jon Peltier
Time: Saturday, February 20, 2010, 9:25 am
Matt -
To change the up-down bar width, select one of the related line chart series and format it. On the Options tab is a setting for gap width, which works the same as in a bar chart.
You have to fake the labels. Excel has no built in way to assign labels from an arbitrary range to a set of points. You can use Rob Bovey’s Chart labeler add-in (http://appspro.com/) to add the column D labels to any series in the chart. If you want fine control over the label positioning, you may want to add another series to the chart. Use an XY type, reassigned to the same axes as the line chart series, or a line chart type, where its plot order is not first or last (which affect the endpoints of the up-down bars). Hide the lines and markers for this series, and use the Chart Labeler to add your labels to this series.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.