Clustered-Stacked Column Charts
by Jon Peltier
Monday, May 19th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
|
I have created the Peltier Tech Cluster Stack Chart Utility to create such charts automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on hundreds of machines in a wide variety of configurations, which saves time and aggravation. The Peltier Tech Cluster Stack Utility creates charts in either horizontal or vertical orientation. Please visit the Peltier Tech Cluster Stack Chart Utility page or the Peltier Tech Cluster Stack Chart Utility Documentation page for more information. |
Excel offers clustered column charts and stacked column charts among its standard options.
A common request is to make a column chart where the columns are clustered together, while one or more of the clustered columns are divided into sections stacked on top of each other. This isn’t built into Excel, but by rearranging the worksheet data, it can be constructed manually.
Suppose we have the simple data below, with four series of data over three months. One series is to be by itself in the left column of the cluster, and three series stacked on the right.
We can select this data and create a clustered column chart
or a stacked column chart
But however we try to arrange series, we cannot achieve a clustered-stacked chart.
If we keep our wits about us, we can envision a worksheet arrangement that will give us what we want. We need a gap, one series stacked in the first filled slot, three series stacked in the second slot, then another gap, and the pattern repeats. So this is really a stacked chart. Where we have gaps, the series have zero values; where one series is alone, the others have zero values; where the three series are stacked, the other has a zero. This new arrangement is shown below. Gaps in the data occur at columns B, E, H, and K. The left column of each cluster has data in columns C, F, and I; the right column in D, G, and J. For clarity in this exercise, I’ve duplicated the column letters in the category labels range (B7:K7, tan). The top left cell (A7) is blank.
Select this range and create a stacked column chart. It’s halfway done, we just need some formatting.
Change the gap width of any series to zero, and it looks almost done.
The axis labels aren’t right, though. We need the months centered between existing labels, for example, “Jan” between the current positions of “C” and “D”. This simple data works; note its arrangement parallels the original data. I’ve used 10 for the values so the bars show up in the charts. When I finish I’ll change the values to zero.
Here is what the chart’s axis should look like when we’re done:
Copy the data in F1:I2, select the chart, and use Paste Special to add the data as a new series, with data in rows, category labels in the first row (we’ll have to reapply this later), and series names in the first column. The data is stacked on top of the first three categories.
What we need to do is put the axis series on the primary axis and the stacked data series onto the secondary axis (if we reverse this, we will have problems displaying the Jan-Feb-Mar axis labels). Select one of the series, and move it to the secondary axis. I usually move the lowest valued series first (Right 3 in this example) so that the secondary axis series don’t obscure the primary series I need to select and move next.
In turn, select and move all of the Left and Right series to the secondary axis. I’ve changed the gap width of the Axis series to 100% (from zero) to make it easier to follow. The Left and Right series are clustered, not stacked, because that’s Excel’s default column chart variation.
Select one of the secondary axis series, and change its chart type to stacked. All column series on that axis will follow suit. Also, change the gap width of a secondary axis column to zero.
Excel has given us a secondary value (Y) axis, but we only have the primary category (X) axis. Using Chart menu > Chart Options > Axes tab, or in Excel 2007, the Chart Tools > Layout tab, to add the secondary axis to the chart. At first, we don’t have the month names as our labels, because Excel applied the existing labels (B through K) to all series.
Edit the series source data or the series formula for the Axis series, so that the months in G1:I1 are used for category labels.
Almost done. The month names are not centered correctly under the clustered columns. Format the scale of the secondary category axis (B through K at the top of the chart) so that the value axis does not cross between categories. In Excel 2003 and earlier, double click on the secondary category axis, and uncheck the box that is labeled “Value (Y) Axis Crosses Between Categories”. In Excel 2007, right-click on the axis, choose “Format Axis”, and at the bottom of the Axis Options tab, select the “On tick marks” option under “Position Axis”.
Perfectly aligned. Now format the secondary category axis to display no tick labels and no tick marks.
Use Chart menu > Chart Options > Axes tab or Ribbon > Chart Tools > Layout tab to remove the secondary value (Y) axis (or select it and press Delete). Format the Axis series on the secondary axis so that it has no fill. Change the Axis series values in G2:I2 from 10 to zero. Select the legend, then select the Axis legend entry, and hide it by pressing Delete.
Not too complicated, and it shows exactly what we want.
Update 7 August 2008
Clustered-Stacked-Column Combo Chart With Lines shows how to add line series to the clustered-stacked column chart.
Update 27 August 2008
I have had a number of comments and email requests asking how to set up data for specific arrangements of columns in a clustered-stacked column chart. I thought it would help if I included some examples of different configurations here.
Example 1: The Original
This is the original layout. Each cluster has two columns in the chart, and two columns in the data table. The first stack in each cluster has one item, so there is one row for this stack in the data table (“Left”). The second stack has three items, so there are three rows for this stack in the data table (“Right 1″ through “Right 3″).
Example 2: Two by Two
This is like the original layout, except both columns in each stack have two items.
Example 3: More Series per Stack
This is like the layout in example 2, except that each stack has four items, and thus four rows in the data table. Each cluster still has two columns in the chart and in the table.
Example 4: More Categories
This is like the layout in example 2, except that there are four categories instead of three (we’ve added a month). There are two columns per stack plus a blank placeholder column, so we added data in columns L and M and another placeholder column in N.
Example 5: Wider Stacks.
This is like the original layout, with one items in the left stack and three in the right stack, with the addition of two items in the center stack. Each cluster needs three columns in the data table, for example, the original data used columns C and D for the first cluster, while this data uses columns C, D, and E.
Update 13 October 2008
To address difficulties applying this protocol to horizontally oriented charts, I have posted a new article, Clustered-Stacked Bar Charts.
Update 24 January 2009
The protocol required to create clustered-stacked charts is rather tedious, and the data layout is complicated, so I have written a Cluster Stack Chart utility that allows the user to set up a simpler data range, and through a dialog select the clustering and stacking configuration. The utility inserts a new worksheet, adds a table with the appropriate data arrangement, and then creates and formats a clustered-stacked column chart from this table.
For more information about the Professional Cluster Stack Chart Utility, visit PTS Cluster Stack Chart Utility, or read the Documentation for the PTS Cluster Stack Utility.
Related Posts:
- Clustered-Stacked Column Chart Utility
- Clustered-Stacked Bar Charts
- Clustered-Stacked Column Charts with Vertical Separators
- Clustered-Stacked Column Chart with Target Line
- Clustered-Stacked-Column Combo Chart With Lines
- Conditional Stacked Clustered Column Chart with Targets
- Variable Width Column Charts (Cascade Charts)
- Label Totals on Stacked Column Charts
- Horizontal Bands in the Background of an Excel 2007 Chart
- Order of Legend Entries in Excel Charts
Posted: Monday, May 19th, 2008 under Chart Types.
Comments: 254
Comments
Comment from misty
Time: Tuesday, May 20, 2008, 5:13 am
i remember to have seen some cool stacked charts
here
Comment from Jon Peltier
Time: Tuesday, May 20, 2008, 7:00 am
Those charts are predominantly eye candy. The only one without gratuitous effects is the one at top right; it only suffers from gradient fill color effects. The others are inflicted with distractions like beveled edges, false 3D perspectives, and transparency. They must not be as good as the new charts in Excel 2007, though, because I saw no shadows or glow effects in any of them.
Pingback from Interesting new blog: Charts and Things » Natural Blogarithms
Time: Wednesday, May 21, 2008, 1:37 pm
[...] are not trivial tasks. Consider for example the Clustered Stacked Bar graph (see below). First off, it is a challenge, in and of itself, to create the clustering of stacked [...]
Comment from Mark Lipis
Time: Thursday, May 22, 2008, 1:48 am
I found this very helpful but… I have encountered a problem wherein the number and labels in the starting table do not flow through to the final chart. I can’t find any way to get the correct and complete labels to appear. I have a file I can share illustrating the problem.
Is there a solution to this problem?
Comment from Jon Peltier
Time: Thursday, May 22, 2008, 7:20 am
Mark – Forward thee file to me at jonxlmvp at peltiertech dot com, and I’ll look at it.
Comment from Bob S
Time: Friday, June 6, 2008, 5:25 pm
This is excellent info – thanks for posting. Solved my problem today!
Comment from Joanna S
Time: Wednesday, June 11, 2008, 11:14 am
Thanks! Solved my problem today too!! YEAH!
Comment from Elly
Time: Saturday, June 14, 2008, 9:14 pm
Hi Jon,
Thank you very much for your great help!! I finally figured it out!!!
Have a great day~ =)
Comment from Bill B
Time: Monday, June 30, 2008, 12:54 am
It’s really a question: I have some strange data that I would like to plot as a bar chart, but I want some of the bars to NOT start at zero. I’d like some of them to go from, say 20 to 90..
Can this be done??
Bill B
Comment from Jon Peltier
Time: Monday, June 30, 2008, 6:52 am
Bill -
This is called a “floating” column chart, because columns float above the zero line. To get this effect you stack the visible columns on columns which are formatted to be invisible (no border, no fill). There is a tutorial on my web site: Floating Column Charts.
Comment from Jenni
Time: Tuesday, July 1, 2008, 5:19 am
Hi John,
Thanks for your tutorial – and the website, I’ve been using lots of these graphs to produce plots for my university thesis – my department has NO graphing package!
I’ve used these instructions to make some clustered/stacked bar charts, and I added error bars to the unstacked bars (like the blue bars in the bottom chart above).
When I do this, for some reason the error bars (selected as upper only) continue through to the x-axis. Which looks pretty stupid!
Have you had this problem before and do you know how to fix it?
Comment from tom
Time: Tuesday, July 1, 2008, 7:12 am
Great examples! But is it possible to add percent change between stacked and non-stacked columns as a data label on top of each column pair?
Comment from Jon Peltier
Time: Tuesday, July 1, 2008, 8:28 am
Hi Jenni -
I can’t visualize the problem. I’ve emailed you off-line. Reply to my email with a workbook that illustrates the problem, and I’ll have a look at it.
Comment from Jon Peltier
Time: Tuesday, July 1, 2008, 8:22 am
Tom –
You can add any custom labels, though sometimes you have to be clever. If your labels will not be positioned properly when applied to a series already in the chart, you will have to add another series, formatted to be hidden, to hold the labels. It sounds like your labels have to be centered above a cluster of columns.
If you’ve used the dummy column series (the pale yellow columns in my example), this can be your dummy series. Instead of using 10 as a dummy value, use values for this series that make the pale yellow bars as tall as the stacked-unstacked pair, hide the series (no border, no fill), and use Rob Bovey’s Chart Labeler to apply the labels to this series. (The labels have previously been entered into a range in the worksheet.)
If you don’t already have a handy series to use for labels, you can add a series, change it to a line or XY type, then apply the labels.
Comment from Jon Peltier
Time: Tuesday, July 1, 2008, 10:21 pm
Jenni sent me her workbook, and I figured out the problem. The secondary category axis was at the top of the chart, which somehow confused the error bar feature. The workaround was simple once I recognized what to do:
1. Right click on the chart > Chart Options > Axes tab, check box for Secondary Y Axis.
2. Double click on secondary Y axis, Scale tab, uncheck box for Category (X) Axis Crosses at Maximum Value.
3. Right click on the chart > Chart Options > Axes tab, uncheck box for Secondary Y Axis.
Comment from LeeAnn
Time: Thursday, July 10, 2008, 2:19 pm
Thank you so much, my problem is solved! I got through it in a snap!
Comment from Dennis
Time: Thursday, July 10, 2008, 3:05 pm
This just seems so insanely complicated–I applaud your efforts to fix Excel’s shortcomings when it comes to stuff like this.
For many years I used CricketGraph for Mac. I’ve been trying desperately to wean myself from it given that it’s a Classic app (and as soon as I switch from a PPC to an Intel, I won’t have a choice). I’m fairly certain that this mixed type of graph–regular column plus a stacked column-could be made in a matter of seconds with CG’s “overlay graph” command. You’d simply create the first column graph, then create a stacked column graph and overlay it on the column graph. No trickery with empty cells in the data, or super gymnastics with the x-axis labels. Why Excel can’t handle this when the technology has been around since the mid 1990s is amazing.
Keep up the good work, John. As long as Excel makes easy things difficult, your efforts will be invaluable :)
Comment from Jon R
Time: Thursday, July 17, 2008, 9:14 pm
This has been very helpful. It took a while to understand exactly what was going on and how to format the data but once I practised a few times, it all became pretty straightforward (and I learnt a lot about MS Graph in the meantime). I had some trouble getting the category names centred but eventually solved the problem by putting 2 blank columns between the paired data columns instead of one. This gave me perfectly centred x-axis labels and also looked better because it gave better spacing between the data columns.
My data had a huge range of values – 1 to about 4,000,000, which looked ridiculous on a normal stacked chart. I tried applying the logarithmic scale but Chart does funny things with logs of low numbers. The overall effect was to make the blank columns show up with values – not what I wanted. I evenutally used the the 100% stacked column chart type (with no logarithmic scale) which worked fine. It was a different way of presenting the picture but it made the point.
Thanks for your website – it’s great.
Comment from Allan
Time: Friday, July 18, 2008, 12:27 pm
AWESOME –
Exactly what I needed. Got me 99% of the way.
Comment from Brett
Time: Tuesday, July 22, 2008, 11:38 am
Awesome stuff. Only thing is, I want to actually graph the stacked column on a secondary axis. Is this impossible since we are already using one to create this effect?
Comment from Jon Peltier
Time: Tuesday, July 22, 2008, 11:44 am
Brett – This should be pretty easy. simply format each series in turn and change it to the secondary axis. The staggering of the data required to position the stacks next to each other will also position the columns in the right position whichever axis they are plotted against. Just make sure that all series stacked in each column are plotted on the same axis.
You should also keep in mind the problems associated with dual axes:
Secondary Axes in Charts
Comment from Chuck Niemi
Time: Wednesday, July 30, 2008, 11:16 am
With the help of this website, I have been able to create a stacked combined chart comparing two years worth of cable cuts. However, one problem persists. The y axis scale bears no correlation to the data values. How do I correct this issue. The data should be shown as multiple units of 1.
Comment from singkit
Time: Thursday, July 31, 2008, 11:34 pm
Thanks. Great help ^_*
Comment from JP Gonzalez
Time: Monday, August 4, 2008, 8:14 pm
Hi Jon,
Thanks for all the help and work you’ve shared with the rest of the Excel user community! I had a question though… can this chart method be combined with the broken Y axis chart? I’m trying it out and can’t seem to get to work YET… Any tips?
Comment from Jon Peltier
Time: Monday, August 4, 2008, 9:03 pm
JP – Both techniques are technically tricky, and both techniques can be deceptive. Combining them can be done, but this can be risky.
You would have to set up a “regular” clustered- stacked chart, then decide where it makes sense to break the axis (or whether it makes sense). You have to then locate the break in the middle of a floating column, trickier than in the middle of a column that sits on the zero baseline.
Comment from jr
Time: Tuesday, August 5, 2008, 1:41 pm
great tutorial…only problem I’m having is getting the y-axes labels to appear on the left hand side of the chart when I finish…all else looks good though!
Comment from Jon Peltier
Time: Tuesday, August 5, 2008, 2:15 pm
JR – Format the appropriate horizontal axis so the vertical axis does not cross at the maximum.
Comment from achao
Time: Thursday, August 7, 2008, 9:54 am
Is it possible to add another layer of complexity to this: put in a line graph which needs to use the secondary Y-axis for its values?
Put another way, I need to do a combination chart of stacked and unstacked bar graph + line graph. Excel’s built in custom chart “line-column on 2 axes” is the format I want it in, but I want the bars to be stacked and unstacked…
Comment from Jon Peltier
Time: Thursday, August 7, 2008, 10:31 am
achao – You can add as much complexity as you want, and often more than is advised. Usually secondary axes add more confusion than they resolve. I’ve posted about secondary axes here:
* Secondary Axes in Charts
* Secondary Axes that Work – Proportional Scales
In any case, you can add your line data to the chart (copy the data, select the chart, paste special as a new series), change its chart type to a line or XY type, then format the series to plot on the secondary axis. If the line series is centered on each cluster, use this data for the dummy X axis instead of the light yellow column series in the example above.
Update…
I decided this made a good blog topic. Clustered-Stacked-Column Combo Chart With Lines shows how to add line series to the clustered-stacked column chart.
Comment from Jacob
Time: Monday, August 11, 2008, 10:17 pm
Excellent site. I don’t think that Excel had this capability and I was planning to resort to Visio for a dumb graph.
Thanks much.
Comment from Rash B
Time: Monday, August 11, 2008, 10:51 pm
hi,
great tutorial… but the prob is that when I move all the series to the secondary axis, only the first values of all the series are visible. i don’t actually have a chart spread over a number of weeks. Also, my X-axis, directly becomes the no. of weeks instead of B, C, D… when i move all the series!!!
Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 8:02 am
Rash –
When you move all the series, you need to make sure the chart has both primary and secondary category axes. You also need to make sure that both axes use the appropriate ranges for the category labels.
Perhaps I’ll post an annotated workbook which documents each step of the process.
Comment from VP
Time: Thursday, August 14, 2008, 12:28 pm
Hi Jon,
Great website and insight! Thanks for your help over many topics! This may be a basic question but I’m trying to create a stacked column chart for a set of horizontal figures as the series for each bar (per month) and then need the next bar to represent another set of figures (month) from another tab. Essentially, I’m trying to compare July to June (and eventually the whole year) but the data keeps stacking up in the same bar (for June). I’m probably not explaining well enough and may be easier to show you with example.
Thanks again for your help!
[VP emailed me to say he'd rearranged his data, and everything was working. I was going to suggest that he try to rearrange his data, since this technique relies on careful data arrangement.]
Comment from Tee
Time: Thursday, August 14, 2008, 1:05 pm
Hi Jon,
Thanks for the informative tutorial. I have been wasted a lot of time in searching for combined clustered and stacked bar function in Excel. However, I have a question regarding this combined bar method. Can I display the biggest stacked value only instead of total value of the series? Take for example, from month Jan, the value is 60,27 and 11. Can I just display the bar which has 60 as maximum point and 3 different colors where one color will be from zero to eleven, the next one will be from 11 to 27 and last color cover from 27 until the end? I still would like to maintain the clustered-stacked column structure but I wish the stacked column will have the displayed method above ( max is at 60 instead of 98). Hope to hear from you soon. Thanks. :)
Comment from Jon Peltier
Time: Thursday, August 14, 2008, 1:23 pm
Tee – Change the largest value to 60-(27+11), then when it is added as another column on the stack, the sizes are right.
Comment from Tee
Time: Thursday, August 14, 2008, 1:36 pm
Thanks for the prompt reply. Just want to message that you I have figured out using the suggested method. Anyway, thanks a million, man. :)
Your work is amazing. Now only I realize that Excel can actually do so many things. :P Might think about switching to Excel next time. Currently, I am actively using Matlab to plot my graphs. Thanks. :)
Comment from Laura
Time: Thursday, August 14, 2008, 4:14 pm
Jon–so much good on this site! Thanks! But (of course, there is a but…) I am working on a stacked column pivot chart project for a customer that must have the y axis gridline scale maximum value and major unit set to specific values. The maximum line needs to be colored red. I can’t seem to find the way to do these things. Suggestions?
Many thanks!
L
Comment from Jon Peltier
Time: Thursday, August 14, 2008, 4:19 pm
Laura -
Does the red line use data from outside the pivot table? If so, then it cannot be included in the pivot chart. Pivot Charts can only use data from the pivot table, so it cannot use external data for the line. Also, if you intend to use an XY type for the line, a pivot table does not allow combinations with as many types as a regular chart allows. Pivot charts don’t allow XY series, among other types.
Comment from Laura
Time: Thursday, August 14, 2008, 6:06 pm
Jon,
Thanks for the quick response!
The red line is just for emphasis. We are mapping out maximum port usage, so the red line indicates peak saturation. I am creating the pivot table and chart programmatically using OLE commands. Old stuff, but works in our old environment–VFP. I ’spose I can always use a border on a very narrow row and place the table just under it. But is there a way to set the max value and major unit in code?
L
Comment from Jon Peltier
Time: Thursday, August 14, 2008, 6:42 pm
Laura -
I envisioned this approach when you mentioned a line:
Naturally it can be done in code.
The axis limits can readily be set in code. Turn on the macro recorder while you do it manually to see what syntax you need:
The code you want looks something like this:
Sub AdjustYAxis()
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 10
.MajorUnit = 1
End With
End Sub
Comment from Laura
Time: Sunday, August 17, 2008, 5:53 am
Jon, sorry for delayed response, I have been out of pocket the last few days. Your suggestions above make sense, thanks. I got frustrated with the macro editor when I began the project because both 2003 and 2007 choke on the pivot table creation, throwing errors with the add (2003) or create (2007) functions.
Example:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
*!* “Data!R1C1:R156C3″, Version:=xlPivotTableVersion10).CreatePivotTable _
*!* TableDestination:=”Max Values!R4C2″, TableName:=”PivotTable2″, _
*!* DefaultVersion:=xlPivotTableVersion10
returns a dialog with Runtime error -5 Invalid procedure call or argument.
That is why I started looking around for a solution and found your site.
Thanks again for your help, and the great site!
Laura
Comment from Jon Peltier
Time: Sunday, August 17, 2008, 9:12 am
Here is a snippet of code from a project that successfully builds pivot tables in Excel 2000 through 2003 (and I believe it also works in 2007).
Set myPT = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, SourceData:=rData) _
.CreatePivotTable(TableDestination:=ActiveSheet.Range("A10"), _
TableName:="PTDiff1", DefaultVersion:=xlPivotTableVersion10)
I can’t tell exactly what’s up with the code you’ve pasted into your comment, but I see a couple of differences. My code assigns the result of the command to a pivot table variable, so the code can address it directly for further modification; yours does not. Your code has a Version keyword in the PivotCaches.Create statement, while my PivotCaches.Add statement does not. Another difference is that my code uses actual ranges, such as a range variable rData and the table destination ActiveSheet.Range(“A10″), while your code uses text R1C1 addresses.
Comment from Robya
Time: Saturday, August 23, 2008, 8:48 pm
Is there a way to show 2 series as 2 stacked columns on 1 chart?
For example –
x-axis = quarters of the year: Q1, Q2, Q3, Q4
left y-axis = projected sales by region as a stacked column
right y-axis = actual sales by region as a stacked column
Regions = America, Europe, EMEA, APAC
This chart can help get a quarterly comparison of projections vs actuals by
quarter and region.
Thanks!
Comment from Jon Peltier
Time: Saturday, August 23, 2008, 9:50 pm
Robya -
Use the procedure shown above, but set up your data like this:
Comment from Richard
Time: Tuesday, August 26, 2008, 3:51 am
Is it possible to have a stacked bar graph like this:-
Actual vs Budget, but to have the actual data a different colour to the budget data.
Thanks
Comment from Jon Peltier
Time: Tuesday, August 26, 2008, 6:48 am
Richard -
Presumably you are stacking components of budget next to components of budget (actual on top of budget wound make no sense). Set up the series so for each combination you use a separate series, that is, budget rent is in a different series from actual rent. Then format each series differently.
Comment from Trisha
Time: Thursday, August 28, 2008, 12:14 pm
Thank you so much Jon! This really helped me. I got a little frustrated on the first try after my labels were not matching up, but after following your steps precisely the second time it worked perfectly!
Thanks again!
Comment from Tony
Time: Thursday, August 28, 2008, 3:49 pm
Jon, thanks for your great suggestions. I’ve used your technique to create a 2×2 clustered-stacked chart but now I can’t use the built-in Trendline function which I was using with a regular clustered chart. Is there anyway that I can add a Trendline to my new stacked-clustered chart? Thanks again.
Comment from Jon Peltier
Time: Thursday, August 28, 2008, 5:22 pm
Tony -
I think Excel knows better than to apply a trendline to stacked series. A trendline also doesn’t make so much sense for a normal clustered chart, since the trendline goes through values at the center of the category, while the clustered series are offset left and right. Also, the Clustered-Stacked chart has a lot of gaps in the categories.
But anyway. What you need to make trendlines is to add line chart series that have points where the tops of the columns are. So one line series has points at the top of the bottom left series (it uses the bottom left series data), the next line series has points at the top of the top left series (it uses the sum of the top and bottom series data). Same for the two right series. Now you can make trendlines using each of the four line series.
I hope this little illustration helps. In the table, the colors have the same meanings as in the rest of the post: tan is the category labels, yellow is the column series names, yellow is the column series data. The new ranges are: pink for the line series names, blue for the line series data. The line series data is selected to place the points on the appropriate columns, and the trendlines are fit based on the line series.
Comment from Vivian
Time: Friday, August 29, 2008, 11:23 pm
Hi John,
I just begin to use charts in Excel. You lost me here, “What we need to do is put the axis series on the primary axis and the stacked data series onto the secondary axis (if we reverse this, we will have problems displaying the Jan-Feb-Mar axis labels). Select one of the series, and move it to the secondary axis. I usually move the lowest valued series first (Right 3 in this example) so that the secondary axis series don’t obscure the primary series I need to select and move next.”
Could you please explain “move it to the secondary axis”? How do I do that?
Thanks
Comment from Jon Peltier
Time: Saturday, August 30, 2008, 9:54 am
Vivian -
When you create a chart, you get two axes. Usually a horizontal axis with categories and a vertical axis with values. In the first charts at the top of the page, the horizontal axis has the Jan-Feb-Mar category labels, and the vertical axis has the values, 0 to 80 or 0 to 100. These are the primary axes.
When you have two or more series of data plotted in a chart, you can choose to plot some of the data on the secondary axes. These are additional axes to show data that may require different categories or a different value scale. In general using the secondary axes causes more confusion than it solves, but I’m using them here to get the label appearance I want.
In this chart, taken from the middle of the example, I have primary and secondary axes:
The primary category (X) axis has the category labels A-B-C.
The secondary category (X) axis has the labels B through K.
The primary value (Y) axis has a scale from 0 to 11.
The secondary value (Y) axis has a scale from 0 to 70.
The instructions leading up to this chart (and the two or three preceding charts) explain how to assign the different series to the appropriate axis.
Comment from Icy
Time: Saturday, August 30, 2008, 1:12 pm
Is there anyway to show the data table under the graph in the correct format?
Comment from Jon Peltier
Time: Saturday, August 30, 2008, 2:48 pm
Ah, data tables. They will be featured in a future post.
Data tables are not very flexible. Everything in the plot goes into the data table, and vice versa. Formatting is limited. Even the order of entries is hard to adjust. Especially in a chart as complicated as this one, data tables are not very useful.
The workaround is to make a regular table using a region of the worksheet near the chart. You can format this table however you want, include and exclude whatever data you want.
Comment from Vivian
Time: Saturday, August 30, 2008, 8:50 pm
I got it! Thank you so much. The project is finally done.
Comment from Valerie
Time: Friday, September 5, 2008, 4:14 pm
Hi John,
We recently converted to Excel 2007. I’m familiar with clustered-stacked column charts in previous version, but struggling with the 07 version to create the same effect.
I’ve followed your directions and the chart is almost complete, but I’m stuck on the section where you center the Jan-Mar labels. You say “Format the scale of the secondary category axis (b-k at the top of the chart) so that the value axis does not cross between categories.”
Could you please point me in the right direction for Excel 07 as to where I would change that.
Thank you,
Valerie
Comment from Jon Peltier
Time: Friday, September 5, 2008, 4:55 pm
Valerie -
I went through the exercise in 2007. It still works, but some things are a bit different, and the names of various check boxes have changed.
Here’s the chart after adding the axis series, and after moving the three “right” series but not the “left” series to the secondary axis. Excel 2007 remembers that the columns were stacked, while Excel 2003 forgets and makes them clustered when moving them to the secondary axis. That is a nice little improvement in 2007.
When the last series is moved to the secondary axis, the appearance changes, not in the same way as in 2003.
Here’s the chart after adding the secondary category axis (labels B through K).
Here’s the format axis dialog for the secondary category axis. What we need to do is change Between Tick Marks at the bottom to On Tick Marks. This is the terminology that changed and probably confused you and all other Excel 2007 users who read this post.
Here’s the chart with the month labels perfectly centered.
Here’s the final chart after fixing everything else that needed fixing.
Comment from Valerie
Time: Monday, September 8, 2008, 11:08 pm
John,
Thank you very much for your additional assistance. I finally succeeded.
Valerie
Comment from Katie
Time: Wednesday, September 17, 2008, 10:04 am
Thanks so much for this!
Comment from John
Time: Thursday, September 25, 2008, 4:50 pm
This was a great help and was exactly what I was looking for.
Thanks
Comment from Chris
Time: Monday, September 29, 2008, 8:50 pm
John,
Great info, but I’m encountering two problems.
1) In examples above, upon adding secondary X axis, the tick marks onr primary X are shown moving between clusters. Mine remain to the right of of each cluster.
2) When I attempt to scale the secondary x axis by unchecking “value axis does not cross between categories”, the first cluster aligns well, but each following cluster is shifted slightly more to the right of the corresponding Axis data (attempting 12 months of data).
These may be related issues and I’ve missed something.
Thanks!
Comment from Jon Peltier
Time: Monday, September 29, 2008, 10:42 pm
Chris -
I think the issues are related. Check to make sure that the data ranges assigned to each series have the right number of blanks everywhere to position the ticks where intended.
Comment from petya
Time: Thursday, October 2, 2008, 2:45 pm
Jon,
your techniques are great. I have an additional kink to the graph that I can’t find a simple and quick answer to.
how do you add vertical lines between the categories. For example, in your graph, how would you put a vertical line between Jan and Feb, and Feb and March.
Also, how do you do this more generally – in case I have 10 more groups?
Thanks!
Comment from Jon Peltier
Time: Thursday, October 2, 2008, 4:44 pm
Petya -
I’ve added a post showing Clustered-Stacked Column Charts with Vertical Separators.
Comment from SY
Time: Thursday, October 2, 2008, 11:35 pm
FANTASTIC! I finally managed to do it. My geeky gf is trying to keep track of the memory usage in her 7 disk drives. Its madness. Thanks for this!
Comment from Sam
Time: Monday, October 13, 2008, 10:49 am
Jon,
Thanks for all the great tips. Quick question…can you do this same stack column graph but rotate it to be a stacked bar graph? I’ve tried but keep running into several issues. My challenge is that my labels are long and look messy when shown as a column graph.
Thanks,
Sam
Comment from Jon Peltier
Time: Monday, October 13, 2008, 11:53 am
Sam -
To address difficulties applying this protocol to clustered-stacked bar charts, I have posted a new article, Clustered-Stacked Bar Charts.
Comment from Ms
Time: Thursday, October 16, 2008, 4:59 am
john i got problem with my data it seem when i add 2nd axis x it show the same with
the 1st..in my data got some column-stack is 0 value.
Comment from Jon Peltier
Time: Thursday, October 16, 2008, 6:45 am
When you switch a series from primary to secondary axis, often you have to change the series data. Even if you specified that it should use a different range for its X values, often it assumes the X values of the other series in the chart. You have to change its X value range to the range with the correct values, and then the secondary X axis will reflect these values.
Comment from Evan
Time: Wednesday, October 22, 2008, 1:53 pm
Thank you so much.
Evan
Comment from Michael
Time: Wednesday, October 22, 2008, 7:48 pm
Great tutorial, Jon. In my particular instance, I am working with a constantly updated, large data set. Is there any way to apply your clustered-stacked column solution from a pivot table? If not, what recourse is available?
Many thanks in advance.
Comment from Jon Peltier
Time: Wednesday, October 22, 2008, 8:51 pm
Michael -
If you can figure out how to get the pivot table to show blanks where you need them, then you can make it work. However, I can’t imagine it’s possible to do that. I suppose a VBA procedure could be built to arrange the data.
Comment from Rebecca
Time: Thursday, October 30, 2008, 10:04 am
Great help! I am 95% there… I just can’t figure out the step about adding the secondary axis. My axis bars do not expand to cover the whole area. Instead they stay where they were (in the first third of the graph.) Is my data placement wrong?
Thanks!
Comment from Jon Peltier
Time: Thursday, October 30, 2008, 10:52 am
I don’t know about your data placement. If you used data ranges shaped like the ones in my example, the data should be okay.
You have to chart the axis label series on the primary axis, and all of the data series on the secondary axis. Then you need to make sure you have both primary and secondary category (X) axes in the chart, primary at the bottom and secondary at the top.
Comment from Alex
Time: Wednesday, November 5, 2008, 7:37 pm
I’ve been trying to use these examples, but can’t seem to get two sets of stacked columns, (similar to Example 3: More Series per Stack), but with the Actual and Forcast having separate (left and right) ranges (y-axis), and still get the months centered below the columns.
Does the usage of the Month series to get the x-axis labels centered below the columns preclude getting each stack ranged on a separate y-axis?
Comment from Jon Peltier
Time: Wednesday, November 5, 2008, 8:05 pm
Alex -
That’s correct. What I would do is put an axis scale on the secondary Y axis that’s appropriate for the right hand stack, then keep the right hand stack on the primary axis, but normalize the right hand stack data by dividing by the secondary axis max and multiplying by the primary axis max.
Comment from Label Problem
Time: Friday, November 7, 2008, 3:09 pm
Hi Jon,
Awesome stuff. I’m having trouble with my lables. In my stacked data, my bottom piece I need to have the category and value show. So, I format the first series with both value and category separated with a comma and placed inside end. This is fine for the first series, yet the other three series now reflect the category (only) as well as though it was linked to the first series. When I click on the second series, it doesn’t show the category is selected, however to get the label spacing consistent, I have to click category again and value so they both show up. Problem is, the categories overlap and looks messy as I’ve added it twice now; 1 from the first series, and now from the second series formatting. There seemed to be a similiar problem with the first post here and you asked for the spreadsheet. How did you fix this?
Comment from Jon Peltier
Time: Friday, November 7, 2008, 5:26 pm
I can’t envision exactly what you mean. The “basic” chart has a separate dummy series for the category labels. If you have these category labels below the clusters, can’t you get by with just the values in the column series labels?
Comment from Susi
Time: Monday, November 10, 2008, 12:18 am
Hello,
I have a problem with the months. I would need columns for the whole year not only for three months. Does it work then still the same way or is there something different I have to attend?
Comment from Jon Peltier
Time: Monday, November 10, 2008, 9:04 am
Hi Susi -
You merely need to repeat the same pattern. See Example 4 at the end of the main article, which extends the chart from 3 months to 4.
Comment from Michael
Time: Monday, November 10, 2008, 9:36 am
Hi Jon – great stuff! I appreciate your work very much. I work along the clustered-stacked-column-charts guide.
I tried to figure it out with dynamic ranges. Everythings works fine when defining the data source for the Right items by dynamic ranges.
But when I define the Left one it is stacked ontop all the right ones.
It is possible to work with dynamic ranges for clustered-stacked-column-charts data sources?
Cheers Michael
Comment from Jon Peltier
Time: Monday, November 10, 2008, 10:08 am
Michael -
It should be possible to use dynamic ranges as well as static ranges. Whenever I make a dynamic chart, I first make a static chart, often with a subset of the dynamic data. When it looks good, I define the dynamic ranges, check them to make sure they define the ranges I intend them to, then replace the static ranges with th dynamic ranges.
Make sure the dynamic Left items are defined right. If the Left items stack on the Right ones, I would check that the definition of the Left items are not off by one row or column. The data has to be staggered to get the side-by-side appearance, so be sure the dynamic ranges are properly staggered.
Comment from Marg
Time: Tuesday, November 11, 2008, 7:21 pm
I wanted to add some stacking to just one of the columns in each cluster, and this explanation was very clear and helpful. Thank you!
Comment from Michael
Time: Wednesday, November 12, 2008, 3:22 am
Jon,
I did it!! – Dynmic clustered-stacked-column-charts! You were right, my dynamic ranges were not staggered correctly.
Michael
Comment from Marg
Time: Wednesday, November 12, 2008, 1:41 pm
I liked your method of staggering data, but unfortunately my data exceeded some Excel limitation, and I couldn’t present all 12 months. The chart died at 8 months of staggered data. However, I happened upon a solution by accident which I thought I would share.
In my case, I only needed to stack one column of three. I resorted to a column-line chart provided by Excel. I clustered the three columns with the middle one being the one I needed to stack, and representing the total data that needed stacking. The stacked lines had their data points in the middle of the middle column. When I right-clicked on the data point of one of the lines, I noticed it would allow me to change the chart type! I modified the chart type for each of the lines to be a stacked column and they stacked on top of each other right over the middle column that I needed to stack! Thanks to your other tips about adjusting the gap width, I was able to make my middle column the right width.
This method (column-line, then change chart type on lines) doesn’t require staggering data, but it will only work if you have some symmetry to work with. i.e. I was stacking 3 values in the middle column of 3 columns.
Comment from Jon Peltier
Time: Wednesday, November 12, 2008, 3:56 pm
Marg -
I’m not sure how you’d be running into Excel’s limitations, but I’m glad you figured out how to make it work.
Comment from Nafri
Time: Friday, November 14, 2008, 1:43 pm
Really appreciate your valuable website and particularly this topic (“Clustered-Stacked Column Charts”). While you have indeed demonstrated how to do this thing which otherwise seemed pretty much impossible, I am tempted to ask if you really consider Excel at making clustered-stacked column charts helpful. Ideally, this should have been one of the chart types — not requiring what Dennis called “super gymnastics with the x-axis labels”.
Would appreciate it if you could talk about some good charting software — stand-alone or Excel add-ons.
Comment from Jon Peltier
Time: Friday, November 14, 2008, 3:27 pm
Nafri -
If Excel included all of these things, I wouldn’t have much to write about, would I?
Seriously, I have not used much besides Excel and Excel add-ins for so long, that I don’t think I could respond intelligently.
Nathan of FlowingData has posted on different visualization tools in 40 Essential Tools and Resources to Visualize Data.
Comment from Mark
Time: Thursday, December 18, 2008, 2:25 pm
You are a TRUE Life Saver… I knew Excel 2007 did not support Clustered-Stacked Column Charts, that is, until I happened to pull your incredible workaround. Fantastic !! Absolutely Fantastic….{:o)
Comment from Tim
Time: Thursday, December 18, 2008, 4:55 pm
Jon – Your instructions were great. My cluster-stacked chart is perfect. I do have one quick question – I work with Excel 2007. Is there any manipulative way to create a secondary vertical and/or horizontal axis for a 3-D stacked column chart vs. a standard stacked column chart? I’m only curious because I think a 3-D would look a lot more presentable over a standard. At your convenience, please advise, and Thanks Again. Happy Holidays.
Comment from Jon Peltier
Time: Thursday, December 18, 2008, 6:06 pm
Tim -
Thanks for the note.
Re the 3D chart. (1) First of all, “presentable” is in the eye of the beholder. When I see a 3D chart, or a pie chart, or a number of other ill-conceived visualizations, my expectations immediately take a plunge. (2) Second, 3D charts have fewer capabilities than their normal cousins. Combination charts and secondary axes are but two of the features you must sacrifice to gain the marketability of a 3D chart.
Comment from Steve Britton
Time: Monday, December 29, 2008, 12:50 pm
I’ve been trying and trying to apply these concepts to a chart I’d like to make in Excel, but I haven’t had any luck at all! I made the chart in Visio (located here for viewing: http://i53.photobucket.com/albums/g58/brittonsm/PPVVisualMgmt.jpg) –
It’s a stacked bar chart with MTD and YTD on each of the Y’s axis – If anyone can provide some insight I’d much appreciate it.
Thanks,
-Steve
Comment from Tara
Time: Wednesday, December 31, 2008, 12:22 pm
Jon,
Thank you for this site. It has been tremendously helpful! I’m working on a mixed column / line chart in Excel… And my problem is with the column chart – There are these teeny tiny horizontal lines in the top of each individual bar. I don’t understand where they’re coming from or how to make them disappear. Maybe you could point me in the right direction? Thanks again for putting this site together!
Comment from Jon Peltier
Time: Monday, January 5, 2009, 6:25 am
I had Tara send me the file, and it turns out the lines have a simple explanation. Her data had several points per month, but her time-scale X axis had a base unit of months. This means all points for a given month are plotted in the single slot for month. If a taller bar is plotted behind a shorter bar, the border along the top of the shorter bar looks like a line below the top of the longer bar.
The remedies include:
1. Plotting only one bar per month.
2. Using a fill but no border for the bars.
Comment from Christina
Time: Thursday, January 8, 2009, 2:54 pm
Hi Jon,
I was following the instructions on how to create the chart and have ran into a little problem. When I’m changing the stacked data series onto the secondary axis and the axis series on the primary, all my axes labels disapear and all the stacked data series goes back to primary axis. The axis labels are weekly dates. i can do this chart fine when my labels are from day to day (ex: Jan 1, Jan 2, Jan 3 …), the second I want the labels to be weekly, the chart doesn’t work for me. Could you help me out please.
Thanks!
Comment from Jon Peltier
Time: Thursday, January 8, 2009, 5:04 pm
Hi Christina -
I’ve sent back your workbook with its chart. There were a few issues. I think the protocol is about the same in Excel 2007, but even so it seems harder to me.
1. I don’t know why the horizontal axes disappeared. You can get them back from the Chart Tools > Layout tab, Axes drop down button.
2. When you have weekly dates, Excel plots all seven days of the week, so you get six empty days for each data point. I changed the primary X axis to a “text” type.
3. The data range for your main columns needs a blank column of data on either side of the staggered data (see columns B and K in the first staggered table in this article). For your chart, I moved the series names to the left one column, to column B, and extended the category labels and Y values for these series leftward to include column C and rightward to include column BH.
4. I would use colors which are less saturated.
Comment from Christina
Time: Thursday, January 8, 2009, 5:36 pm
You are a life saver. I’ve been trying to fix this for a week. Thank You so much for your help. :)
Comment from Max
Time: Friday, January 9, 2009, 2:52 am
Hi Jon,
Thank you so much for your contribution! I’ve been looking for this for years. There is one thing I can not get right: the alignment of the data labels (it works fine for the first two data points but they start ‘drifting’ for the next few points; I can send the graph by email if you would be willing to share your email address). Appreciate your help!
Best regards,
Max
Rotterdam, The Netherlands
Comment from Jon Peltier
Time: Friday, January 9, 2009, 6:25 am
Max – This misalignment is usually due to an off-by-one error: too many or two few categories.
Comment from Kumar
Time: Thursday, January 15, 2009, 2:15 pm
Hi Jon,
I was going through the instructions for making Clustered-Stacked Column Charts but in the middle of it I got stuck. “The month names are not centered correctly under the clustered columns. Format the scale of the secondary category axis (B through K at the top of the chart) so that the value axis does not cross between categories.” – I am not able to follow you after this. what should I keep the Scale for Secondary Category “X” Axis?
Comment from Jon Peltier
Time: Thursday, January 15, 2009, 3:14 pm
Kumar -
In Excel 2003 and earlier, double click on the secondary category axis, and uncheck the box that is labeled “Value (Y) Axis Crosses Between Categories”. In Excel 2007,right-click on the axis, choose “Format Axis”, and at the bottom of the Axis Options tab, select the “On tick marks” option under “Position Axis”.
I’ve added this to the appropriate place on the page.
Pingback from Contextures Blog » Compare Annual Data With an Excel Clustered Stacked Chart
Time: Monday, January 26, 2009, 12:03 am
[...] data for different columns is on separate rows. Jon Peltier describes all the gory details on his Clustered-Stacked Column Charts [...]
Comment from Beth
Time: Tuesday, January 27, 2009, 5:30 pm
Thanks so much for this helpful tutorial. Unfortunately, I’m stuck. I’m using Excel 2003. I can’t figure out how to move the axis labels from where they are on the left to be centered under the clustered column. I tried unchecking the box as you described, but that didn’t seem to help.
Does it matter where I put the original labels? I just started in column B with them.
Do you have any suggestions?
Comment from Jon Peltier
Time: Wednesday, January 28, 2009, 2:29 pm
Beth -
If the labels are all scrunched to the left, I suspect you made the stacked columns properly, but then neglected to add the labels using another series. You need to set up another range to hold the data for the labels, then add this range as another series, and move the stacked columns to the secondary axis. It’s covered in the post.
Pingback from Review of PTS Clustered Stack Chart Utility for Microsoft Excel | Pointy Haired Dilbert – Chandoo.org
Time: Monday, February 9, 2009, 2:39 am
[...] multiple series with few blank values and then plotting a regular stacked column chart. Check out the entire process on Jon’s site [...]
Comment from Joe
Time: Friday, February 13, 2009, 9:57 pm
Hello, I would like to make a vertical column chart (4 columns) with no gaps (touching one another), the x-axis on a log scale, y-axis regular scale and each column to be a certain width with the first column starting a certain distance from the y-axis. Can you help?
Comment from Jon Peltier
Time: Saturday, February 14, 2009, 8:30 am
Hi Joe -
That’s like a marimekko chart. Essentially it is a stacked area chart. The x axis is a date axis, using dates formatted as regular numbers. Line and area charts treat the dates as proportional values, which allows areas to have vertical edges and points with nonuniform spacing. These effects can be used to produce column and stacked column charts with variable widths.
Comment from Mike ZANG
Time: Saturday, February 14, 2009, 1:13 pm
Hi, Jon
Your site is wonderful, Thank you very much for you articles.
I have a question, I have some as below, I hope Funds and Profit in one stacked column, and Loss, Cash, Share in another stacked column, then they are in clustered column, can you tell me how to do?
Date Funds Cash Share Loss Profit
09/02 100 100 0 0 0
09/01 200 50 100 50 0
08/12 200 100 20 80 0
08/11 250 150 200 0 100
08/10 300 100 100 100 0
Comment from Joe
Time: Saturday, February 14, 2009, 2:03 pm
Thanks for the reply. Can you do a marimekko chart in MS Excel? My vertical columns are only with one number and are not stacked to 100%. They will be as tall as the value of that column. Can the x-axis on the marimekko chart be scaled for a log scale and show the log scale on the x-axis? Thanks!
Comment from Joe
Time: Saturday, February 14, 2009, 2:05 pm
One more question, do you have a link to an example of a simple marimekko chart that I reference? Thanks! – Joe
Comment from Jon Peltier
Time: Saturday, February 14, 2009, 5:15 pm
Joe -
If you can wait a couple of days, I’ll have new articles here. Tentatively I’m planning to write Monday about variable width column charts, and on Tuesday I will extend the procedure to Marimekko charts.
In general, the height of the bars is whatever you tell Excel to make them. You can stack the heights themselves, or stack the series so the height at each position is 100%.
The log scale is a bit trickier, but it can be done. You need to make a fake log scale axis using a dummy XY series, and fudge your data so it comes out right. In general, though, using a log scale for an axis that depicts stacked quantities would be so easy to misinterpret. Changing the order of stacking would make a huge difference in the perceived values of the colored regions.
Comment from Jon Peltier
Time: Saturday, February 14, 2009, 5:29 pm
Mike Z -
It’s like the other examples. Think how you want the data to look, and arrange it accordingly. Here’s the category label data (primary axis):
and here’s the stacked column data (secondary axis):
Comment from Joe
Time: Saturday, February 14, 2009, 8:41 pm
Ok, I can wait. Thanks. Your site is great! – Joe
Comment from Mike ZANG
Time: Saturday, February 14, 2009, 10:20 pm
Thanks for your answer , Jon.
I made it what is little different from you. my data is http://www.geocities.jp/mikezang/stock/data1.png , and chart is http://www.geocities.jp/mikezang/stock/chart1.png .
I have two more questions:
first is it possible to arrange data as http://www.geocities.jp/mikezang/stock/data2.png ?
second is how to move axis on the top to bottom? I tried config it, but the result is http://www.geocities.jp/mikezang/stock/chart2.png . What can I do?
Comment from Jon Peltier
Time: Saturday, February 14, 2009, 10:28 pm
Mike – I suppose you could arrange the data that way, but you may have to merge the three cells for a date. Merging cells leads to other problems, for example, if you wanted to sort the range.
In your chart, you formatted the xis to show its labels in the Low position, but you didn’t move the axis. To move the axis, you have to format the other axis, and tell it where you want the first axis to cross. In this case it may be as simple as unchecking the Category Axis Crosses At Maximum box.
Comment from Mike ZANG
Time: Saturday, February 14, 2009, 11:00 pm
I tried, but I can’t get the result what you said. can you show me how to do?
http://www.geocities.jp/mikezang/stock/test.xls
Comment from Jon Peltier
Time: Sunday, February 15, 2009, 9:31 am
Mike -
In this protocol, I have put the series with the labels (“Date” in your workbook) on the primary axis, and the other series on the secondary axis, in order to get suitable labels. You have reversed this arrangement, so it will be much more difficult to get a suitable display.
Suggestion: Your vertical axisuses labels like 500 and a multiplier in the title of X10000. First, it is hard for people to count zeros in a label like this, especially if it is rotated text, so use a thousands separator (a comma here in the US). It is awkward to use a non-thousands multiplier (in other words a multiplier that’s not 10 to a power divisible by 3). Since 500 x 10000 is 5 million, use numbers on the order of 5, and use either “x1,000,000″ or “millions” in the title.
Comment from Mike
Time: Sunday, February 15, 2009, 7:58 pm
Thanks for your advise. I want to know if there is a simple way to exchange primary and secondary axis?
Comment from Jon Peltier
Time: Sunday, February 15, 2009, 8:15 pm
Mike -
The only way I know of is to move each series on its own. Depending on the version of Excel, after you switch one series you amy be able to click on the next version and hit the F4 function key to repeat the switch for the next series.
Comment from Mike
Time: Monday, February 16, 2009, 1:46 am
I made right lables, thanks a lot. Your site is great!
Comment from Mike
Time: Thursday, February 26, 2009, 10:41 pm
Hi, Jon
I found a limit for data range, for example, I can add data using =(B!$A$2,B!$A$5,B!$A$8,B!$A$11,B!$A$14,B!$A$17,B!$A$20,B!$A$23,B!$A$26,B!$A$29,B!$A$32, B!$A$35,B!$A$38,B!$A$41,B!$A$44,B!$A$47,B!$A$50,B!$A$53,B!$A$56,B!$A$59,B!$A$62,B!$A$65, B!$A$68,B!$A$71,B!$A$74,B!$A$77,B!$A$80)
but if I add data as=(Balance!$A$2,Balance!$A$5,Balance!$A$8,Balance!$A$11,Balance!$A$14, Balance!$A$17,Balance!$A$20,Balance!$A$23,Balance!$A$26,Balance!$A$29,Balance!$A$32, Balance!$A$35,Balance!$A$38,Balance!$A$41,Balance!$A$44,Balance!$A$47,Balance!$A$50, Balance!$A$53,Balance!$A$56,Balance!$A$59,Balance!$A$62,Balance!$A$65,Balance!$A$68, Balance!$A$71, Balance!$A$74,Balance!$A$77,Balance!$A$80)
I can only view =(Balance!$A$2,Balance!$A$5,Balance!$A$8,Balance!$A$11,Balance!$A$14, Balance!$A$17,Balance!$A$20,Balance!$A$23,Balance!$A$26,Balance!$A$29,Balance!$A$32, Balance!$A$35,Balance!$A$38,Balance!$A$41,Balance!$A$44,Balance!$A$47,Balance!
so I think only 252 characters can be used for range, do you know this limitation? can I overcome this limit?
Comment from Jon Peltier
Time: Thursday, February 26, 2009, 11:37 pm
Mike -
This limitation is well known, if poorly documented by Microsoft. A chart series can accept up to 32,000 points, if the data is in a contiguous range. The series formula has the additional limitation that each of the arguments (name, X values, Y values, and plot order) must be less than 1/4 of the length limitation of an Excel formula. Kind of a waste that plot order consumes 250-odd characters, since it can never be more than three characters in length (there cannot be more than 255 series per chart).
Long story short, the way to get around this limit is to put your data into a contiguous range. Keep the data where it is, but use a second range that links to the first, with only the data which is to be plotted (no gaps).
This is another example of my old adage:
Spend five minutes with your data, and save five hours of frustration.
In your case, the payoff might be even better than that.
Comment from Gaby
Time: Wednesday, March 11, 2009, 10:11 am
Hi:
Thanks a lot for this information, very helpfull and interesting. I am working with Excel 2002 and having some trouble trying to get the 2 columns right next to each other (step 5). Do you think you can help e out with that?
Thanks a lot!
Gaby
Comment from Jon Peltier
Time: Wednesday, March 11, 2009, 10:23 am
Did you remember this step:
“Change the gap width of any series to zero”
Comment from Gaby
Time: Wednesday, March 11, 2009, 11:45 am
Hi:
Yes, I know you mentioned it, but I am not really sure where I do that.
The place where I can go to the x-axis does not allow me to go any under than 1.
Thanks
Comment from Jon Peltier
Time: Wednesday, March 11, 2009, 12:41 pm
Double click on a chart series (any of the columns), click on the Options tab of the Format Series dialog, and Gap Width is one of the options. Change it to zero.
Comment from Gaby
Time: Wednesday, March 11, 2009, 3:14 pm
Perfect! Thanks a lot for your help. That resolved my issue!
Comment from Anne
Time: Friday, March 13, 2009, 9:30 pm
Finding this tutorial on creating stacked-clustered column charts was a godsend, but… I’m having the same problem mentioned by Rebecca above.
“I just can’t figure out the step about adding the secondary axis. My axis bars do not expand to cover the whole area. Instead they stay where they were (in the first third of the graph.)”
I assume I’m just missing some subtlety in the settings somewhere. Can you help please?
Comment from Jon Peltier
Time: Friday, March 13, 2009, 10:57 pm
Anne -
Usually it’s a simple matter of leaving out one little detail. Have you added the secondary category axis?
If you’re using Excel 2007, some of the steps nave to be done in a particular order, whereas in 2003 order is less critical. Also in 2007 some of the operations are located in different places, so the Excel 2003 descriptions may not lead you to all of the right steps.
Comment from Mike ZANG
Time: Friday, March 20, 2009, 1:55 am
Jon
I have a new question about X-axis, as you know, my data are stock data, so the x-axis is date but only for workday, I want to show every monday at x-axis, is it possible?
Thanks for your advise.
Comment from Jon Peltier
Time: Friday, March 20, 2009, 6:23 am
There are two possibilities
1. The axis is a date (time) axis, in other words, all dates including weekends have a slot on the axis.
Format the scale of the X axis so that the base unit is days, the minimum date is also a Monday, and the major unit (tick spacing) is 7 days.
2. The axis is a category (text) axis, which only has a slot for days with reported prices.
Insert the date and a blank value for any holiday in the range. Make sure a Monday is the first data point. Format the axis scale so that the space between category (tick) labels is 5.
Comment from Ben
Time: Wednesday, April 1, 2009, 6:43 pm
Thanks very much- quite helpful.
BW
Comment from Bobby
Time: Thursday, April 9, 2009, 8:09 am
I am writing from Singapore and wanted to say thank you to Jon for providing this tutorial, it really helped me understand the technique behind the creation of cluster stacked column combination charts. The tutorial was so enlightening that I was able to add a line graph to the cluster stacked column combination chart by amending the chart type for the primary x-axis data series without looking at the solution put forth by Jon.
At first there was a slight drift in the x-axis labeling – a little to the left and then a little to the right – but it was no biggie and I was thrilled at having created this chart on my own. In the evening as I contemplated my latest adventure with Excel charts, it dawned up on me that I needed to make some corrections. Basically I am looking at the three segments of a company over a period of nine years. In order to ensure that there is no drift in the primary x-axis labels, it was necessary to make sure that the total number of elements in the secondary x-axis would total [(number in group=3) + 2] X (number of elements in primary x-axis=9) = 45. There were 46 elements and that accounted for the drift.
The +2 in the formula refers to the space before and after each grouping. Initially I added only one space but found that the primary x-axis label did not center correctly. It would either veer too much to the right or left but say in my example there were 36 elements then it would veer consistently and not drift from left to right.
However, I did uncover a small curio in the legend. Most of the elements in the legend were not needed and I wanted to delete them. While I could delete these legend items and it would display correctly in Excel, when I tried to paste the chart into Word certain parts of the deleted legend would pop up while the ones that I wanted to keep would be deleted. When the chart was saved and then opened again, it would display the same quirk as the cut and paste charts.
Today was a day dedicated to simply learning about and playing with cluster stacked column combination charts and it was a very satisfying learning experience. Thank you again Jon and I hope that my contribution to your blog is some recompense for your generosity.
Comment from Jon Peltier
Time: Thursday, April 9, 2009, 8:29 am
Bobby -
Thanks for the note. The slight offset in the axis labeling might be caused by not having one axis with the axis crossing between categories and the other crossing at a category.
Which version of Office are you using? I wonder if the legend issue is one more silly thing about Excel 2007.
Comment from Bobby
Time: Thursday, April 9, 2009, 10:19 am
I am having trouble accessing this particular blog on Clustered-Stacked Column Charts and it might have something to do with my system but the tutorial is so good I wonder if you actually end up selling fewer of the utility because of it. I am using Excel 2007 and the issue with the legend is most likely a bug. I have tried assigning zeros to the empty spaces in the data array in the hope that having an element instead of being null would help but it did nothing. The chart was moved to a separate sheet of its own and it looked fine when I worked on the legend but then acted up when copied and pasted. The funny thing is that when I remove all but the last two items in the legend it stayed stable. Also, the label for the data series used to create the primary x-axis was appears at the end of the legend instead of at the beginning.
It is near midnight and I will have to stop playing with the Excel charts. Thanks again Jon!
Comment from Jon Peltier
Time: Thursday, April 9, 2009, 12:00 pm
. . . the tutorial is so good I wonder if you actually end up selling fewer of the utility . . .
Good point. I’ll delete the tutorial.
What’s wrong with the legend?
Comment from Bobby
Time: Friday, April 10, 2009, 10:07 am
I actually accidently stumbled onto this tutorial because it is not linked to the Charts and Tutorial section and then could not find my way back later. Prior to that I had spent some fruitless hours surfing the net and learning that what I wanted to do could be done but no one seemed willing or able to explain how to do it. For most part those in the know teased the neophytes with taunts like “you need to think laterally”. Imagine how happy I was when I found the tutorial.
At first I did think that the tutorial had been taken down but after scrounging around I found the link to it in the PTS Blog. As someone who analyzes businesses, I do believe that you are entitled to earn a return on all the intellectual property which you have made available. Doing so via the charting utilities is a great way to monetize your knowhow and I understand why you might want to promote that. But my sense of ‘loss’ did kick in when something that had been made freely available was seemingly no longer there. My apologies Jon if you were offended.
Going through the rest of your material has really opened up a whole new world of charting possibilities for me. Although I do not draw charts for a living, it is perhaps one of the handiest things that can be accomplished in Excel. I would rate creating high impact charts as perhaps more valuable than the VBA which I spent so much time learning. Nothing makes the point better than a well drawn chart.
I mean what I say next to help, Jon. It might make a difference if you put up examples that do not quite have a 1990s look and feel to them. The charts which I have seen on your website do not look bad, but they do not look great either. I say this because I know that PTS is as much a business as it is a passion for you.
P.S. The problems with the legend are still there but it is a very minor complication. To show you I would have to email the data and chart to you but that has to stay confidential.
Comment from Jon Peltier
Time: Friday, April 10, 2009, 10:16 am
Bobby -
I wasn’t offended at all, just amused. And even more amused that anyone might think I was offended by such an innocuous comment.
Re the 1990s look and feel to the charts, unfortunately it’s hard to look 2000s without lots of glitter and gratuitous visual effects. I’ve paid more attention lately to making cleaner charts and using nicer colors. Anything else you think would help?
Comment from Bobby
Time: Friday, April 10, 2009, 6:46 pm
All is well then Jon. :)
Comment from Andy
Time: Monday, April 20, 2009, 10:24 am
Thanks for the original post and to everyone else for their contributions. You have no idea how many questions this has answered for me – should certainly give my rather limp spreadsheets at least some worthwhile content!
Comment from Pam
Time: Wednesday, April 29, 2009, 3:07 am
Hi,
I have to thank you for making these tutorials. They are very useful in getting ideas on how to make more interesting graphs in Excel.
I do have a question on making stacked graphs. I have been trying to apply this technique to making this kind of graph but using 3 axis (ie. x = month, y = amount – stacked, z = year) and so far, it hasn’t been working out. If you have any pointers on this, it would be super.
Comment from Jon Peltier
Time: Wednesday, April 29, 2009, 9:27 am
Hi Pam -
I’m not sure how you would use three axes for this. I can think of a couple of ways to show it.
1. Simple Stacked Column Chart
Start with this data arrangement:
Create a stacked column chart:
2. Clustered-Stacked Column Chart
Start with this arrangement:
Create a clustered-stacked column chart as above:
You can build the data range yourself, or use the PTS Clustered-Stacked Column Chart Utility with this much simpler data arrangement:
Comment from Jill
Time: Monday, May 4, 2009, 10:36 am
Is it possible to graph side by side stacked bars using two different sheets in the Excel worksheet? For example, if I have one sheet designated for 2008 data and another for 2009 data and want to show 2008 vs. 2009 data for the following can I do it?
Each year I want a stacked chart showing the following. I want one stacked bar with all three categories in it for 2008 then next to it another stacked bar showing 2009 data; however, the info are on two separate sheets.
Jan Feb Mar Apr
TOTAL MEMBERSHIP AGES <65
TOTAL MEMBERSHIP AGES 65-67
TOTAL MEMBERSHIP AGES 65-68
Thanks
Comment from Jon Peltier
Time: Monday, May 4, 2009, 12:48 pm
This is a case where it makes sense to collect the data into a summary range. You can link to the original data, so changes are reflected in the summary.
It is possible to do this using data on separate sheets, but having the data on one sheet makes it so much easier to set up. When you can’t see all the data in one place, you are prone to errors, and even if you make no mistakes, it takes longer, because you have to move back and forth, comparing ranges, and remembering layouts. You have to rearrange the data anyway, so it might as well be on a summary sheet.
Comment from ifat royter
Time: Wednesday, May 27, 2009, 5:47 am
I am trying to do a stacked graph with two sets of data, one with quarterly data and on is YTD which is on a much higher scale, so in order to present them together, I put the quarterly data on a primary axis, and the YTD data on a secondary axis and this way both sets of data have the same proportion.
As long as the numbers are positive, the graph looks good, but if one of numbers s negative, then the X-axis of the secondary axis doesn’t start at zero, even though, in the scale definitions it does say that the X-axis starts at zero and the graphs looks funny, you can see it in example in the graph with the negative numbers
How can we fix it so the X axis always start at zero even though the numbers are negative sometimes.
Comment from Jon Peltier
Time: Wednesday, May 27, 2009, 9:39 am
Ifat – I have covered exactly this issue in Align X Axis to Y=0 on Two Y Axes. you can do it manually (and tediously), but I’ve included a VBA procedure to handle it for you.
Comment from kdm
Time: Saturday, May 30, 2009, 3:23 pm
Jon,
Please advise if possible. I need to create a chart that is a cross between a 3-D Column Chart and a Stacked Chart. I have not been able to figure out how to do this.
This is what I need my chart to look like…
Values along the vertical axis
Months along the horizontal axis
Individual columns (3-D effect) for each year, within each month (depth)
Multiple components for each column (Stacked effect)
To help visualize, this type of chart could be used to compare sales figures over time, comparing months and years, while also creating the ability to note the sales contributions of Product A versus Product B (value breakdown — not percentage), within those time frames.
Thank you!
Comment from Jon Peltier
Time: Saturday, May 30, 2009, 6:18 pm
First of all, 3D columns will not help show the data the way you think they will. The third dimension will either distort the values, or worse, obscure small values plotted behind large values. Even if you’ve seen charts like this that you think were effective, I guarantee they were not. The pretty effect made them look good, and you completely didn’t realize you were not able to read the values.
You can show the data you want more clearly with a stacked-clustered column chart, with the major categories as months, clustering together the yearly values, and stacking what needs to be stacked. You can read how to build these charts in the Clustered-Stacked Column Charts tutorial.
Comment from Stu
Time: Tuesday, June 9, 2009, 12:37 pm
Jon, I’m having challenges with a most basic issue: the secondary axis. (Excel 2007) I paste special the axis chart (F1-I2) into my target chart. First, the month labels appear underneath (not expected based on your tutorial.) Second, assigning a series from my target chart to a the secondary axis is, well, how?
MSFT says: On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click Depth (Series) Axis.
I don’t see that option (only Vertical and Horizontal,) and the Layout tab – Axis option shows me no option for the secondary axis.
Puzzling.
Comment from Jon Peltier
Time: Tuesday, June 9, 2009, 4:42 pm
In 2007, the steps work about the same as in 2003. When I paste F1:I2 as a new series, it stacks on the other series on the left of the chart.
To move series to the primary axis, start with the smallest (because the secondary series will obscure the primary ones). Select the series, press Ctrl+1 (numeral one) to pop up the format series dialog, and right on the first pane of the dialog in 2007, select Secondary. Repeat for the other series. Excel 2007 remembers that the series were stacked and had a gap width of zero (2003 changes them to clustered and applies the default 150 gap width.
Now you need to add a secondary category axis. On the Chart Tools > Layout tab, click the Axes deopdown arrow, select Secondary Horizontal Axis > Show Left to Right Axis. You don’t need to edit the source data in 2007, Excel uses the month labels as the axis labels.
You need to format the secondary category axis (top of chart) so the axis crosses on tick marks.
Other than these minor adjustments, in 2007 it works about like it does in 2003.
Comment from Scott Murray
Time: Monday, June 15, 2009, 10:18 am
Hi Jon, like a lot of the posts on here. my problem is with the labels. I copied your example for the Axis data, with the only difference being that I have 12 months. I did NOT include any gap columns in this data series.
The result is that my Axis series stays bunched up on the left side of my chart. It does not expand out to fill the whole chart, as yours does in the example. Therefore my month labels do not allign with the data series’ I moved to the secondary axis.
Any ideas?
Thanks
Comment from Jon Peltier
Time: Monday, June 15, 2009, 10:35 am
Scott -
1. Make sure there are both primary and secondary category axes in the chart.
2. Make sure that the primary and secondary series use the correct category label ranges.
Comment from Teng
Time: Wednesday, June 17, 2009, 5:47 am
Hi Jon,
I was wondering if it is possible to create a stacked clustered column chart using 2 y-axes?
The x-axis will be “month”. The left y-axis would be “fees”, and the right y-axis would be “headcount”.
Each month, there are 2 stacked columns; the first column relates to fees and so should be read off the left y-axis; the second column relates to headcount so should use the right y-axis.
How would you create such a graph?
Many thanks for your help!
Regards
Teng
Comment from Jon Peltier
Time: Wednesday, June 17, 2009, 6:06 am
Teng -
You need to combine this approach with Columns on Two Axes. Since you already have stacked column series, it’s really a matter of assigning the appropriate series (headcounts) to the secondary axis.
Keep in mind the inherent problems with Secondary Axes in Charts.
Comment from Teng
Time: Wednesday, June 17, 2009, 6:44 am
Hi Jon
Many thanks for your reply. Unfortunately I still can’t get my graph to work.
After assigning my headcount series to the 2nd y-axis, I want to turn the headcount series into a stacked column as well.
When I change the chart type for the headcount series to stacked column, it stacks the headcount figures on top of the existing fees stacked column, instead
of creating its own stack.
How do I resolve this problem please?
Thanks for your help!
Teng
Comment from Jon Peltier
Time: Wednesday, June 17, 2009, 7:56 am
Did you select the headcount series, and then change the chart type, or did you change the chart type of the entire chart?
Comment from Teng
Time: Wednesday, June 17, 2009, 8:26 am
Hi Jon,
I selected a data point from the headcount series and changed the chart type to stacked columns.
Regards
Teng
Comment from Jon Peltier
Time: Wednesday, June 17, 2009, 8:53 am
Excellent! It’s always something dumb like that.
Comment from Thom Mitchell
Time: Monday, July 6, 2009, 9:38 am
[Periodically, I have to take off my applied mathematician-hat and put on my "department bureaucrat-hat." In this world I deal with discrete data points and tables rather than functions.] Last week the I received some application information broken down three ways: between economics & business, between in-state and out-of-state applicants, and for fall admissions in 2008 and 2009. After some playing around and not really finding what I sought in J-Walk’s _Excel Charts_, I found this clustered-stacked column chart page.
This may be the same question that Pam asked (04.29.2009), so I apologize for being too dense to be certain. Although I was initially overwhelmed by the small slider on the vertical scroll bar for this page, I worked through the tutorial and derived this result:
Result 1: http://tmitch.siuc.edu/Excel/BusinessAndEconApplications0809_1.gif .
The chart title in my Result 1 is the only clue that the left-hand column-bars probably represent 2008 and the right-hand column-bars probably represent 2009 in the three clusters.
Before the clustered-stacked solution, I derived one result on my own:
Result 2: http://tmitch.siuc.edu/Excel/BusinessAndEconApplications0809_2.gif .
Two questions:
1. Is there any way to encode or label the years in my Result 1?
2. If not, do I have to go to either my Result 2 (looking similar to your result http://peltiertech.com/images/2009-04/YearMonthAB_stack.png ) or your result here: http://peltiertech.com/images/2009-04/YearMonthAB_clustack.png ?
My needs notwithstanding, an outstanding tutorial and post!
~Thom
Comment from Jon Peltier
Time: Monday, July 6, 2009, 10:54 am
Thom – If you had different series for 2008 and 2009, your first chart would have 2008 in state, 2008 out of state, 2009 in state, and 2009 out of state. It appears that you have not split these into each year’s data.
Comment from Thom Mitchell
Time: Monday, July 6, 2009, 11:08 am
And if I split those series, will I get a chart analogous/similar to this one of yours: http://peltiertech.com/images/2009-04/YearMonthAB_clustack.png ?
Would this solution necessarily give me the four columns/bars for “Business”:
(i) 2008, in-state, (ii) 2009, in-state, (iii) 2008, out-of-state and (iv) 2009, out-of-state?
Perhaps I ask for the impossible, but I’m trying to exhibit my “assimilated” status and aim for “less is more.”
~TM
Comment from Jon Peltier
Time: Monday, July 6, 2009, 11:10 am
Thom – Yes, it would be like that chart, where A = in-state and B = out-of-state.
Comment from Thom Mitchell
Time: Monday, July 6, 2009, 11:41 am
Why do I so often want the impossible….
(So I can’t display a “label” and have it positioned above the top piece of the stacks or will this look exactly like what it is: a hacked solution that’s not really a solution?)
Comment from Jon Peltier
Time: Monday, July 6, 2009, 12:51 pm
You want the year labels above the stacks? Add a couple dummy series of data, one for each year, which capture the total heights of the stacks.
Add these series to the chart, on the same axis as the stacked columns.
Change these added series to line series.
Add data labels to each line series, using the series names (years), and position the labels above the points
Hide the series (no lines, no markers) and the legend entries (two single clicks on the legend entry label, then Delete).
Comment from Thom Mitchell
Time: Monday, July 6, 2009, 2:54 pm
Thanks, Jon!
http://tmitch.siuc.edu/Excel/BusinessAndEconApplications0809_3.gif
:)
Comment from Jon Peltier
Time: Monday, July 6, 2009, 4:33 pm
Thom – That’s what I thought you wanted. Glad to help.
Comment from Michael Bowman
Time: Wednesday, August 12, 2009, 2:09 pm
This is great stuff. I do performance testing and needed a way to display comparative response data and this was exactly what I was looking for. It’s a fair guess I would never have come up with this on my own.
Comment from Heather
Time: Thursday, August 27, 2009, 11:27 am
Jon-
This is great stuff and I was able to get my chart to turn out exactly how I needed it but ran into a snag when I went to add an additional data series to a stacked column.
I enter quarterly data by year so I have a stacked column for ‘08 with data series of Q1, Q2, Q3, and Q4. My second stacked column is for ‘09 in which I only have data for Q1. Is there an easy way to add data for Q2 without having to go through all the formatting steps again?
Thanks in advance for your help.
Comment from Jon Peltier
Time: Thursday, August 27, 2009, 2:26 pm
Heather -
Assuming you already have your five quarters of data, you could insert another row for the latest quarter, as shown, and populate it accordingly.
The easiest way to get the data into the chart might be to select the 2009 Q1 series, copy the SERIES formula, then select the chart area, paste the formula into the formula bar, edit it slightly**, and press Enter.
**Edit the SERIES formula to increment the plot order by 1, so if you copied the series formula for the 5th series, change the 5 to 6 inside the last parenthesis. Also, change the referenced range for series name and Y values (first and third arguments in the SERIES formula) so it refers to the row for the new series.
You may have to select this added series to change it to a stacked column type, and put it on the secondary axis like the other quarterly data series.
Comment from Hans L
Time: Friday, August 28, 2009, 8:58 pm
Your example seemd simple enough. I followed your detailed instructions until I got to the point where the chart has two X-axes and two Y-axes. My problem is that the Left thru Right 2 series (data for the B thru K values) hang as stalagtites from the second X-axis, upside down, so to say. The axis series (values F1:I2) show correctly. I got so frustrated that I used Excel-2007 and got the right results, but I often have to work in other environments using Excel 2003. I’ve tried this also on my own home computer which still has Office 97 and I get the same “up-side-down” results.
There are some nice graphs among all your responses that I will be able to use for other presentations. Many thanks for your help.
Comment from Jon Peltier
Time: Friday, August 28, 2009, 10:46 pm
I sometimes get the stalagtites in charts like you describe. Looking over this protocol, which I wrote up for 2003 and which follows exactly what I would have done in 97, 2000, and 2002 as well, it appears I didn’t encounter stalagtites here, but had only stalagmites. One way to avoid the problem is to format the value (Y) axis so that the category (X) axis crosses at zero instead of at the maximum, but sometimes in complex workaround charts like this, moving an axis has unexpected consequences. Since the B-C-D-etc labels are not required for this chart, perhaps you could try this formatting trick.
Comment from Hans L
Time: Saturday, August 29, 2009, 2:30 pm
Thanks for your response Jon. I tried your suggestion but that got me into trouble with the other axes. Eventually I found that at the point where you say: “Select one of the secondary axis series, and change its chart type to stacked” you must use the chart options in the pop-up menu. I would simply click on a dataset, click the chart icon at the top, and choose the stacked chart option; wrong move! You lose the right hand Y-values. So, I finally woke up and it worked. Thanks again for your help.
Comment from Petti
Time: Thursday, September 3, 2009, 5:51 pm
Thank you so much for the step by step instructions and yes, it just made my day!
I need exactly the this kind of graph!
Comment from Matt
Time: Monday, September 14, 2009, 5:53 pm
Thanks for the help…I’m stuck on the last step though….how do I switch the secondary Y-axis values from the right side of the chart to the left side?
Thanks again…
PS I’m using Excel 2007
Comment from Jon Peltier
Time: Monday, September 14, 2009, 7:56 pm
Matt -
You need all four axes visible, if only temporarily. Format the primary X axis, and make the Value (Y) Axis cross at the maximum. Then format the secondary X axis, and make the Value (Y) Axis NOT cross at the maximum.
Comment from Joe
Time: Sunday, September 20, 2009, 11:51 am
Hello Mr. Peltier, I have a simple column chart with our frame sizes on the x-axis and the costs of each frame size on the y-axis. I have 2 lines, one for 2008 and one for 2009. I was asked to plot the least-fit-squares line for each set of data (one for 2008 and one for 2009). Is the standard trend line in Excel a least-fit squares line? If not, how do I do it? If you have any other suggestions or ways to display changes in cost from year to year for product, that would be great. Your website is awesome! Thanks, Joe
Comment from Jon Peltier
Time: Sunday, September 20, 2009, 9:14 pm
Joe -
The trendlines are least-squares fits (except for moving averages). You could look up LINEST in Excel’s online help files to get more information.
Comment from Mindy
Time: Tuesday, October 13, 2009, 3:57 pm
Is there a way to add totals to the tops of the stacks in a stacked column graph?
Comment from Jon Peltier
Time: Tuesday, October 13, 2009, 8:30 pm
Mindy -
My colleague Andy Pope shows how to display totals on stacked columns on his web site.
Comment from angeline
Time: Wednesday, October 14, 2009, 2:28 am
I need to create a chart showing the monthly value (Jan – mar on the x axis) and the Total value (370 … 720)
what is the best way to show
Jan Feb Mar total
a 100 120 150 370
b 200 220 250 670
c 150 140 430 720
Comment from Jon Peltier
Time: Wednesday, October 14, 2009, 9:44 am
Angeline -
I don’t think you need something as fancy as the cluster-stack charts. Starting with your data:
you could make a clustered column chart
which isn’t too bad, or a stacked column chart
which isn’t as good for comparing each item (i.e., comparing a and b in March or b in Feb and March). With a simple rearrangement of the data:
you can make a combination chart with six series: three lines with data for Jan, Feb, and Mar, but blanks for Total, plus three columns with blanks for Jan, Feb, and Mar, and data for total.
Lines are good for showing trends over time (month to month) and using columns for quarterly total makes them stand apart from the monthly data in lines.
Comment from Mindy
Time: Wednesday, October 14, 2009, 10:14 am
Thanks! That is a slick way to get the column totals too.
Comment from angeline
Time: Wednesday, October 14, 2009, 12:44 pm
thanks, I manage to get what I am looking for. But now I have another problem.
I need to show the data table, with the blank cell, it does not look good. Any suggestions?
Comment from Jon Peltier
Time: Wednesday, October 14, 2009, 1:21 pm
Someday I’ll write a post entitled “Data Tables: How Can a Feature that Sounds so Good Be so Useless?” It’s not easy to get what you want with data tables. You can’t control what you show, you have limited ability to format the table, and it’s nearly impossible to reorder items in the table.
I rarely use data tables in my charts. If I need to show data from the chart, I set up a table in the cells near the chart, apply a little formatting, and call it good. Tushar Mehta describes how to do this in a tutorial called Custom Chart Table.
Comment from Ian
Time: Saturday, October 17, 2009, 10:49 am
I was up until 2 AM last night trying to figure out on my own how to do exactly this for my work presentation, until I found this site. It works (and looks) great…I’m speechless.
thx!!!
Ian
Comment from Joe
Time: Tuesday, October 20, 2009, 11:06 am
Worked exactly as described for Excel 2003. Thanks for the help.
Comment from J. Susnik
Time: Wednesday, October 21, 2009, 6:04 am
Jon,
I have commented before and got some very useful advice, thanks for that. I have another question. I have been looking at you piece on stacked bar charts with the gaps ’squeezed out’. This half solves my problem.
I have data which is also sub-grouped e.g.: 1 with sub groups 1a and 1b and 2 with sub groups 2a and 2b. ‘1′ and ‘2′ have a value associated with them. ‘1a and b’ and ‘2a and b’ have their own numbers but are essentially just breakdowns of 1 and 2. You can think of it as national numbers with regional breakdowns for example.
What I would like is to plot the values for 1 and 2 on the x axis (normal order, so bottom upwards) as a column, while the values for 1a and b and 2a and b are plotted on a secondary x-axis as columns with the axis plotted in reverse order (top downwards). I would like the ‘a’ and ‘b’ components to not have gaps between them (gaps squeezed out).
I hope this makes sense. Any help would be greatly appreciated.
Many thanks,
Janez
Comment from Jon Peltier
Time: Wednesday, October 21, 2009, 12:05 pm
JS – You have data for 1 and 2, and also data for 1a, 1b, 2a, and 2b? Are a and b stacked or side-by-side? Where exactly are the gaps?
Comment from J. Susnik
Time: Thursday, October 22, 2009, 3:41 am
Jon,
Thanks for your reply, i thought my question might be a bit ambiguous.
Yes i have data for 1 and 2, and 1a and 1b and 2a and 2b. 1a, 1b, 2a and 2b are essentially sub-sets of 1 and 2 showing, for example, regional details of national stats (represented by 1 and 2). So what i would like is for 1 and 2 to be plotted as columns from the bottom of a chart projecting upwards.
Then i would like clusters of ‘1a and 1b’ and ‘2a and 2b’ plotted from the top of the chart projecting downwards (essentially Values in reverse order) above the positions of 1 and 2 respectively.
It’s probably a question of how to arrange the data, but at the moment i can’t quite see how to do it. I have got close, but was not happy with the result.
Thanks for your continued help.
Janez
Comment from Jon Peltier
Time: Thursday, October 22, 2009, 7:55 am
Janez -
I was concerned about confusing readers if both the totals 1 and 2 are plotted along with the constituents a and b in the same chart.
I’m going to start a new series for questions like this: “You Asked For It”, after I check whether this title infringes on anyone’s prior art.
Comment from Gary Brooklyn
Time: Friday, October 23, 2009, 10:05 am
Hi Jon,
Hope you can help. I followed your steps above however I am having trouble with labeling a secondary X-Axis with Months rather than B through K headings as you have used in your example. Right now I have a chart that has both Primary and Secondary X-Axis labeled B through K. I can send in my spreadsheet for your review.
Your help is much appreciated.
Gary
Comment from Jon Peltier
Time: Friday, October 23, 2009, 11:14 am
Gary -
When you add the secondary series data, check Categories in First Column, but do not check Replace Existing Categories.
Since it’s already mixed up, select the primary axis series, and change the category labels from the B through K labels to the month labels you should have started with.
Comment from Gary Brooklyn
Time: Friday, October 23, 2009, 3:12 pm
Jon, I followed all those steps and I still get the same result.
Comment from Jon Peltier
Time: Saturday, October 24, 2009, 4:08 pm
Gary sent me his workbook, and I discovered that one of his series had only values of zero, and thus was invisible. Gary hadn’t moved this invisible series to the secondary axis, and this caused the primary axis not to change. As soon as this hidden series was moved, the axis changed as expected.
Pingback from Analytics Team » Blog Archive » Clustered, stacked column charts in Excel
Time: Tuesday, October 27, 2009, 11:42 pm
[...] utilize Excel and its charting capabilities. This is one I found quite useful today, how to make clustered, stacked column charts in Excel. Column charts are great, as are stacked column charts. But this shows you how to make [...]
Comment from nimish
Time: Thursday, November 5, 2009, 7:35 am
hi jon
impressive blog! . . one quick question – is there a way for creating a column graph with kinked axis? i wanted to show forecasted growth for 2009, 2010, 2011 & 2020. i was wondering how to insert a kink between 2011 & 2020.
thanks
nimish
Comment from Jon Peltier
Time: Thursday, November 5, 2009, 7:47 am
Nimish – I describe the technique in Broken Y Axis. It can be applied to the X axis instead of the Y axis.
Comment from Johan
Time: Monday, December 7, 2009, 10:55 pm
ahh.. thanks man, you of all people explained to me (after several hours) how to arrange this. Thanx.
Comment from Jennifer
Time: Tuesday, December 8, 2009, 2:25 pm
This is really really helpful! Thanks so much for posting. But if I want to include data table under the chart, how do I show the data in a clean way rather than the rearranged format just for the chart? Thanks.
Comment from Jon Peltier
Time: Tuesday, December 8, 2009, 4:46 pm
Jennifer -
The problem with the built-in data tables is their extreme inflexibility. You can only show the data in the chart, as it appears on the chart (rearranged in this case), in the order Excel wants to show it. It is more work to create your own display table in the worksheet, but you have much more control over what is displayed and how it is formatted.
Comment from Avi
Time: Tuesday, December 29, 2009, 10:31 pm
This post was extremely helpful for what I was trying to do. Thanks much for sharing….I’d been looking for a way to display my data in a similar fashion but didn’t know how to.
Comment from Jeff
Time: Wednesday, December 30, 2009, 6:41 pm
From you comment above on 10/14/09 @9:44 am, how do you make just the total column as bars and keep the three monthly columns as lines??
Comment from Jon Peltier
Time: Thursday, December 31, 2009, 2:35 am
Jeff -
Could you clarify? I thought that’s what I had done in that comment.
Comment from Giovanni
Time: Wednesday, January 6, 2010, 7:52 pm
Hello there,
Thank you so so much! You made my day :-)
Keep it up!
Giovanni
Comment from Susan in AZ
Time: Friday, January 15, 2010, 6:05 pm
Thank you so much for this article. It was exactly what I needed.
Comment from Cas
Time: Thursday, January 28, 2010, 1:59 pm
Hi John, wonderful stuff, i’m stuck like a few others trying to get Excel 2007 to differentiate between the two x-axes. I’ve added the second X axis, but both X axes still have the original number of categories, so my new labels are still all scrunched up to the left. I can’t see on Excel 2007 a means of specifying a different set of categories for the original X axis. Help!? Best regards, Cas
Comment from Jon Peltier
Time: Thursday, January 28, 2010, 2:48 pm
You might have to re-enter the X value range for all series. Excel 2003 would remember these even if they weren’t used, but Excel 2007 usually forgets them. It’s rather inconvenient.
Comment from Cas
Time: Thursday, January 28, 2010, 3:03 pm
Thanks John, I have re-entered and checked – the data ranges for the 2 sets of x-axes labels seem to be correct, but the scale of both axes remains set at the larger number of categories, so I have a long tail of blank categories under the bottom scale. How do I tell it to have only the required number of categories for the primary x axis?
Comment from Jon Peltier
Time: Friday, January 29, 2010, 10:50 am
To anyone still watching the comments, Cas told me that he had discovered a hidden series that still had the larger category range. He corrected this and the chart was fine.
Comment from savithri
Time: Saturday, January 30, 2010, 11:04 am
Hi Sir,
I am able to reach you here via FireFox 3.5.7 but not thro’ Internet Explorer 6. I Tried viewing, quite a few posts of yours in IE 6. But for this one, all of them are visible right till the end
savithri
Comment from Jon Peltier
Time: Saturday, January 30, 2010, 12:17 pm
Savithri – IE6 is obsolete. You should upgrade if only to reduce the security risk.
Comment from Martin
Time: Tuesday, March 23, 2010, 12:05 pm
Hi Jon,
Great tutorial! Really helped me a lot. I got one more question I want to ask you.
Is it possible to obtain an extra categorization on the x-axis (like you’ve shown in the first example in your comment on April 29, 2009), for the clustered-stacked column chart?
Taking example 3 in your update on 27 August 2008, I would like to have ‘Actual’ and ‘Forecast’ categorized on the x-axis together with the months, while the legend only shows the numbers 1 to 4, instead of ‘Actual 1′ to ‘Actual 4′ and ‘Forecast 1′ to ‘Forecast 4′. Or in the case of your tutorial, have ‘left’ and ‘right’ added to the x-axis, instead of showing in the legend.
Hope you can help!
Cheers,
Martin
Comment from Jon Peltier
Time: Tuesday, March 23, 2010, 8:57 pm
Martin -
You’re asking for more of a dual category axis chart, which I describe in Chart with a Dual Category Axis.
You’d need something like this data (yellow cells are completely blank, green cells contain a space character)
to get this chart
or this data
to get this chart
Comment from yoxi
Time: Thursday, March 25, 2010, 8:52 am
This is a great tutorial Jon!
Thank you!
Comment from mikezang
Time: Friday, March 26, 2010, 8:46 am
Hi, Jon
I have a question about how to change series index order.
I have a stock chart with some other series, the index 1 to 4 are OHLC, and volume is 7, 5 and 6 are for other data, I want to change 7 to 5, what can I do?
Comment from Jon Peltier
Time: Friday, March 26, 2010, 12:38 pm
If 5 and 6 are not the same chart type as 7, you may not be able to change the order of the legend entries. I wrote about this in Order of Legend Entries in Excel Charts.
Comment from Wen
Time: Thursday, April 8, 2010, 6:07 am
Hi Jon,
I didn’t follow well in this step:
how the primary axis becomes B, C, D when I set the secondary axis to B-K?
Thank you!
Comment from Jon Peltier
Time: Thursday, April 8, 2010, 6:30 am
Hi Wen -
The primary axis corresponds to the series with only three points, so only three category labels appear. They are B, C, D (the first three categories in the original chart) or Jan, Feb, Mar when the intended category labels are identified. I believe in Excel 2007, the range used for the labels is not always remembered when the chart is so drastically modified, so you have to change the series source data.
Comment from Tanya
Time: Wednesday, April 14, 2010, 12:01 pm
Thank you! This was a life saver.
Comment from Ai-Lynn
Time: Tuesday, April 20, 2010, 12:25 pm
Hello Jon
Thanks very much for this! I have a problem though. My uttermost right column appears to be cut in half (vertically) and thus the graph looks incomplete. How do I fix this? I tried to reselect the secondary axis (for the left and right columns) and every time i reselect it (change frm secondary to primary to secondary again) the preview looks fine but after that the right half of the uttermost right column is prematurely cut. I would be happy to send you my worksheet if you don’t understand what I am saying. Trying to describe graphs in words are really difficult!
Thanks very much!!
Ai-Lynn
Comment from Ai-Lynn
Time: Thursday, April 22, 2010, 7:28 pm
Hi Jon
I’ve sent you my worksheet. Was wondering if you got it. I know you’re probably busy but I’ve had problems with my emails not getting through before so I thought I’d check.
Thanks!
Comment from Jon Peltier
Time: Thursday, April 22, 2010, 8:17 pm
Hi Ai-Lynn -
I did receive your workbook, and just now had a chance to look at it.
All you need to do is extend your column data ranges by one more column. In your original they go from columns B to V, and they should go from B to W. The blank column in the data range is required for proper spacing of tha columns in the chart.
Comment from Jon Peltier
Time: Thursday, April 22, 2010, 8:17 pm
Hi Ai-Lynn -
I did receive your workbook, and just now had a chance to look at it.
All you need to do is extend your column data ranges by one more column. In your original they go from columns B to V, and they should go from B to W. The blank column in the data range is required for proper spacing of the columns in the chart.
Comment from Ai-Lynn
Time: Thursday, April 22, 2010, 10:53 pm
Thanks very much! I tried to do that the other day but failed but today’s was a success. Must have had too much of graphs the other night.
Ai-Lynn
Comment from Amanda
Time: Tuesday, April 27, 2010, 4:56 am
This helped a great deal!! Thank you so much!
Comment from Mira
Time: Tuesday, May 4, 2010, 1:19 pm
When I am constructing my stacked columnn graph the dates on the x-axis are displaying the same date. I have two fields with 4/30/2010 and 4/30/2009 respectively in the table. But the graph displays 1/1/2010 and 1/1/2009. Is this a glitch or have I done something to prompt this response? It has never happened before when I have used this type of graph.
Thanks.
Comment from Mira
Time: Tuesday, May 4, 2010, 1:55 pm
I found the solution: When I switch the date/month format from mm/dd/yyyy to dd/mm/yyyy it all works out fine. Although, why it should make a difference remains a mystery to me.
Comment from kjv1611
Time: Tuesday, May 25, 2010, 11:37 am
Thanks for this tutorial, Jon. I was helping out a coworker with what seemed like (in my mind) should be simple to chart, and seemed in Excel practically impossible. Following your advice here, I’ve successfully got a Clustered Stacked Column Chart built where some columns are stacked, and others are not. Good stuff.
I’m sure a lot of us are glad you left dealing with metals to dealing with mentals. ;0)
–Okay, the stuff you did with metals was likely very mental as well.
Comment from Brian
Time: Wednesday, May 26, 2010, 7:34 pm
John –
In Excel 2007, using stacked column charts, I have 2 data series. I know how to add data labels for each series (I have them displaying inside the columns). Can I add a total on top of each column?
Thanks!
Comment from Jon Peltier
Time: Wednesday, May 26, 2010, 9:33 pm
Brian – I show how to do this in Label Totals on Stacked Column Charts.
Comment from Brian
Time: Thursday, May 27, 2010, 5:22 pm
Thanks Jon!
Comment from Brian
Time: Thursday, May 27, 2010, 5:29 pm
One more thing – where’s the “buy me a cup of coffee” button???
Comment from Jon Peltier
Time: Thursday, May 27, 2010, 6:42 pm
Brian – The cup of coffee thing was pretty much of a distraction, so I’ve removed it.
Comment from Thom
Time: Thursday, May 27, 2010, 7:28 pm
No more buy you a cup of coffee? :-(
Pingback from Column charts with negative axis | ohsomuchwork
Time: Monday, May 31, 2010, 12:32 pm
[...] really good tutorial can be found here on how to create “clustered-stacked column charts”. It works very well in Excel 2007 [...]
Comment from Pranav
Time: Sunday, June 6, 2010, 8:39 pm
Hi Jon,
My data is broken in to four categories. To compare two year’s of data, I made a stacked column chart for two years of data and use series lines to show comparison against categories. To visualize imagine two stacked column charts (side by side) with series line going across to show year to year changes.
My question is – ” Is there a way to fill the area between two series lines?”
I use Excel 2003 for my work
Comment from Jon Peltier
Time: Sunday, June 6, 2010, 10:32 pm
Pranav -
I can’t think of a built-in way to color between series lines in a column chart. I suppose it’s possible using a combination area-column chart, but it would be very complicated to get the area series to match up with the columns.
Comment from Sam
Time: Monday, June 7, 2010, 12:54 pm
This was an awesome tutorial! Thank you so much!!
Sam
Comment from Heidi
Time: Thursday, July 15, 2010, 12:44 pm
Jon,
Were you ever able to help with Jenni’s problem (Comment from Jenni
Time: Tuesday, July 1, 2008, 5:19 am) with the error bars that extend all the way down to the x-axis?
I’ve made my stacked, clustered chart (thank you ever so much for your tutorial!), but am having Jenni’s problem with my error bars.
Any help would be greatly appreciated!
Thanks!
Comment from Jon Peltier
Time: Friday, July 16, 2010, 6:05 am
Heidi -
I don’t recall the issue now, but I had replied later the same day with a solution for Jenni.
Comment from Myriam
Time: Tuesday, July 20, 2010, 10:05 am
What do you do if you want 1 or 2 unstacked columns to be associated with the value axis with one scale and the stacked column to be associated with the secondary value axis with a different scale? The way the graph is set up not, the stacked and unstacked columns both have to be on the same secondary value axis scale. Thanks in advance!
Comment from Jon Peltier
Time: Thursday, July 22, 2010, 1:32 pm
Two Y axis scales in a chart make that chart confusing. Trying to apportion different column chart series between the dual axes makes the chart much more confusing. If the columns use different axes, there is no value in keeping them in the same chart, or in the same panel in a panel chart.
If you shoot me some data, I could show you what I’m talking about. Make sure you don’t mind the data appearing here for all five of my subscribers to see.
Comment from Rod
Time: Tuesday, July 27, 2010, 4:50 pm
Jon,
I’ve got a question I hope you can help me solve. I’m trying to make a clustered stacked bar chart with one column as a $ amount and the stack a % of that dollar amount. The cluster is to compare distributor net sales and direct net sales. I would then like to compare it across 3 product segments between 2 years. Example using shoes: Showing columns with net sales and the stacked column inside showing gross margin of those sales. Compared between years 2008 and 2009. Across the 3 categories of running shoes, basketball shoes, and dress shoes. I was thinking it would be least confusing to put a vertical seperator between the three categories and within each of the segments compare 2008 direct (one bar) and distributor (other bar) as a cluster with GM% stacked inside and 2009 direct (one bar) and distributor (other bar) as its own cluster again with gm% stacked inside. So it should have a total of 6 2bar clusters (direct and distributor), across 3 segments (running, basketball, dress – 2 clusters each segment, 1 for each year), with both bars in each cluster having two items (net sales, gm%). Hopefully I did’nt make this sound too confusing and you will be able to help me. Ohh and I’m using excel 2003.
Best Regards, Rod
Comment from Jon Peltier
Time: Tuesday, July 27, 2010, 9:57 pm
Rod -
It makes no sense to put sales (in $) and margin (in %) into the same stacked bar. How do you decide how much $ corresponds to how much %? And more important, how will anyone interpret the result?
I’d make two charts, or a panel chart with two panels, sales in the bottom, margin in the top. The panel would bel ike the one in How to Build a 2×2 Panel Chart, half as wide.
Comment from InternationalShipping
Time: Tuesday, August 3, 2010, 10:27 am
Please forgive my ignorance, I just a newbie when it comes to charts, I have gone through the post and, the first thing that came to my mind is what is the business applicability of “clustered stacked column charts”, lets take for example shipping figures to US, UK, Japan for a period of 1 year. What would “clustered stacked column charts” help us visualize.
Comment from Melia
Time: Tuesday, August 3, 2010, 6:49 pm
Thanks for your expertise on all of this…
I’m currently running into a problem while trying to update a stacked chart that was working fine in Excel 2003, but now that we’ve switched to 2007, suddenly the data isn’t translating to the chart like it used to. I’ve changed nothing about the chart’s layout except to add some data to it, and yet a few cells’ worth of data is not translating to the chart.
I’ll include the screenshots below, but essentially, in the chart, April, May, and June 2010 are all missing their “Calendar Project” data, displayed in the purply/red color in all other months. June is also missing its “on-going referrals” data, which should show up in blue. (the light purple isn’t missing as its value is 0 for both May and June).
Screen shot of the chart: http://www.flickr.com/photos/melizoic/4858650860/
Screen shot of the data layout: http://www.flickr.com/photos/melizoic/4858030939/
The blue-highlighted boxes in the data layout are where the data isn’t translating to the chart. As far as I can tell, the numbers are clearly included in the data set-up, but are for some reason not translating to the already-formatted chart. If I create a new chart from the data, the numbers suddenly do show up, but the chart no longer includes the correct x-access “month” descriptors; rather, it substitutes numbers 1-40something under each bar. It took me awhile to set up the original chart, and ideally I’d like to update that instead of re-inventing the wheel (since I’m having trouble finding my way around 2007’s chart creation as it is).
Any thoughts on why some of my data might not be translating to the chart, when it always worked before? And/or how to get my x-axis correctly labeled with the month names from column A, if I do have to start from scratch?
Thanks!
Melia
Comment from Jon Peltier
Time: Wednesday, August 4, 2010, 10:32 am
The cluster-stack chart would be good for showing data with three dimensions. For example, you might have separate clusters for US, UK, and Japan. You might have different columns within a cluster for categories of shipment. Each column might have stacked boxes representing Q1, Q2, etc.
Comment from Liz
Time: Tuesday, August 10, 2010, 11:48 am
I can’t work out how to add a secondary axis…I have looked at the chart options / axis…but there is no secondary axis box to check…help??
Comment from Jon Peltier
Time: Tuesday, August 10, 2010, 12:38 pm
Liz -
Is there more than one series in your chart? Can’t have a secondary axis without a second series.
Comment from Liz
Time: Wednesday, August 11, 2010, 2:45 am
I think I have 4 series…I have 4 rows of data (2 of each stacked on top of each other)…sorry I have quite basic knowledge of these things
Comment from Yair
Time: Thursday, August 12, 2010, 5:32 am
Thank you for this – helped me immensely.
Comment from Jon Peltier
Time: Thursday, August 12, 2010, 1:43 pm
Liz -
To obtain a secondary axis, you first have to assign an existing series to the secondary axes. Select and format the series you intend to plot on the secondary axis. On the main tab of the Format Series dialog in 2007 or the Series tab in 2003, choose Secondary. You may have to explicitly tell Excel which secondary axes you want, from the Axes command on the Layout tab (2007) or the Chart Options command on the Chart menu (2003).
Comment from Sid
Time: Thursday, August 19, 2010, 6:05 pm
Hi Jon,
Awesome website…thank you very much.
I’m having trouble with something..On one of the last steps when you say “In Excel 2007, right-click on the axis, choose “Format Axis”, and at the bottom of the Axis Options tab, select the “On tick marks” option under “Position Axis”.”
When I try this Jan goes to the left corner, Feb goes to dead center and March goes to right corner of the graph rather than under the respective bars. Can you please let me know how to fix that?
Thank you.
Comment from Jon Peltier
Time: Friday, August 20, 2010, 7:23 am
Sid – Do this to the secondary axis at the top of the chart (B, C, D, etc.), not to the primary axis at the bottom.
Comment from Sid
Time: Friday, August 20, 2010, 4:45 pm
Thank you Jon…that worked perfectly – Awesome
){kind=link}
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.