i remember to have seen some cool stacked charts
here

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.

[...] 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 [...]

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?

Mark - Forward thee file to me at jonxlmvp at peltiertech dot com, and I’ll look at it.

This is excellent info - thanks for posting. Solved my problem today!

Thanks! Solved my problem today too!! YEAH!

Hi Jon,

Thank you very much for your great help!! I finally figured it out!!!

Have a great day~ =)

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

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.

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?

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?

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.

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.

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.

Thank you so much, my problem is solved! I got through it in a snap!

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 :)

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.

AWESOME -

Exactly what I needed. Got me 99% of the way.

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?

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

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.

Thanks. Great help ^_*

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?

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.

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!

JR - Format the appropriate horizontal axis so the vertical axis does not cross at the maximum.

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…

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.

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.

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!!!

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.

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.]

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. :)

Tee - Change the largest value to 60-(27+11), then when it is added as another column on the stack, the sizes are right.

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. :)

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

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.

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

Laura -

I envisioned this approach when you mentioned a line:

Add a Target 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:

Record Your Own Macro

The code you want looks something like this:

Sub AdjustYAxis()
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 0
        .MaximumScale = 10
        .MajorUnit = 1
    End With
End Sub

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

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.

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!

Robya -

Use the procedure shown above, but set up your data like this:

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

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.

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!

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.

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.

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

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.

Is there anyway to show the data table under the graph in the correct format?

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.

I got it! Thank you so much. The project is finally done.

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

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.

John,

Thank you very much for your additional assistance. I finally succeeded.

Valerie

Thanks so much for this!

This was a great help and was exactly what I was looking for.
Thanks

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!

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.

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!

Petya -

I’ve added a post showing Clustered-Stacked Column Charts with Vertical Separators.

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!