Variable Width Column Charts (Cascade Charts)
by Jon Peltier
Monday, February 16th, 2009
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Reader Joe left a comment on the Clustered-Stacked Column Charts article asking how to make column charts with variable width columns. Stacked versions of these charts are often used in marketing where the horizontal axis is divided up into the segments of a market, with the width of each segment proportional to sales in that segment, and the vertical axis is divided into the competitors in each segment, with each block proportional to that competitor’s sales or share of the segment.
I’ll provide my normal warning here, that using areas is not the most effective way to convey information. However, for people familiar with this chart type, it is an effective and customary chart.
Cascade Chart Utility
I’ve received a lot of questions and comments about variable width column charts, or cascade charts, as they are often called. To address this attention, I’ve created a custom Excel utility to create cascade charts from simple data tables. You can read about it in PTS Cascade Chart Utility.
Variable Width Column Chart (Cascade Chart)
Laying Out the Data
For this simple example, we’ll use four entities, each with their own widths and heights.
Like most non-standard charts, we have to use a special layout for our chart source data. Here is how the above data is set up. The first column, the X values, comprise a cumulative total of the series widths: 0, 25, 75 (25+50), 150 (25+50+75), and 250 (25+50+75+100). Each of these values is listed twice. In between each of these values, I’ve inserted another row with the average of the values above and below, with orange text. These orange values correspond to dummy data which will provide labels later in this protocol.
Each series has mostly zero values or blanks. The X values for series Alpha range from 0 to 25: at X=0 the first Y value for Alpha is zero and the second is 100, the height for Alpha; at X=25, the first Y value is 100, and the second is zero. This provides the step up from 0 to 100 and the step down from 100 to 0. At the in-between point where X=12.5, Y is also 100. Data for other series is laid out in the same way.
The column labeled “dummy” holds Y values for a dummy series that will provide the labels in the column marked “Labels”. I’ve colored this text orange to indicate labeling helper data, corresponding with the inserted orange rows above.
Creating the Chart
Select the data in columns E through J and create a stacked area chart.
Select the “dummy” series (select the “Delta” area and press the up arrow) and change its chart type to a line chart.
All the X values in column E are treated as text labels, equally spaced without regard to their numerical value. In Classic Excel (2003 and earlier) go to the Chart menu > Chart Options > Axes tab. The Primary Category axis is listed as Automatic.
Change this setting to Time-Scale. This is somewhat misleading, as Excel time-scale axes only consider dates and ignore times.
The result is that all Y values for equal X values are vertically aligned, as if in an XY chart. Of course, Excel has helpfully converted the values into dates, spaced one month apart.
First change the spacing to 25 (or another value that makes sense with your widths).
Then change the number format of the axis labels to General (or to another format that makes sense with your widths).
Add Labels to the Variable Width Column Chart
If you didn’t need labels, you could have ignored the “dummy” series and the rows with orange X values. But usually you want labels. To do this, I use Rob Bovey‘s free Chart Labeler utility. This utility adds a menu item called “XY Chart Labels” to the Tools menu. It works on any chart type that accommodates chart labels, not just XY charts.
Using the utility, assign the labels in the “Labels” column to the “dummy” series. The labels here have been positioned below the points.
These labels overlap with the axis labels, but there are a few ways to avoid this. You could position the labels above the points. (In this chart, I’ve removed the dummy series from the legend, and formatted it without lines or markers to hide it.) The legend itself could now be removed, because it’s redundant.
If the labels are crowded (the Alpha label barely fits within its column), you could rotate the text, but this makes it more difficult to read.
Depending on the purpose of the chart, you could hide the axis labels, and leave the “dummy” series labels below the chart.
There are a couple of alternative sets of data you could use for the “dummy” series, instead of the zero values that correspond to the bottom of the columns. The table below shows data that produces points which are located at the top of the columns, or at the mid-height of the columns.
Position the “dummy” labels above the points located at the tops of the columns.
Here is the chart again after hiding the dummy series.
Center the “dummy” labels on the points located at the mid-heights of the columns.
Once more without the “dummy” series.
You may be interested in Marimekko Charts, which are 100% stacked variable-width column charts.
Related Posts:
- Marimekko Charts
- Charting Breakdown of Ads in a Television Show
- LOESS Utility – Awesome Update
- LOESS Utility – What the Buttons Mean
- Label Totals on Stacked Column Charts
- How to Edit Series Formulas
Posted: Monday, February 16th, 2009 under Chart Types.
Comments: 34
Comments
Comment from Colin Banfield
Time: Monday, February 16, 2009, 4:22 pm
“I’ll provide my normal warning here, that using areas is not the most effective way to convey information. However, for people familiar with this chart type, it is an effective and customary chart.”
Jon, the two statements almost appear to contradict each other. The first sentence is somewhat of an understatement. As you mention in “Peltier Goes Bar Hopping,” using area to encode data is even worse than using pie charts (I shudder at the thought of a stacked area chart). The example you use illustrates the problem well. Which area is bigger, Beta or Gamma? Alpha or Delta? In what way do you consider the chart to be “effective?”
Comment from Jon Peltier
Time: Monday, February 16, 2009, 8:06 pm
Colin -
Well, you got me. The best way to represent the simple numbers I plotted in this exercise is with an XY chart.
I was using this article as a lead in to the next one, which discusses Marimekko charts. These are stacked-variable-width-column charts, and that sounds terrible. But within the marketing community these are actually a staple, and the way they are used does not lead to the same confusion over areas that you reminded me about here. The line you quoted should probably be affixed to the next article, and the “effective and customary” description removed from this one.
In Marimekkos, the widths and heights are considered separately, so that the width indicates the relative size of a market segment, that is, the relative size of the entire column relative to other columns, and the height of each block in a stack represents the fraction of that block within its stack.
Comment from Colin Banfield
Time: Monday, February 16, 2009, 9:23 pm
Hi Jon: Please continue with the follow up discussion on Mekkos. There’s an interesing discussion on the topic over at Stephen’s site
http://www.perceptualedge.com/example13.php
Comment from gerdami
Time: Tuesday, February 17, 2009, 6:24 am
Just wondering whether this technique could render the “oil cost curve” attached below.
Comment from Jon Peltier
Time: Tuesday, February 17, 2009, 8:19 am
Gerdami -
This is exactly the technique one would use, with the following data and some appropriate formatting.
Again, use of this kind of chart is subject to the limitations of encoding data using areas. Of course, the areas here have no real physical meaning. The widths correspond to predicted reserves of oil, which the vertical dimensions indicate a range of costs.
Comment from derek
Time: Tuesday, February 17, 2009, 3:39 pm
I really like your integrated dummy series on the right. I used to use a separate, neater and more compact series added using Paste Special.., but in the corporate environment I’ve found it breaks my colleague’s brains, and I get endless calls for support when they accidentally misplace something or need to make an adjustment. I’m learning never to attempt a chart type that will leave my control, that does not use a single simple wizardable rectangle as the base of the data. Sigh.
Comment from gerdami
Time: Wednesday, February 18, 2009, 4:30 am
Re: oil cost curve.
Jon, you’re great.
Thanks for sharing your knowledge.
Comment from Owen Bussey
Time: Tuesday, March 10, 2009, 8:24 pm
How would you do this same procedure in excel 2007? I am stuck on how to convert the x axis to time units so I get vertical columns.
thanks,
Owen Bussey
Comment from Jon Peltier
Time: Wednesday, March 11, 2009, 5:25 am
Owen -
That has been moved to the Format Axes dialog. Right click on the axis, choose Format Axis. The dialog opens to the pane shown below. Just above the first horizontal line, there are three radio buttons for Axis Type. Choose the Date Axis option.
Pingback from ggplot2: Variable Width Column Chart « Learning R
Time: Sunday, March 29, 2009, 7:29 am
[...] 2009 March 29 tags: chart, ggplot2, plot, R by learnr Jon Peltier uses several dummy series to create a variable width column chart in [...]
Comment from Gordo
Time: Thursday, October 29, 2009, 11:22 pm
My data set is a carbon cost curve with 20 points adding to around 40 kilotonnes. This means that some of the points are less than one kilotonne and I think the date function cannot handle this small number? I multiplied by 1,000 because my original data was in kilotonnes but now the numbeers seem to large and the graph refuses to cooperate either way! Is there a limited data range that we need to use? (Hint, if I mutiply by 10 it works like magic but the scale shows the wrong numbers)
Comment from Jon Peltier
Time: Friday, October 30, 2009, 8:53 am
Gordo -
The date axis cannot handle fractional values, and I know there is an upper limit as well.
Sometimes I have to use whatever works with the data (your factor of ten, for example), then hide the real axis labels and add a series which has dummy labels where I want them. See Custom Axis Scales using Dummy Series for examples.
Comment from Federico Boccardo
Time: Monday, November 30, 2009, 5:18 am
Dear Jon,
I’m working to build a waterfall chart with variable width coloumn that i can automatize througha macro in excel could you please show to me how I have to format my figures?
Could you give me an email address where I can send to you my work?
Thank you in advance
Federico
Comment from Mayank Singh
Time: Tuesday, September 14, 2010, 12:04 am
Hi,
I have to plot a column chart for the following data table and here, the columns’ width must vary with the ‘rate’ column’s values :-
Time (hours) MWH( Mega Watt Hour ) Rate ( Rs./Unit )
1 600 2
2 100 1
3 1000 3.8
4 1500 1.5
5 800 2.9
How can I make such a column chart using MS-excel 2007?
Please Reply.
Thanks.
Mayank Singh.
Comment from Mayank Singh
Time: Tuesday, September 14, 2010, 12:12 am
In my abovesaid comment the table is not properly arranged. Here are the values again:-
time 1, 2, 3, 4, 5
MWH 600,100,1000,1500,800
rate 2,1,3.8, 1.5, 2.9
Comment from Gordo
Time: Tuesday, September 14, 2010, 1:53 am
Hey Mayank, we all have to start somewhere. Jon has given excellent instructions here but it is much easier if you have done one of these before. I use these tips quite often for MACC curves. I have your chart done already. If you post your email I will send it?
Regards
Gordo
Comment from Jon Peltier
Time: Tuesday, September 14, 2010, 6:20 am
Your table should start out like this (in columns the way you had it first, with Rate before MWH):
just like the first table in this example. Follow the steps carefully, and you should end up with something like this:
I used my commercial Cascade Chart Utility to create this in 60 seconds: 1 second to make the chart and the rest to tweak the formatting.
Comment from Reza
Time: Tuesday, September 14, 2010, 12:43 pm
Hi Jon,
Can you please explain the steps to make the graph asked by mayank (chart for mayank) in excel 2007.
Comment from gerdami
Time: Tuesday, September 14, 2010, 2:10 pm
Could not solve Mayank Singh’s problem manually by using the described technique because of the decimals.
However, I succeeded with a column chart, 5 series and replicated values for each step of 0.10 from 0 to 11.2, overlap set to 100 and gap width set to 0.
Comment from Jon Peltier
Time: Tuesday, September 14, 2010, 8:26 pm
Hi Reza -
The protocol I laid out here works in new and old Excel. Where have you gotten stuck?
Comment from Jon Peltier
Time: Tuesday, September 14, 2010, 8:29 pm
Gerdami -
You need to normalize the X axis data. I used an applicable approach in Fill Below an XY Chart Series (XY-Area Combo Chart) to normalize the X axis values from 0 to 1000.
Comment from Reza
Time: Tuesday, September 14, 2010, 9:04 pm
Sir,
I am confused in which chart type to choose from in the new excel to make it look like the one you generated.
Comment from Jon Peltier
Time: Wednesday, September 15, 2010, 12:32 pm
Reza – The cascade chart is based on a stacked area chart.
Comment from Rich
Time: Tuesday, March 29, 2011, 10:12 am
Hello…. love this chart. Wondering if there is any way to make this into a Stacked Chart as well? What I’m trying to show are:
Stacked Chart with 5 product categories “height” = market size of each category
4 different time periods (Q1, Q2, Q3, Q4)
Now comes the tough part. I would also like to make the Width of each of the stack segments = our Market Share.
So the chart would look like a regular stacked chart but with wide and narrow segments on each stack
Comment from Jon Peltier
Time: Wednesday, March 30, 2011, 2:09 pm
Rich -
You can easily stack these variable width bars. If your data can be aligned like that in A1:D5 below (for only two series), it can be expanded like that in F1:J18, and plotted using the protocol in this tutorial to make the chart shown below.
Your market share widths will be hard to display intelligibly. Your four market share percentages (for the four quarters) will only coincidentally add to 100%, which is how the widths of your segments be constructed.
I suppose you could adjust the widths to the market share widths, and including a span of 100% minus market share with values of zero, within each equal-width quarter. I’ve tried to capture this with the data and chart below.
Comment from Aviad
Time: Sunday, June 19, 2011, 2:37 am
Hi,
Can you please explain how did you calculate the numbers in column F in the chart above (the second one, with the market share percentages…) ?
Thanks for a great solution !
Comment from Aviad
Time: Sunday, June 19, 2011, 8:35 am
… and another thing…
How can I add values inside the stacked bars?
In the chart above, for example, I would like to add in Q1 “100″ in the red part, and “75″ in the blue one, “75″/”70″ in Q2, etc.
10x !
Comment from Rose
Time: Thursday, October 27, 2011, 1:33 am
hi,
I follow up your steps and make the chart, but I can’t make space for Axis X in Excel 2010, meanwhile numbers under Axis X looks crowded and there are overlap of numbers…
in addition, I wasn’t able to change dummy series to line style.
who can help me out? thanks.
Comment from Jon Peltier
Time: Thursday, October 27, 2011, 11:25 am
Rose -
To change the Dummy series, first select one of the visible series (e.g., Delta), then use the up arrow until the Dummy series is highlighted (the highlight symbols are aligned along the tops of the visible series). Then go to Chart Tools > Design > Change Chart Type, and select a line type.
One way to hide the axis labels is to apply a custom number format of ” ” (a space character within double quotes).
Comment from Rose
Time: Thursday, October 27, 2011, 9:18 pm
Jon,
thanks for your help.
now, I know how to hid the axis label and how to change dummy series in to line, but I still can not manage the scale of Axis X, you mentioned to change spacing to 25 right after changing the axis type to Date axis (in Excel 2010), where and how can I to do so… in another word, how can I manage axis X scale? let’s say, 0, 500, 1000…
look forward to your reply. thanks.
Comment from Wei Chi Wong
Time: Wednesday, December 7, 2011, 9:40 am
Jon – is it possible to overlay two different sets of data on the same variable width cascade chart? I’m thinking specifically of two industry cost curves showing two sets of data (different values on both x and y axes for each).
Thanks!
Wei Chi Wong
Comment from Jon Peltier
Time: Wednesday, December 7, 2011, 1:55 pm
Wei Chi -
Construct a chart with one set of industry data. Then add the other set of data, switch it to the secondary axis (and probably you can then delete the secondary axes provided by Excel), and construct a second cascade with this new data.
You may want to show all of your data using lines instead of areas. The technique is the same.
Comment from Wei Chi Wong
Time: Wednesday, December 7, 2011, 2:53 pm
Thanks Jon. I tried what you suggested, but when I add the second set of data, it simply covers over the top of the original data. Say for example I have the first point with a y-value of 500 and the second (secondary data) point with a y-value of 600, I no longer see the first point after the addition of the second. What I’d like to have is for the 500 to show on top of the 600, so that both are evident.
Comment from Jon Peltier
Time: Wednesday, December 7, 2011, 3:30 pm
Wei Chi -
Make your charts using a line chart type, not an area chart.
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.