Excel has built-in chart types for clustered columns and bars, and for stacked columns and bars. One of the commonest charting questions in online Excel forums is, “How do I make a chart that is both clustered and stacked?”
This article demonstrates a protocol that works in both modern versions of Excel, that is, Excel 2003 and earlier and Excel 2007 and later. The technique is a bit convoluted, and it requires an expanded data layout to get the appropriate appearance. And there’s an additional degree of complexity to get the category labels to line up neatly under or beside the clusters.
For those who need to produce many of these charts, and who don’t have 15 minutes to spend on each one, I have created the Peltier Tech Cluster Stack Chart Utility, a commercial Excel add-in that does the heavy lifting at the click of a button.
Built-In Column and Bar Charts
Let’s start with this simple data set, which compares budget and actual values for three commodities for two quarters of the year. We want to have clusters for each commodity, with stacked actual values next to stacked budget values within each cluster.
Without any effort or thought we can easily create clustered column or bar charts from this data.
Stacked column and bar charts are just as easy.
It’s not obvious how to combine the chart types. The protocol involves inserting blank rows and cells into the data range of a stacked column or bar chart, and values only appear in some of the places in the chart. The proper arrangement will cluster stacks of values with stacks of zeros separating the clusters.
Starting the Chart
I’ll leave the original data alone (always a good practice) and create a staging data region which is linked to the original data. The easiest way to do this is to copy the original data, then use Paste Special Link to start building the staging area. We’ll make our chart first, then explore how modifying the data layout changes the chart. In practice, we’ll modify the data first and then make the chart, knowing the effects of data layout on chart appearance.
The first step is to make a stacked column or bar chart from the data in B6:E9. There are no categories selected (i.e., the commodities are not part of the initial chart), so Excel just uses the counting numbers 1, 2, 3.
Since categories always start from the origin, the bar chart’s category labels go from the bottom up, instead of top down as in the sheet. So the vertical axis has to be formatted to make the categories go in reverse order. Also the value (horizontal) axis has to cross at the maximum category, which is at the bottom now, since the order of categories was reversed.
Adjusting the Data
So that’s only stacked. Let’s adjust the data by inserting some rows.
The stacks of columns/bars are now spread out. Not yet what we want.
But lets stagger the budget data by a row, to move the budget data points off the actual data and onto blank slots in the chart.
Unfortunately the staggering doesn’t happen automatically, so we have to go back and tell Excel what data range to use for the chart. Right click on the chart, then select Select Data or Source Data (the command is version-specific). Click in the Chart Data Range box, and select this whole data range.
One more adjustment to the data. Let’s insert a row at the beginning and end so there’s a space outside of the first and last cluster.
Again, we have to explicitly tell the chart about the updated data range. This is almost what we want.
Reduce the gap between columns/bars to give the chart a clustered appearance, and color code the data series to make it clearer which data series are associated.
In practice, it is not necessary to create a chart using the compact data and adjust it after every modification to the data. The correct protocol is to adjust the data, and then make the chart shown here, and proceed with adding labels, below.
Adding the Labels
Almost done. We need to add the category (cluster) labels. We’ll do this by adding a “dummy” series to the secondary axis, and the secondary axis will have the category labels we want. Add a column to the original data range for the dummy axis series (column F in our example).
Select this added data (F1:F4), and hold Ctrl while selecting the column with our labels (A1:A4), so that both areas are highlighted. Make sure you include the blank top cell in the first column. Copy the range, and use paste special to add this data to the chart as a new series, in columns, with series name in the first row and category labels in the first column. In other words, use these settings:
In Excel 2003 and earlier, the original labels (1, 2, 3, etc.) remain along the axis, but in 2007, the new labels take their place, even if we hadn’t checked “Replace Existing Categories”.
Since zero value bars have zero height or width, they don’t appear in the chart. Just to show where this new series is added, I’ve temporarily replaced the zeroes in column F with values of 500. The series spans only the first three categories.
If you’re making a stacked-clustered column chart, convert this new series to a line chart type. Sometimes Excel 2007 doesn’t expand the legend enough to show the legend entry for Axis, so I’ve stretched it in this chart.
Manipulating the Axes
Now format the Axis series to place it onto the secondary axis. To do this, select the Axis series. If you can’t see the Axis series, click the dropdown in the top left of the Chart Tools > Layout or Format tab, and choose the Axis series. Then press Ctrl+1 (numeral one) to open the Format Series dialog. On the first tab, choose Secondary Axis.
Now add the secondary category axis, which is secondary horizontal in 2007 column charts and secondary vertical in 2007 bar charts. This command in on the Chart Tools > Layout tab.
We need to reverse the bar chart’s secondary vertical axis (like we did the primary when we first made the bar chart), and at the same time, make the horizontal axis cross in the automatic position, which generally means at zero or at the minimum.
Now we have to switch the position of the category labels. In the column chart, format the left hand vertical axis so the horizontal axis cross at the maximum, and format the right hand vertical axis so the horizontal axis crosses at the automatic (minimum) position.
In the bar chart, format the bottom horizontal axis so the vertical axis cross at the maximum, and format the top horizontal axis so the vertical axis crosses at the automatic (minimum) position.
Important – Axis Label Alignment
We want only half a slot on the outside of the first and last clusters, not the full slot shown above, to center each cluster on the commodity category labels. Format the top horizontal axis of the column chart, or the right vertical axis of the bar chart, so the crossing axis is positioned on tick marks. The bottom horizontal axis or the left vertical axis of the chart, which contains the labels we just worked so hard to add, should have the crossing axis positioned between tick marks.
Select the secondary value axis, which is scaled from 0 to 1 (the right vertical axis of the column chart, or the top horizontal axis of the bar chart), and delete it.
Format the primary category axis, which is scaled from 1 to 10 (the top horizontal axis of the column chart, or the right vertical axis of the bar chart), and format it so it has no tick marks or tick labels, and no line type.
Finally, select the Axis legend entry. In Excel 2003 be sure to select the text label, not the legend key (the marker and line). Press Delete. In the column chart, format the Axis series to be invisible (no marker, no line).
That wasn’t so hard, was it? Though it did take a very long time.
Adding a Line to a Clustered-Stacked Column Chart
It’s relatively easy to overlay a line chart series onto the clustered-stacked column chart. Instead of the column of zeros we used to generate our commodity axis labels, put in the values you want to plot, and add a meaningful column header.
When you go through the process above to add your labels and manipulate the axes, you will end up with data points where you want them. Just don’t bother hiding the series at the end of the process. If you want to show the line on a secondary axis, despite my warnings to the contrary, don’t delete the axis, simply scale it appropriately to your data.
Adding a line to a cluster-stack bar chart is much more complicated, so I will not cover it here.
Peltier Tech Cluster Stack Chart Utility
This tutorial shows how to create Clustered-Stacked Charts, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, can be prone to error, and becomes tedious.
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.
Did you change the instructions for the clustered and stacked column chart from last week? I made several charts succesfully using the older version instructions but this update has me totally confussed!
Heather – The changes are minor, and overall, I think this protocol is easier.
I guess I dont’ handle change well! ;) and I am having troubles replicating my charts – would it be possible to send me the old instructions via email?
Thanks Jon. This is very cool. I actually do this using pivot table and chart it. However, I was wondering if you can cluster ‘actual’ stacked bars side by side and then the ‘budget’ stacks side by side, instead of coffee actual and budget together.
I have a bar graph with 4 bars of data but one of the bars keep overlapping with another and I don’t know how to set it so they all sit next to each other, please help!
Sorry – forgot to mention, there is a scale on either side.
Hi Donna -
Sounds like your series are not all on the primary axis. The one(s) on the secondary axis are in front of those on the primary axis.
Is this a general question, or have you gotten stuck using these instructions?
Hi Nina -
I think what you want to do is transpose your data (switch rows and columns) and then follow the procedure to modify the data and create the chart.
For the column chart, how do you switch the primary horizontal axis to be on top (1,2,3,4…10) to make the secondary horizontal axis (Coffee, Tea, Milk) to be on the bottom.
Thanks Jon, I figured out a different way coz I am using pivot table and chart. I separated the data into two columns and plugged in zeroes in the blanks and grouped the variables in rows.
Your website is very helpful. I have learnt a lot about macros, add-ins and coding from your website. Thank you so much.
Jon:
Is it possible to create bar chart (100% stacked bar) with a secondary x-axis (horizontal)? I would like to chart percentage totals for multiple products, across more than one year.
I can create a 100% stacked bar chart with percentage on the x-axis and years on the y-axis. As I add years the chart gets taller. I would like for years to move horizontally. So, 100% product mix move across horizontally by year.
Thank you.
Sorry – this is a general question. When I put them all on the primary axis, my scale on the right hand side disappears.
Thanks for the instructions. It was a little confusing at first for me because my data was transposed from your example.
I tried inserting blank columns instead of rows, but Excel 2007 did not handle that the same way. So, I transposed my data and your procedure worked like a charm.
Would be helpful to see the Series Values as things progress.
In my original clustered chart, I had a line chart on the secondary axis. Changing to a clustered stacked chart really did a number on that. Once I finally realized that it WAS drawing the line, but since I had only 1 entry point every 5th cluster, I was not getting a line. Added Data Labels to see where the line would have been, then added Markers so I could have a visual of the line.
All-in-al, very helpful. I learned a lot about how Excel does clustering.
In Excel 2003 I am not able to go from the first picture to the second picture in section ‘Manipulating the axis’, because when I add a second category x-axis (after adding the second y-axis) this wil not result in a stretching of the line graph and thus the newly added categories stop short, i.e. I cannot align properly later on with the original x-axis. Am I doing someting wrong?
I am using Excel 2010. Trying to make a graph with horizontal bars.
When I try and insert the ‘dummy’ series, I can’t use the ‘Paste Special’ function as it doesn’t seem to exist. If I set it up as another series the only way I can use the category values is to replace the x-axis labels.
I tried using the dummy values then initially converting to a line, then the idea was to put it on a secondary axis then add labels. Once I change the format of the data series to line, it automatically sets up a a secondary y-axis but no x-axis, and the option of secondary axis for this data series is greyed out.
I am going round in circles, maybe someone can help??
Lisa -
What version of Excel? To Paste Special, first copy the range, then select the chart. In 2003 & earlier, go to Edit menu > Paste Special. In 2007 and later, on the Home tab of the ribbon, click the down arrow on the Paste button, and click Paste Special.
Roy -
“I had only 1 entry point every 5th cluster”
Go to the source data dialog, click on hidden and empty cells, and choose the interpolate option for empty cells. This will draw the lines to connect the markers across the gaps caused by the empty cells.
Thanks so much!!!
I’ve been trying to make a time line graph for planned and actual dates for each project. I’ve been working on it for ever to try and have both clustered AND stacked bars….
you saved the day, and in such an elegant and simple way to the one’s I tried before I found this site (secondary axis, graph on graph etc.)
THANKS!
THANK YOU so much for sharing how to do this. You’re instructions are AWESOME!!! I have been trying to do this for some time and finally found some much needed help!
You ROCK!
i was able to do the graph, however, when i adjusted such that there will be no gap, the graph indeed removed all the gaps, including the supposed to be gaps between each category. what do i do with this?
thank you!
Susie -
Do you have blank rows in the adjusted data, between data for the clusters?
i see. i wasn’t able to add blank rows on the adjusted data.
thank you very much! it worked perfectly now! your articles really helps a lot ;p
There is a much more simpler way of producing such a chart in Excel.
Put your data in the format below:
Group A Group B Group C
Level 1 Level 1.1 5 5 5
Level 1.2 5 5 5
Level 2 Level 2.1 5 5 5
Level 2.2 5 5 5
Level 3 Level 3.1 5 5 5
Level 3.2 5 5 5
The Level 1,2,3 are merged cells. Level 1 is merged across level 1.1 and level 1.2
Select this entire data set and press the graph button in excel. Select clustered chart and see the magic.
Alok -
I’ve taken a screen shot of the data to clarify it for other readers. I will note a few things:
1. I think you meant to choose a stacked chart. The top chart below is clustered, the bottom one is stacked.
2. Merging the cells makes no difference; Excel centers the outer labels across the inner labels regardless.
3. This groups the stacked columns using lines among the axis labels. This is fine in some cases, and I’ve written about it in the past (see for example Chart with a Dual Category Axis). But it has issues:
a. There are excess lines among the axis labels, which may make the chart seem cluttered.
b. There is an extra level of labels which may not be needed and may in fact add to the clutter.
4. You don’t get the visual clustering as in the tutorial above. Sure, you can reduce the gap width, add blank rows in the data (but make sure the cells highlighted yellow contain a space character to preserve spacing of axis labels).
But this does not solve the cluttered axis issues in point 3.
Jon,
Thank you so much for this information. I still struggled with it a lot and spent a couple of hours manipulating the chart and data to get it to look right for my purpose, but your instructions made all the difference in the world! A million times thank you!
DB
Jon,
Thanks for a super-helpful tutorial! However, I’m stuck on the “add the secondary category axis” step – are you able to tell me how to do this bit? I have added the “axis” series, and displayed on secondary axis, but this is a secondary vertical axis (mine is a column chart). Your next step says “which is secondary horizontal in 2007 column charts” – this is the bit I’m stumped on.
Thanks,
Kate
Jon
Thanks for your efforts. Is it feasible to apply clustered columns with a Broken Y Axis?
Regards
Brent
I’m also stuck on the “add the secondary category axis” step.
Thank you so much! This helped me do EXACTLY what I needed to do.
I was tearing my hair out when I found this.
Great help.
Thank you.
Hi, I’ve followed this (up the line bit, I don’t need a line) successfully, however as I have more data than you (weeks of the year, 31-52) I can notice that the bars start to visibly converge with the right hand tick mark on the Category Axis around the 6th week.
So you have any idea what, if anything, I can do to stop this?
Ignore my last comment, just needed to include another row of data in my source data and now they’re all nice and tidy.
For those struggling adding the secondary X axis, I had the same problem, see http://www.andypope.info/tips/tip008.htm that’s what helped me.
I have the blank spaces in and there are gaps between the bars but there’s still a blank legend for the gap that I can’t get rid of. If I delete it in the legend entries the gap goes away. Help please.
Hi Jon,
Is it possible to graph the double-stacked bars (primary y-axis0 on the same chart as a line (secondary y-axis)?
Melissa -
You must still be using Excel 2003.
Click once to select the legend, then click again on the text of the label you want to remove., not just on the little square in front of the label. Then press Delete.
Eddie -
The protocol uses a line chart series on the secondary axis to provide the axis labels. If you want a line series to put a marker where each stack in a cluster is, rather than centered on each cluster, you need to add a new series, move it to the primary axis, and make sure it’s a line series. Don’t mess with the line series that’s holding the axis labels in place.
The data would be arranged like this, based on the last worksheet screenshot of the post (before the comments):
Series Name: Cell F6
Data for Markers: Cells F8, F9, F11, F12, F14, F15
Blanks which are still part of series Y values: Cells F7, F10, F13, F16.
First, thanks for the amazing tutorial! I’ve actually enjoyed work this week because I’m learning. I am, however, stuck when trying to add a line to a clustered-stacked column chart. My data points (retail units in thousands) show up but I can’t get the connecting line to as well. Any idea? I was able to follow your instuctions until it’s time to manipulate the axes. Adding images of cell data along teh way would be helpful. I can’t tell if you’re actually typing “coffee, tea, milk” and “last year” into the ‘active’ cells B6:E16 or not. If you are, how and where?
Rebekah -
Coffee, Tea, Milk are in A2:A4, and Last Year’s label and values are in F1:F4, as shown. I simply used these values instead of zeros, and unhid the markers and line so the points would appear.
Thanks, Jon. I didn’t understand your reply so perhaps I’m not asking the question correctly. I wish I could share an image so you could see what I mean, but I can’t from a work laptop. At this point, I’m stuck because I have coffee, tea, and milk on both the top and bottom horizontal axes. No clue how yours became 1-10! It doesn’t look like anything happens when I click on secondary horizontal axis from left to right. ??? (Excel 2007, column chart)
If we can directly email, please let me know!
Jon,
I am not able to convert just the axis part to a line graph. Can you please tell me how to do that part of the instructions? If possible a screen shot would help a ton.
Thank you,
Corwin
Corwin -
I’ve enhanced the instructions for this part:
Select the Axis series. If you can’t see the Axis series, click the dropdown in the top left of the Chart Tools > Layout or Format tab, and choose the Axis series. Then press Ctrl+1 (numeral one) to open the Format Series dialog. On the first tab, choose Secondary Axis.
Rebekah -
The beverage labels typically appear when you add the axis label series. They disappear when you move the axis label series to the secondary axis, and the original axis labels come back. The beverage labels reappear on the secondary horizontal axis (secondary vertical axis if it’s the bar version you’re making) when that axis is added to the chart.
Thank you, thank you, thank you!! These instructions are awesome.
Hello Jon,
I have been struggling to get a basic chart with excel 2007. It was much much easier with 2003.
I have a table as below.
Step1 Step2 Step3 Step4 Step5 Step6 Step7 Step8
Rel 1 1′ 1′ 1′ 3′ 2′ 2′ 1′ 1′
Rel 2 1′ 1′ 1′ 1′ 2′ 1′ 3′ 3′
Rel 3 1′ 2′ 3′ 3′ 2′ 1′ 1′ 2′
Rel 4 1′ 1′ 1′ 2′ 2′ 2′ 3′ 3′
Rel 5 1′ 1′ 1′ 2′ 2′ 3′ 3′ 3′
Legend 1′ Done
2′ Not Done
3′ NA
Rel stands for releases and multiple releases are being worked upon in parallel.
I have to show the progress status of each release of the program in the form of a stacked column chart.
So basically, I need the releases in the x-axis, the steps in the y-axis, and equal length columns with 8 divisions, represented by 3 colors – done, not done or not applicable.
I am just not able to get the Steps 1 to 8 in the y axis.
Can you please help?
Oops…I do not think the cut and paste of the table came our well.
Basically each release has 8 steps and people are working parallely on multiple release.
So each release has some steps completed, some not completed and some not applicable.
In order to show status of each release w.r.t to the 8 steps, I need to have a stacked column chart. We can represent done, not done and not applicable step by different colors but each step needs to be of same dimension in the chart.
John,
I have followed your workflow and everything works correctly with your sample data however when I input my data namely a dates for secondary X axis it starts acting weird. The dates I use 7/1/2011 and 7/4/2011 and 7/11/2011. I place the dates where you have coffee milk and tea. When I get to secondary chart and expand it out it doesn’t expand out with those three dates it does a range from 7/1/2011 to 7/11/2011.
Jon, Sorry I misspelled your name the first time and accidentally hit send before I finished.
I have followed your workflow and everything works correctly with your sample data however when I input my data namely a dates for secondary X axis it starts acting weird. The dates I use 7/1/2011 and 7/4/2011 and 7/11/2011. I place the dates where you have coffee milk and tea. When I get to secondary chart and expand it out it doesn’t expand out with those three dates it does a range from 7/1/2011 to 7/11/2011 in integrals of 1. Is there a way to make sure it applies my dates?
Cedric -
Excel recognizes your dates as dates, and tries to fit them on a date-style category axis. Select the axis, open the formatting dialog (Ctrl+1 is a handy shortcut), and on the main screen of the dialog, under Axis Type, change Automatic or Date to Text.
Hi. Thanks–very helpful. One remaining question: If I want to order the way my bars are stacked, can I do it? I’ve tried rearranging the data columns, but that hasn’t worked. I’m trying to use the stacked bars to model heights, and I want a particular column to be at the top of my stack.
KR -
Without special measures, all stacks have the series in the same order. To make one column sit at the top, it has to have the largest plot order of all the columns. Change the plot order by changing the last argument of the SERIES formula, or by rearranging them in the Select Data dialog, by selecting a series in the list and pressing the up or down buttons above the list of series.
My boss wants me to create a chart for him, and I don’t know if it’s possible. He wants a combination of unstacked and stacked columns. The first column would be the total college enrollment. The stacked column (next to the first column) would be broken into Outreach enrollment numbers for the various Outreach programs. By putting the college enrollment and the Outreach enrollment side-by-side, he hopes to show what percentage of total enrollment is occupied by the Outreach programs.
I hope I explained this in a way that you can understand. I would appreciate any suggestions.
Hi Patty -
This is not difficult to do, by following this protocol. Your data needs to be all in columns, one column for Total College Enrollment, then one column each for all of the Outreach programs.
If you see my expanded data, Q1 and Q2 Actuals are in one row, Q1 and Q2 Budget are in the next, then there’s a blank row, and this pattern repeats. In your data, Total College Enrollment goes in its own row, then all of the Outreach are in the next row, then a blank row, repeating as needed.
John, this is so great, I can ALMOST see it doing what I need to do.
But, before I can plug my own, real data into your ‘template’, I need ot get past:
“We need to reverse the bar chart’s secondary vertical axis (like we did the primary when we first made the bar chart), and at the same time, make the horizontal axis cross in the automatic position, which generally means at zero or at the minimum.”
For the love of me, I have no idea what this means, and I am flat-out stuck.
Can you help a sister out?
Thank you.
Forget my previous question – I finally got around it.
I do have a follow up question to Jon, or anyone else using this method.
Is there a max number of data points you can use?
As I see it, the sample has 3 – Product, Quarter, Actual.
Could you do it with 4? Say, site, job gain, job loss, month?
I can’t see a way, yet, and I wonder if that’s because there isn’t?
Lauren -
This approach breaks the data among three factors. There is no clear way to break it down further. What you would have to do is either combine one factor (e.g., lump all quarters together to get a total) or make separate charts to show each factor (one chart for each quarter).
Jon:
I cannot get the Q1 Budget and Q2 Budget to move to the “2″ column. I understand that i have to right click the chart and click “Select Data Source” (excel 2007), however, I don’t know what to do from there? I’ve tried a number of thing to no avail. What am I missing in your instructions?
Thanks
Scott -
Have you offset the budget values down one row from the actual values?
Yes. I recreated your spreadsheet and I inserted the extra rows and moved the Q1 and Q2 budgets down one row.
I’m not able to convert my “print screen” image to a picture for you to link to help us out.
Jon,
Good news! I figured it out. So, I have a suggestion. Edit or add this to the following:
“Unfortunately the staggering doesn’t happen automatically, so we have to go back and tell Excel what data range to use for the chart. Right click on the chart, and select Select Data or Source Data (the command is version-specific)….
… When the “Select Data Source” window pops up, click in “Chart data range” and select your entire chart data again.
Thanks for being willing to help. I may have another question. LOL!
Scott
Great post. Everything worked like a charm until I get to:
“We want only half a slot on the outside of the first and last clusters, not the full slot shown above, to center each cluster on the commodity category labels. Format the top horizontal axis of the column chart, or the right vertical axis of the bar chart, so the crossing axis is positioned between tick marks.”
I totally get what the purpose is, but in Excel 2003 I can’t figure out how to do this. Could you possibly tell me ste-by-step how this can be done in Excel 2003?
Thanks!
Kris -
Select the axis, press Ctrl+1 (numeral one) to open the Format Axis dialog, and where you edit the scale of the axis (first view in 2007+ or Scale tab in 2003-), check the option whose labels indicate “crosses between”.
That worked perfectly. I tried that multiple times before and now I realize that I needed to DESELECT that option – Duh. Thanks for your help!!
Hello Jon, wondering if you can help with this. I used your example and would like to display the following:
A X Records B X Records C X Records D X Records
BAFB 44 20 60 30 503 307 10 5
SAFB 11 5 84 40 194 50 10 5
BAFB 34 10 177 50 927 500 10 5
MAFB 16 8 80 40 1258 600 10 5
So BAFB will shoe A (44) and X (20) stacked then B (60) and X (30) stacked through D (10) X (5). I can get your example to work but cant seem to figure out how to add 2 more columsn. Thanks very much, Anthoyn.
Hi Jon,
I have a requirement to draw clustered Stack chat with the following information,
Products Total Spent Forcast
A 10 6 4
B 14 10 2
C 2 4 3
Spent and Forcast should be in stacked with total in clustered. How can I achieve with excel 2007?
Hi Jon,
After going through this post I was able plot my chart on my requirement mentioned in comment :).
Thank you very much for this post.
-Mahesh
I just want you to know what a great help your website has been to me over the past few years. Tips like this have solved SO MANY of my frustrations with excel when I know what I want something to look like but don’t know how to create it. A MILLION THANKS!
Your tips were great and I was able to create the graph I wanted, but now I need to insert error bars and it is not working. I can only get excel to add error bar and I have 5 pairs of bars. I have made tons of figures and know how to use the custom function. Have you had issue with error bars and stacked figures? Any ideas on how to fix it.
Thanks.
This was excellent, thanks. Your grasp of every detail in Excel never fails to impress!
Very helpful, although I can’t seem to center my axis labels between two stacked columns. I lines up directly under one column. I know it has to do something with the “summy” series, but I can’t seem to figure it out. By the way, I am using Excel 2007
Hi, I can’t figure out what you mean by paste special under the adding the labels section. And i also can’t get the category labels and the axis line to spread to the rest of my chart. They stay under the first set of clustered/stacked columns. Would you be able to help me out? Thank you.
Otherwise, this seems to be the data I’m looking for, as I am seeking to have clustered, stacked columns and then add a line on it to show percentage trend.
After you copy the data, select the chart, and from the Home tab, click on the down arrow thingy at the bottom of the Paste button (or in 2003, choose Edit > Paste Special) and select Paste Special.
Then follow the instructions carefully to make sure you move the label series to the secondary axis, add the secondary horizontal axis, then make sure to format the primary horizontal axis crosses setting is set to “between tick marks”.
Thanks for this article! It helped me a lot.
Thanks! It worked!
This is a great article. Kudos!
Hi Jon
I need to create a bar chart for my boss with the following sample data. I have created my secondary axis (% identity) which is on the right. However the numbers on the primary axis comes up twice!! Please can you help as I don’t know what am doing wrong. Thanks!
Identity No.Identity % Identity
Identity 1 3 2 65%
Identity 2 0 0 0%
Identity 3 0 0 0%
Identity 4 0 0%
Identity 5 2 0 50%
Identity 6 1 2 100%
Identity 7 2 0 50%
Identity 8 2 0 50%
Identity 9 2 0 0%
Identity 10 0 0 0%
Asanwa -
I don’t know how you are plotting this data, or how it relates to this chart type.
Thanks for the help!! This was really useful..
How do I get rid of the gaps between columns/bars to give the chart a clustered appearance though??
Thanks!
Daphne -
Select one of the columns, press Ctrl+1 (numeral one) to format it, and on the main view (Excel 2007/2010) or on the Options tab (Excel 2003/2002/2000/97), change the gap width to zero.
Thanks a million for writing on graphs.
I tried doing you way, however, i cant reduce the gap for each two just like yours. every column of mine has space in between. hope you can clarify.thanx!
Very instructive! I want do something even more complex: Expenses by category and subcategory, with the clustered columns representing the categories and the stacks representing the subcategories. Here’s the complication: the subcategories change for each category. For example, category Activities has subcategories Soccer, Baseball, Hiking, etc.;
category Charitable Donations has subcategories American Heart Assn, American Red Cross, Habitat for Humanity, etc.; category Dining Out has subcategories Pizza, Fast Food, Salads-Sandwiches, etc. In a perfect world I’d be able to put data labels on each segment of a stack.
I can do this easily with separate charts for each category and its subcategories, but one huge chart is the holy grail. Is this possible without spending hours?
You are AWESOME! Thank you so much for this development. You made me look like a genius :-)
Great tool, it worked perfect on the stcked columns but I need lines in my graph. How can I leave the lines without them being distorted. I tried smoothing the numbers out across each cell the lines are reading but it still is very wavy. Any help would be appreciated.
Thanks,
Bryan
is there an excel example that can be downloaded for the clustered bar chart?
Bryan -
What kind of lines? And how are you smoothing the numbers (I don’t know what you mean)?
Thank you!
Bryan followed up:
When I insert new columns to allow for the clustering it cancels out my
lines because it has rows that do not have data in them. I have to add
data to the columns that were inserted to allow the lines to show up.
The smoothing I am talking about is averaging the numbers in the new
columns, i.e. if I have 1000 and 2000 between two columns and have to
add 2 columns to create the new the stacked cluster then I have to fill
in data into the two blank columns added 1000, 1333, 1666, 2000 but
when the graph reads the line it is extremely wavy.
My response:
Right click the chart, choose the option to adjust the data, and click the Hidden and Empty Cells in the bottom left of the dialog. Choose the option to draw a line across the gap. This means you won’t have to manually interpolate the line across the gap.
Hi, I am fairly new to graphs using 2007. I know how to create a gap between each bar, BUT, how do I change the width of the bars themselves????? They come our really skinny and hard to see.
Thanks, Lea.
Hi Lea -
If you have set Gap Width to zero, but the bars are still thin and far apart, it’s probably because you have dates on the category axis. So for example, if you have one value each week, the bar will be as wide as one day, and there will be six blank spaces between the bar and the next bar. Change the axis type to text instead of date or automatic.
Thanks very much Peltier! I m wondering if you can make the charts in 3 D with Excel 2003 this way?
Joy -
3D charts limit the ways you can combine chart types and secondary axes. Which is a good thing, because they also are an ineffective way to try to display data.
Hi,
Is there a way to add a clustered column to the 2 already existing stacked columns?
Thanks!
Alex -
Sure, but what you need to do is add them to the expanded data. Keep in mind that a “clustered” column in this case is simply a stacked column with only one in the stack.
The first example shows how you might add another category (Soda) that has a total that’s not broken into Q1 and Q2. The second shows how you might add a third item (Benchmark) that’s not split into Q1 and Q2 to each cluster of items that is split (Budget and Actual).
hello. is there a way to show on the same graph a single bar as well as a stacked bar. . . for example I want to show single bar total of 100 units, along side it I want to show out of that 100….70 units used and 30 remaining and show the 30 on top of the 70 to equal the 100 bar …hope that made sense . . thx
Hi Jon, Did you describe anywhere a methode of having a vertical line (based on percentage data) on a horizontal stacked (100%) bar chart?
Thxs Ronald
This is awesome and it works perfectly for me… wow…. what a smart person
thanks
Reggee -
Check the comment right above yours. A single bar clustered next to a stack of bars is really just a stack of 1. Sounds like you want to use the single bar like a benchmark (or target), use the second example.
Jon,
Hi! It is 7:43 p.m., and I am so stuck!!! I am using excel 2003, and I have used a pivot table in order to create a stacked graph. I’m not sure how to include “space characters” in my raw data in order to add additional spaces/separators in my graph. Basically, I’m trying to make a stacked graph into a stacked and clustered graph by adding in blank spaces. Is there anyway that I can do this?
Thanks,
Naomi
Hi Naomi -
Unfortunately you can’t use a pivot table directly in this technique, nor can you turn a pivot chart into a clustered-stacked chart.
What you can do is copy the pivot table, then paste special-link on a new sheet (so if the pivot data changes, this data will adapt). Then follow this technique to make your chart.
Your example shows data on one axis representing dollars(budget). Is it possible to develop a clustered stacked chart whereby you might have dollars as one stacked column next to, say, a stacked column representing units sold with data data on a secondary vertical axis.