Clustered and Stacked Column and Bar Charts

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?”

Cluster-Stack Column Chart

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.

Data for Cluster-Stack Charts

Without any effort or thought we can easily create clustered column or bar charts from this data.

Clustered Column and Bar Charts

Stacked column and bar charts are just as easy.

Stacked Column and Bar Charts

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.

Data for Cluster-Stack Charts

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.

Cluster-Stack Column and Bar Charts - Step 1

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.

Cluster-Stack Column and Bar Charts - Step 2

Adjusting the Data

So that’s only stacked. Let’s adjust the data by inserting some rows.

Expanded Data for Cluster-Stack Charts

The stacks of columns/bars are now spread out. Not yet what we want.

Cluster-Stack Column and Bar Charts - Step 3

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.

Expanded Data for Cluster-Stack Charts

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.

Cluster-Stack Column and Bar Charts - Step 4

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.

Expanded Data for Cluster-Stack Charts

Again, we have to explicitly tell the chart about the updated data range. This is almost what we want.

Cluster-Stack Column and Bar Charts - Step 5

Reduce the gap between columns/bars to give the chart a clustered appearance: select one series of columns, press Ctrl+1 (numeral one) to open the formatting dialog, and in the first screen you see (“Series Options”) change the entry for Gap Width to zero. Color code the data series to make it clearer which data series are associated.

Cluster-Stack Column and Bar Charts - Step 6

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

Finalized Data for Cluster-Stack Charts

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, select the chart, and use paste special (Home tab of the ribbon > Paste dropdown > 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:

Paste Special Dialog

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

Cluster-Stack Column and Bar Charts - Step 7

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.

Cluster-Stack Column and Bar Charts - Step 8

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.

Cluster-Stack Column and Bar Charts - Step 9

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.

Cluster-Stack Column and Bar Charts - Step 10

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.

Cluster-Stack Column and Bar Charts - Step 11

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.

Cluster-Stack Column and Bar Charts - Step 12

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.

Cluster-Stack Column and Bar Charts - Step 13


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.

Cluster-Stack Column and Bar Charts - Step 14

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.

Cluster-Stack Column and Bar Charts - Step 15

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

Cluster-Stack Column and Bar Charts - Finished

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.

Data for Cluster-Stack Column Chart with Line

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.

Cluster-Stack Column Chart with Line

Adding a line to a cluster-stack bar chart is much more complicated, so I will not cover it here.

Clustered-Stacked Charts in Peltier Tech Charts for Excel 3.0

Peltier Tech Charts for Excel 3.0

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, is prone to error, and becomes tedious.

I have created Peltier Tech Charts for Excel 3.0 to create Clustered-Stacked Charts (and many other custom 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. Charts can be made using data in a wide variety of arrangements, in either vertical or horizontal orientation.

This is a commercial product, tested on thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.

Please visit the Peltier Tech Charts for Excel 3.0 page for more information.


Peltier Tech Charts for Excel


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

  2. Heather – The changes are minor, and overall, I think this protocol is easier.

  3. 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?

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

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

  6. Sorry – forgot to mention, there is a scale on either side.

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

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

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

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

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

  12. Sorry – this is a general question. When I put them all on the primary axis, my scale on the right hand side disappears.

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

  14. 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?

  15. 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??

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

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

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


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

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

  21. Susie –

    Do you have blank rows in the adjusted data, between data for the clusters?

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

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

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

    Dual Axis Charts

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

    Dual Axis Charts

    But this does not solve the cluttered axis issues in point 3.

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


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


  27. Jon

    Thanks for your efforts. Is it feasible to apply clustered columns with a Broken Y Axis?


  28. I’m also stuck on the “add the secondary category axis” step.

  29. Thank you so much! This helped me do EXACTLY what I needed to do.

  30. I was tearing my hair out when I found this.
    Great help.
    Thank you.

  31. 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?

  32. Ignore my last comment, just needed to include another row of data in my source data and now they’re all nice and tidy.

  33. For those struggling adding the secondary X axis, I had the same problem, see that’s what helped me.

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

  35. Hi Jon,

    Is it possible to graph the double-stacked bars (primary y-axis0 on the same chart as a line (secondary y-axis)?

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

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

  38. rebekah ann stoutenburg says:

    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?

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

  40. rebekah ann stoutenburg says:

    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!

  41. 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,

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

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

  44. Thank you, thank you, thank you!! These instructions are awesome.

  45. 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?

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

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

  48. 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?

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

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

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

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

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

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

  55. 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?

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

  57. 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?


  58. Scott –

    Have you offset the budget values down one row from the actual values?

  59. Yes. I recreated your spreadsheet and I inserted the extra rows and moved the Q1 and Q2 budgets down one row.

  60. I’m not able to convert my “print screen” image to a picture for you to link to help us out.

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


  62. 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?


  63. 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”.

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

  65. 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?

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


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

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


  69. This was excellent, thanks. Your grasp of every detail in Excel never fails to impress!

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

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

  72. 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”.

  73. Thanks for this article! It helped me a lot.

  74. Thanks! It worked!

    This is a great article. Kudos!

  75. 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%

  76. Asanwa –

    I don’t know how you are plotting this data, or how it relates to this chart type.

  77. 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??


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

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

  80. 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?

  81. You are AWESOME! Thank you so much for this development. You made me look like a genius :-)

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


  83. is there an excel example that can be downloaded for the clustered bar chart?

  84. Bryan –

    What kind of lines? And how are you smoothing the numbers (I don’t know what you mean)?

  85. Thank you!

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

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

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

  89. Thanks very much Peltier! I m wondering if you can make the charts in 3 D with Excel 2003 this way?

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

  91. Hi,

    Is there a way to add a clustered column to the 2 already existing stacked columns?


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

    Cluster-Stack Column Chart with Unstacked Category Cluster

    Cluster-Stack Column Chart with Unstacked Item in Each Cluster

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

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

  95. This is awesome and it works perfectly for me… wow…. what a smart person


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

  97. 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?



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

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

  100. Hans –

    You could develop a chart that has some bars on the first axis and other bars on the second axis, but you’re asking for trouble. No matter how clever you are indicating which bars go with which axes, people will be confused.

    It would be better to normalize the different classes of data, for example, % of target, or % of highest overall monthly value.

  101. Jon,
    I am stuck on my chart. It’s almost the way I want it, thanks to your tutorial, but not quite perfect yet. It seems the problem has to do with my “labels”, which are dates. I cannot get the labels to line up underneath the columns. And it would be really nice is the last label/date, 1/31/12, would display along the axis. Can you help?? Thanks in advance!

    Here’s a picture of my chart and my data:

  102. I want to know whether i can custom a label in column chart. i mean if my chart label is having 80000, 70000, 65000…. and i want a ratio which i have in another column to appear at the top of each bar where label appears. I simply mean cud i add another row or column as label??

  103. Jon, do you have any tutorials that combine clustered columns with stacked graphs – or is that not possible? Thanks, DL.

  104. David –

    If what you mean is, you want a single bar clustered next to a stacked bar, well, consider a single bar is a stack that is one bar tall.

    Consider the data I started this exercise with. Confine yourself to columns A:D (delete or ignore column E), and construct the chart.

  105. Cathryn Lambourne says:

    Hi Jon

    Thanks for the details of how to build the stacked cluster chart. It worked well but I was having trouble with getting the category labels to line up properly on the x axis. In desperation I tried inserting them in my expanded data table in the first column. I merged the category label across the stacked expanded data for that category, then added it as a horizontal (category) axis in the ‘select data’ box and hey presto it inserted my category labels along the bottom.

    I’m so pleased with myself I wanted to share it with you. Sorry if you had already come across this shortcut.

  106. Jon,
    Thanks so much. I need to add a data table under this chart, is that possible?

  107. These in-chart data tables are usually more trouble than they’re worth, since you can’t omit some of the chart data nor apply much formatting. How about making a table in a range adjacent to the chart, and showing the range and chart together?

  108. Jon,

    Thanks for another great tutorial. I’m stuck on trying to make what I thought was an easy change. I have one set of columns that contain three stacks and the neighboring column is one value. I have no problem making the graph as above with the three-stack on the left and the single-value column on the right. However, I’ve been trying and trying to switch them (single-value column on the left), but it doesn’t seem to want to stay there.

    Any suggestions?


  109. Hi Jon,

    I want to thank you for your great contribution in my data analysis specialy for graphing.
    I have one problem. How can I insert error bars on stacked clustered charts using 2007 excel.


  110. I did all the steps and this works great. My only problem is the color coding. When I click on a bar to change the color, it doesn’t let me manually change the colors for every bar in a series at once but rather one at a time which would take forever.

    In your example, this would be like needing to change the color on each of the Q1 Actual bar segments manually instead of being able to change them all at once. Is there a way around this? I have about 30 different bars so it would take a lot of time to do this. I am using Excel 2007. Thanks!

  111. Vik –

    Each individual series (that is, each series which has a name in the legend) has to be formatted differently than every other individual series (that has a name in the legend). If you select one of these series, and use the paint can tool to fill the first series, you can then select each additional series and use the F4 function key to repeat this formatting on the new selection. Repeat with Select-F4 as needed.

  112. John Niedermeier says:

    Is there an easy way to produce a stacked bar chart with counts of occurence on the y-axis and Fiscal Week (FW) on the x-axis? The problem is that most of the FW’s have no occurences, but I want all of the FW’s represented so that the data is spaced as they occur throughout the year. If it matters, I am trying to do it from a pivot table.

  113. John –

    You can change a field’s settings to show items with no data. This would preserve the FWs with no occurrences, with blanks in the values field.

  114. Adam Tomlinson says:

    Hi John,

    This tutorial looks great and will do exactly what I need, but I have one problem that I cannot seem to resolve. When trying to add labels when I copy the 2 columns and then paste into my chart it comes up with amessage saying “Maximum number of series in a chart is 255” and it adds a whole lot of blank series??

    Any thoughts as to what I could be doing wrong?

    Many Thanks

  115. Adam –

    Did you select the entire columns? Just select the two-column data range, and try again.

  116. Adam Tomlinson says:

    Hi John,

    The error seem to come when I selected the option for replace the series names, when I don’t it seems to work… Not sure why but happy it works now

    Thanks again.

  117. Adam –

    I’ve added a screen shot of the Paste Special dialog, so it’s clear what settings should be selected.

  118. Regarding Error Bar not showing up issue:

    I successfullly created the plot, and tried to put error bars on the columns, and the error bars wouldn’t show up. Originally the data for the error bars was coming from elsewhere in my sheet.

    The FIX to get the error bars to show up was to organize the error bar data in a new column with rows matching the original data rows – i.e. data in A2 & A4, put the error bar data matching this data in say B2 & B4. Then select the whole range – i.e. data range =A1:A5, matching error bar range =B1:B5.

    Hope that makes sense. Would be better with a picture, but I just don’t have time to generate one.

  119. Z –

    Thanks for the clarification. I always find it less problematic if I put all of my data in adjacent ranges. I forget to mention this, because I take it for granted, but it’s not obvious to the typical user.

  120. Hi Jon,
    this short tutorial is really helpful for me.
    I am trying to make plan vs actual chart, the plan chart is consist 3 data which will be shown in stacked chart and then compared to actual in clustered.

    thank you.

  121. Hi Jon,

    I’m new to VBA and Excel Macros.

    I wanted to know whether what I need (listed below) is possible without invoking Macros. This is a project management tracker we are creating.

    1.) I want to follow completion of 3 activities. A, B and C. and the status can be Red, Amber or Green depending on completion status.
    2.) ‘A’ activity will be tracked for 50 cities in a stacked bar configuration, with each city status being Red, Amber or Green. Activities ‘B’ an d’C’ will also be tracked similarly (stacked) but all three are a cluster.
    3.) The next cluster of 3 will be for a second province with a further 50 cities.

    I hope this is clear. I just need to know whether this is possible without VB. If it is, I could use some pointers.

  122. First of all, thank you again for the handy tutorial on this topic. Once I created the clustered column chart, how can I expand the range? In other words, I have the chart looking exactly how I want it now (I created it last year, following your tutorial), and want to add the last year.

    If I ‘select data’ it tells me that the data is too complex to display, and if I select a new range it will overwrite everything – and if I do that, I get a complete mess.

    It would be great if I did not need to redo all the formatting on the chart, just be able to add the last 4 quarters. Alternatively, if I know that it is not possible, I won’t spend any more time on it.

    thx a lot!

  123. Well, I just figured it out – it seems that posting on this board helped my inspiration :). I just had to go into each data series separately and change the range – selecting the whole range produced strange results.

  124. Silvia –

    I like when smart people figure out the answers by themselves and then post it for everyone to see. Saves me the effort of saying the exact same thing.

    One thing that might help is the small utility I discuss in How to Edit Series Formulas. If a bunch of series in the chart now end in row 6 and you need them to extend to row 10 (for example), the program lets you edit all series formulas at once to make this change.

  125. Jon…

    When I try to add that blank row at the beginning and end of the data block to provide a little separation between the first column and the y axis and the last column and the right edge of the graph, (my equivalents of) the Q1 and Q2 Budget series “uncluster” and get stacked back on top of the Actual series. I don’t know if this matters, but I associated the Budget series with the secondary axis before attempting to insert those final blank rows.

    While I’m “stuck” with the technical problem noted above, perhaps I’m headed down the wrong road anyhow. I see where you have advised against using a secondary axis for these types of charts because the user will have a hard time interpreting them. In my case, I’m wanting a chart that will show number of wildfires by year and number of acres burned by year, for a 10 year period, but further broken down (hence the stacked bars) by wildfires’ subclassification as either a “large” or “small” incident. Since acres burned per year typically sum to hundreds of thousands, while the count of fires is in the mere thousands, I had to associate the count of fires with the primary axis and the burned acres with the secondary axis. Your tutorial is helping me compose that chart. But… if you have a recommendation for a different style of chart to better convey this blend of information, I would appreciate your advice.

    And, like so many others, I’d like to thank you for your excellent website, your technical expertise, and your willingness to share.

  126. Jon – thanks for that reference, great article. For reference, part of my previous trouble with editing the series range was due exactly to the chart having been copied from a different file, which was even in a different directory (which is one of the cases you discuss in the article).

    Thus, the path was very long, and the issue was that the arrows did not work in advancing the cursor, but introduced cell designations. The final workaround was to go directly and select the range I wanted, while the original series range was highlighted by default.

    I will check out the utility you mention, and also dynamic charts – concept I hadn’t heard before.

  127. Steve – Why do you want to present both the number of fires and acreage in the same graph? It seems too much, especially if you break down the fires by sizes.

    Anyway – one work around would be to normalize your acreage to the number of fires, so you can use a single axis, and then include in the heading, or legend, or just a text box, the normalization factor.

    In other words, let’s say you have 5,000 fires and 300,000 acres in year 1, you could scale the acreage by dividing to 60 all the acreage numbers. Then in year 1 the two bars will have equal height, and then in future years they’ll be similar in size, so you can use a single axis.

    This way you are also conveying more information – and this in itself is a reason to have these two entities next to each other, rather than in separate graphs. Right away people could see that a rough order of magnitude for the average number of acres per fire was 60 in year 1 (in the example above). Also, they could get a feel for how did the problem evolve over the 10 years. Of course, you’d need to specify the normalization factor in the legend.

  128. Hi Jon-

    Great instructions! I’m having one problem. I’m have showing data for 16 dates…each date includes 3 clusted bars, each stacked with 2 pieces of data. My bottom (secondary) axis contains the dates. After I complete everything, most of the dates are not aligned with the center bar of each cluster. The date on the far left is way to the left of the three bars. The date slightly “moves” to the right with each subsequent cluster. So it looks correct in the middle clusters. But then it looks too far to the right with the right clusters. Any thoughts?


  129. Adam –

    First, make sure the axis with dates is formatted as a text axis (not a date axis or the default).

    Then go up to the subheading that I’ve just inserted, “Important – Axis Label Alignment”, and check the positioning of the crossing axes.

  130. Hi Jon…I am trying to make a stacked bar chart..where there is only one bar for each month and then the month is divided up by categories (different colors)..
    for example, I have 120 orders in the month of this 80 are on-time; 30 are late and 10 are shipped wrong..then I would like to be able to have this type of bar chart for the entire year..much the same as these categories and probably more ..
    I am unsure how to format the data too..thanks so much for your help!

  131. Thank you for your technique

  132. Awesome how-to… quite a complex topic but it worked in first try! Thanks a lot!

  133. Thanks for the great description! Worked well with Excel 2010

  134. Just wanted to say that this was an incredibly helpful find – thanks very much!

  135. Charlotte Pijnenburg says:

    Hi there,

    Thank you so much for this tutorial, it has helped me big time.
    I just can’t figure out how to switch the 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 in Excel 2010.
    I hope you can help me with this, I really don’t get how to do it.

    It would be awesome if you can send me your answer by email.

    Thank you so much!

  136. Tania Maddigan says:

    Thanks for this fantastic information, which I used to create some wonderful graphs. Since setting this up, I have moved from Excel 2007 to Excel 2010 and the graphs no longer look so wonderful. Any suggestions on what I need to correct. Thanks in advance

  137. Thank you. I succeded.

  138. Lostbutfound says:

    Thanks SO MUCH for this amazing information :) Definitely bookmarking your page for future reference! Really, thanks a million!

  139. The Paste Special in Office Mac looks very different and doesn’t have the options in the tutorial. Would you suggest any other approach?

  140. I’ll go against the steam here and say the instructions are poor. Especially the bit that says “Now add the secondary category axis, which is secondary horizontal in 2007 column charts and secondary vertical in 2007 bar charts”. The rest after that may also be rubbish, but that’s where I’m stuck anyhow

  141. Oak –

    Thanks for the constructive criticism. I’ve reworded the section you had problems comprehending and indicated exactly the commands to use.

  142. hi Jon,
    quick question – i have had success using your method as desribed. I was wondering if there were any trouble shooting points you could add if i am trying to do the same thing, but have some negative values included in my data set?
    thanks, Amber

  143. Ps – much appreciate the post, made life a lot easier!

  144. Amber –

    When you stack values, it’s very difficult to represent negative values. The top of the stack doesn’t represent the total, because the negative values have not been deducted. The total height of the stack is even more misleading because it includes the absolute value of the negative values. If the negatives are important to the analysis, you should consider alternative graph types.

  145. Hi Jon,
    fair enough. What about if i only use negative data in isolation (i.e. not stacked -one column to itself) and then have stacked data in another column? e.g. have negative profit in one column by itself and then positive revenues and positive Cash flows stacked in the second column?
    No worries if not possible, just thought i would ask.
    thank you, amber

  146. Hey Jon, thanks for the tutorial. I was painful at first but once I got through it, I’m finding it particularly useful. I used it to show various vendor product lifecycles and how our company aligns our internal adoption and support to the vendor. Having completed all of this, I’m left with one last desire: To add one extra line denoting the current date.

    Alas, you mentioned that “Adding a line to a cluster-stack bar chart is much more complicated, so I will not cover it here.” Perhaps you could just give me a hint or an alternate tutorial that I could use to figure it out?

    Jon Miller

  147. Hi Jon,
    I would like to know more on the header “Important – Axis Label Alignment”. It seems that i am unable to place the crossing axis positioned between tick marks. I have tried changing the number of categories between the tick marks but in vain. Could you share some light on my screenshot?

  148. Cullen –

    You have nine clusters in the chart, but only eight months on the axis. Extend the X and Y range for the axis series by one cell. Then format the top horizontal axis so the value axis crosses on (not between) tick marks.

  149. Hi,
    I am trying to generate a cluster-stack chart where one stack uses one axis, and the other stack uses a secondary axis (scale issue — i.e. have large spend values by category, but small recoveries by these same spend categories). How can I accomplish this?


  150. I am stuck on the part that says to select F1:F4 while holding CTRL and select A1:A4 so both are highlighted. When I try to do that, Excel tells me “That command can not be used on multiples sections”. I am sooo close but I have no labels on the bottom axis. Any advice on what I am doing wrong?

  151. Select one of the ranges, hold Ctrl while selecting the other. Even if you don’t do this, the error you mention happens after another action, perhaps when you copy the range. If the two columnar ranges are not the same number of rows or don’t start in the same row, you will see that error.

  152. Thank you SO MUCH for this post! It is one of the best excel helps I’ve ever seen – so easy to follow and you’re explaining something so difficult! You rock.

  153. Hi, thanks a ton for this guide. It worked perfectly. There were some points where I found it a little difficult to follow, but I think it’s mostly because of the new version of excel. Best regards!

  154. Lenny Zimmermann says:

    Great stuff! Thank you for making it available.

    I think this is the kind of chart I should be using for something I’m working on, but there are parts of it that I’m either not sure how to make it fit or where the date comes out very… asymmetrical. What I would like to be able to do is create a chart similar to the following:

    Chromosome Map

    I have data that looks like this:
    Chromosome | Ancestor | Start | End
    1 | (Length) | 1 | 100
    1 | A | 1 | 30
    1 | B | 20 | 40
    1 | B | 70 | 90

    2 | (Length) | 1 | 90
    2 | B | 10 | 50
    2 | B | 70 | 80
    2 | C | 30 | 50

    And so on. So it seems to me that the cluster here is the Chromosome number (22 clusters total in the real data) and the stacks would be a Reference Length or Ancestor name. The problem I have is how to reference the positioning information. I would need to see if I could get the chart to plot just a, say, gray line for the reference length for the first data segment in the cluster. For the second (1.A, if you will) I would need it to make a red bar from out to 30, then the problem child for B shows up where I would need something like a grey bar for 0-20, blue for 20-40, back to grey for 40-70 and then back to blue for 70-90. So the first subset (1.Length) only really has 1 data point, the second (1.A) has 2, and the third (1.B) has, essentially, 4 (assuming I have to work out all of the appropriate lengths.)

    How do I represent that data when the columns for it are not symmetrical like all of the rest of the examples above? Or am I just looking at doing this completely the wrong way?

    Lenny Zimmermann

  155. Hi Lenny –

    Looks like an interesting application.

    You have 23 categories (22 plus X). What makes the data tricky is that you will need multiple data columns and chart series for the gray stretches, and also for the colored stretches. Probably it’s easiest if I build part of it for you. This table shows my eyeballed data for the first six chromosomes, normalized so the first chromosome is 100 units long. Notice the upper member of each pair has 11 columns and the lower member has 9.

    Chromosome Data - First Cut

    When the data is spread out for charting, it looks like this.

    Chromosome Chart Source Data

    And here’s the chart, with all the bars formatted appropriately.

    Chromosome Map

  156. Lenny Zimmermann says:

    Interesting…. I think I’ll need to look at that more closely to get my brain wrapped around it, but that sure looks right. Very awesome! I would note for most genetic genealogists it would still only be 22 since X is a bit… odd, to work on matching for genetic genealogy purposes, and the real values are a LOT bigger (chromosome 1 ends at 247,093,448, chromosome 2 at 242,697,433 and so on) but it shouldn’t be too many gaps for each data set at least, so I do have to figure out all of the differences to set the sizes of those gaps.Thanks!

  157. Larry –
    I was just making up numbers based on inexact eyeball digitization. But whether the data is normalized to 100 or to 247,000,000, the approach is the same.

  158. Hello Jon,

    How do you do it when you want to use the clustered-stacked chart with the y axis that is on a log scale?
    The closest data to the x axis disappear… It just shifts the next data up.
    Could you please let me know how to sort this out?
    Many thanks.


  159. It makes no sense to use a log scale with a stacked chart. How are you going to compare the values of bars that are offset from each other?

  160. Hello Jon,

    I have realised it.
    Is there then a practical way I can combine the clustered-stacked chart with your practical panel chart?
    When selecting the data, Excel doesn’t display (keep) the range of existing cells but says that the whole range needs to be selected again with the new one.
    It’s a bit dodgy…
    Any idea please?
    Many thanks.

    P.S.: Your website is tremendous.

  161. This is brilliant, Jon. Thank you so much! My supervisor had given me a weird excel list to convert to a chart and I was struggling (typical Intern style), till I stumbled upon this instructions. Your comment about the merged axis to give it a cluttered X axis was exactly what I was looking for.

    Bookmarked! :)


  162. Hi Jon – This is great stuff and exactly what I was looking for. I could follow your steps and successful complete it. Further to this, I need to add % share each of the stacks. For example: Coffee Budget stack should show whats Q1 and Q2 % share on the bar, and Coffee Actual stack should show Q1 and Q2 % share on the bar. Is this possible?

  163. Thank you so much, this is perfect. A mystery solved.

  164. Hello Jon.

    I am stuck at the point where you reverse the secondary vertical axis to get the labels from the top to the bottom. I was going great guns till then & now I’m going mad again.

  165. Alesha –

    Are you unable to reverse the axis? What is the problem?

  166. Sorry Jon, I wasn’t very clear was I? I sent you an email but just in case you didn’t receive it…
    Under the section ‘Manipulating the Axes’ – I cannot find how to reverse the secondary axis on MS10. When I started with just the primary it was as simple as clicking select data & then clicking the box that says ‘switch row/column’. But since adding the secondary axis that option is greyed out. Until I work out this bit I can move on to the labels and finish the task.

  167. Very helpful! Took me a while to figure out how to set the “X” axis to “TEXT” but once I fixed that, the result was beautiful!

  168. In the above stacked column and bar chart you have showed coffee,tea & milk in different bars but Q1 actual and Q1 budget in single bar. If i want to for coffee in single bar Q1 actual or three month individual actual and below actual a single bar presenting Q1 budgeted or three month individual budget. same for tea & milk. how to prepare that can you help me for this.

  169. Harihar –
    I don’t understand. Don’t you want to put the three-month average in the data table instead of the Q1 values?

  170. Thank you very much for posting .It was very helpful to me

  171. I found it difficult about labeling bt I done it in my own way.that option for paste special was not tried to do it by other way.Thank you.

  172. Thanks for sharing this protocol. I’d like to add more bars to my clustered-stacked bar chart (actual, budget, optimistic forecast and pessimistic forecast). Would you have a protocol to add more stacked bars to the chart?

  173. A chart with more bars has a larger data range which has been spread out further to accommodate the clustering and stacking together. I shudder to think about adding new bars to an existing chart, which entails mucking with the original and expanded data ranges. I would set up a new larger range and make a new chart.

  174. Does your utility enable putting one or more lines on the cluster-stack chart? Is it easy?

  175. Greg –

    There’s a protocol you need to follow, but the utility already places a hidden line on the chart. The first line you need can take over this line with its values (and formatting so it’s visible), and additional lines can be readily added.

  176. Hi Jon,

    I was wondering if there is a way to include text labels on both the horizontal and vertical axes. I have tried applying your previous tutorial, “Text Labels on a Horizontal Bar Chart in Excel”, but the method doesn’t seem to work with the clustered and stacked bar chart. I would like to display months on the vertical axis, “Jun-13, etc.” and names along the horizontal axis. I am currently restricted to excel 2003.

    Thank you for your help,


  177. Everett –
    The cluster stack tutorial makes use of primary and secondary axes to handle the plotted bars and the axis labels at the base of the bars. Thus you can’t add another series on a new set of axes, since Excel only has primary and secondary.
    You could visit a much older tutorial that adds an XY series, with points wherever you want labels, and custom data labels on the points: Vertical Category Axis. (Never mind “Vertical” in the title, since you can put XY points wherever you want.)

  178. There is a simpler way to make a clustered bar chart, and a more flexible way. First, the data must appear in the usual format of a pivot table. In this case it would look like this:

    Quarter … Type … Beverage … Data
    Q1 … Budget … Coffee … 10
    Q1 … Budget … Tea … 20
    Q1 … Budget … Milk … 30

    and so on, for Q1-actual, then Q2-Budget, and Q2-Actual

    Next, make a pivot chart. You are now free to move the data attributes about to get Type within Quarter, or Quarter within Type, and so. Next, add blank data above and below to create the clustering, and your done with a little more (obvious) tweaking.

    This method is nice because you can even add a third data attribute, such as “Office”, then have three levels along the x-axis.

  179. Hi Jon,

    I’m really struggling to complete the following step. Every comment above asking the same question seems to have answered itself, so I’m still lost!

    “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.”

    Can you help?


  180. Select the axis. Press Ctrl+1 (numeral one) to open the Format Axis dialog. Find and check the box that says Plot in reverse Order, then find the Axis Crosses at options, and select Automatic.

  181. Jon-

    My boss asked me to make a chart just like this and this saved me, thanks. My question about the alignment of the bars, they seem to be creeping to the right slowly. Not sure if it has anything to do with it, but my data runs horizontal because I couldn’t Transpose AND Past Link. Here is a pic so you can understand better:

    Data layout:

    Chart moving right:


  182. Hi Mike –
    That category creep usually happens because you didn’t include enough blank columns at the right edge of the data. Click on one of the bars and look at the highlighted spreadsheet region corresponding to its data. Extend the highlight further to the right by dragging one of the corners.

  183. Hello John,

    I had done this graph earlier but now I am in requirement to add percentage as line in the stacked bar chart.
    Though it works, it does not look as required. The line with percentage will appear too high of stacked bars.
    This happens because I use numbers for stacked bars and percentage for line. If I convert the percentage to numbers or other way then the chart make sense.
    Is there any way out to prepare chart using numbers for bars and percentage showing the line?

  184. Is there a way to add two lines in to a clustered stack column graph? I’ve managed to add one line in following your instructions but it won’t let me add another one? Thanks for your help!

  185. Hello!
    I want to create a subdivided bar graph which shows the whole percentage of a specific group and then divided by some characteristics. for instance, Percentage of Non-literate in the population with percentage of male and female.

  186. Catherine –
    Your percentages are the Y values. What are the categories?
    It seems to me that the best way to show this data would be a clustered bar chart (or a line chart, but some people are so fussy about line charts with categorical axes) with three series: % Literate Overall, % Literate Male, % Literate Female. With the bars side by side it would be easy to compare the percentages. If you stack things up, comparisons become much too difficult.

    In fact, it might be better with horizontal bars:

  187. Mala Kasthurirangan says:

    Thanks so much! this was perfect and the explanations and guidance were so good, thanks.

  188. Jon,
    How do I reduce the gap between columns/bars to give the chart a clustered appearance? I’m using Excel 2010.

  189. Select the series and press Ctrl+1 to open the Format dialog. Right in the first screen of the dialog is a slider for adjusting gap width.

  190. Thanks!

  191. When I pick to show the chart format with a data table below the chart, I need to show totals for the table columns and rows. Can totals show on the table as it shows in the Pivot Table?

  192. Those data tables below the chart are rudimentary and inflexible. They almost never do what I want, so I almost never use them. What I do is copy the data to a range of cells below the chart, lay it out the way I want, including the order I want, the formatting I want, totals if I want. Then just show this table along with the chart.

  193. Excellent information, helped me a lot. However, I am having one problem. I’ve added the line to my graph but the line isn’t visible. If I add it as a line with markers I can see the markers but still can’t see the line. I’ve checked the transparancy, color and line size options. Anyone have any suggestions on why the actual line isn’t visible?

  194. Are there empty cells in the data range for the line you’ve added? insert #N/A into these cells, and the line should appear.

  195. Jon – you are the best. I never thought of that. I had added the line information next to the stacked bar information, and did leave the spaces. Once I added the #N/A the line showed up. Thanks again!

  196. Hi Jon,

    Great instructions – very thorough.

    I’m running into an issue with my secondary axis and while adding labels. When I follow this instruction –> “If you’re making a stacked-clustered column chart, convert this new series to a line chart type.” The entire chart turns into a line chart and just not the new axis I added. I can still continue with the next steps but when I change the chart type to stack cluster type then the labels (or secondary axis) disappear. What am I doing wrong?

    Office version 2007

    Cell layout (8 series representing data for 4 quarters)

    V1      V2     V3      V4       V5      V6      V7      V8
    data    data   data    data
                                    data   data   data     data
    data    data   data    data
                                    data   data   data     data
    data    data   data    data
                                    data   data   data     data
    data    data   data    data
                                    data   data   data     data

    Appreciate your help in advance!

  197. You have to select only the one series, then change the chart type. If you select other parts of the chart, the whole chart gets converted.

  198. Hi Jon,
    My data has two diferentes units (percentage and USD) and Im having a hard time manipulating the Axes. Please could you help me with this matter??? If you need further information please let me know


  199. Thanks Jon for your quick response but I forgot to mention earlier that I am only selecting the new series that I added using Copy and Paste Special. There’s no way for me to show you the screenshot here so you’ll just need to trust me on that.

    Is your instruction based on Office 2007 or 2010/2013? I’m wondering if the issue is with the version that I’m using even though I don’t think it should be.

  200. LuzMa –

    It is very difficult to have one stack of bars in one set of units and another stack in some other units. It’s hard to construct, and it’s hard to interpret.

    Could you convert both into some kind of ratio?

  201. Scorele –

    I don’t know why it’s misbehaving. These instructions were written using Excel 2007 (I believe) but the technique works in Excel 97 through 2013 (though in older versions it may be hard to find the commands).

    If you try to do anything to the chart right after copying and pasting data, weird things may happen. I usually double click in a cell to turn off the moving border that indicates a copied range, and then format the chart. I don’t know if this is the problem, but it’s worth trying.

  202. Hi Jon,
    The directions on how to create the clustered stacked bar chart work great. I’m only struggling with one part, which is how to narrow the gap between the sets of bars. How do I narrow the space between the left hand axis and the first stacked bar and between each subsequent month of data? I am on Excel 2010 and tried finding the answer in the thread but I’m still struggling.

    Here is the file I’m working with:

    Thank you so much for all your help,

  203. Eric –
    The gaps at the ends of the can be shrunk in half by continuing the exercise, following the instructions for “Adding the Labels”, “Manipulating the Axes”, and “Important – Axis Label Alignment”.
    The gaps between bars are non-negotiable, and must remain one bar wide.

  204. Can the utility be used with data in a pivot table?
    I am working with a sheet linked to an access database and am limited in how the source data can be manipulated.

  205. In principle the utility can use a pivot table for a chart’s source data. I need to check on a detail or two though, before I can state for certain.

  206. Hi Jon,
    I was able to produce the stacked chart, however I need to add a line graph on the second (right hand axis). It doesn’t work when I try it. Is there any way to do this?

    Thanks so much for your help,

  207. See the range with the category labels (coffee, tea, milk in the example) and the zeros? These were used for a line chart series. Put your values in place of the zeros. You will have to select the series (select another series and use the up arrow until the hidden markers are selected) and format it so it’s visible. Then use the command on the ribbon to add the secondary value axis.

  208. Hi Jon,
    The line graph that I need to display will not have comparable units with the bars on the left hand axis so it can’t be placed on the left hand axis. The left hand axis will be in millions but the right hand axis with the line graphs need to be in % (0-100%). I’m sorry….I tried to follow the directions very closely but I still don’t see how this is possible. Is it possible at all to have a clustered stack column chart (on the left hand axis) with several line graphs on the right hand axis?

    Thank you again for your help. I really appreciate it.

  209. Eric –
    All the bars are plotted on the primary axes, which includes the vertical axis on the left and a hidden axis at the top of the chart.
    The horizontal axis labels you see come from the line chart series with the zero values. This series is plotted on the secondary axis, which includes the secondary horizontal axis, at the bottom of the chart. There is no secondary vertical axis, which is why you have to add one.
    For one line series, all you need to do is change the zeros to your values. If you have more, you need to add the data to the chart, as additional line series on the secondary axis, the same way that the first was added.

  210. Hi Jon,
    I finally got the line to show up on the second axis however I’m stuck on the spacing of the data values. It seems no matter where I place them, the line graph can’t show the data if there are 2-3 empty cells in between each number. It only displays a contigous line if the values are in consecutive cells. Can you please check this file and see what I’m doing wrong? Also, I can’t figure out how to take the tick marks off the top line and place an axis on the bottom.


  211. Thank you so much!! This is very useful!

  212. Thanks for the instructions. I am running Excel 2010. Are their directions to do this with a pivot table as I would like the information to update as I refesh the data.

  213. Erin –

    You can’t spread out a pivot table the way I did with a regular range. What you can do is link a range to the pivot data, and spread out this range. The cells with values link to the pivot table cells, so when the pivot table updates, the spread out range also updates, so the chart will update. Note that the chart only knows about the cells that were filled when you built the chart, so if the pivot table expands in size, the chart will not reflect this.

  214. Thanks Mate,

    I thought it would have been easier using built in functionality and then after I googled a workaround and found yours, It was an extremely daunting prospect. However, I got stuck into it (had no choice) and it came together better than I expected. It took a couple of hours, but considering I didn’t have an instructor to immediately fall back on, it’s the sign of a well written instruction that I could get my desired outcome on my own. Now that I have the template, I can easily modify the content for up issue.

    Thanks Again,

  215. THANK YOU SO MUCH!! You are a genious!!!! :)

  216. Greg Lichtenberger says:

    This article was very good and helped me tremendously.



  217. Jon, great information as always. I’m trying to create a stacked bar chart to show the value add and non-value add in a process. I want to show three colors based on the step time in a process and the category of value it represents (customer value add, business (non) value add, and non value add). There could be anywhere from 2 steps to 150 or more with the 3 categories spread throughout. So, I have 3 pieces of information: step number, step time in seconds, and value category. Can this be done? (see direct email for picture)

  218. Hi.
    I cannot figure out how to do Paste Special. When I click “Paste Special,” it shows a window that is different from your screenshot.
    I’m using 2007 version, and the Paste Special screen is divided into two big parts–Paste options and Operation options.
    Under Paste it has All, Formulas, Values, Formats, Comments, Validation….
    Under Operation it has None, Add, Subtract, Multiply, Divide as options.
    I tried selecting random options and pasting, but nothing has really worked for me….
    I wonder why I would get such a different window/screen for the same Paste Special Menu

  219. Nicole –
    Select the chart first, then do paste special. You are describing the dialog for pasting what was copied into worksheet cells.

  220. Thanks – great article. Thanks for your help, which is much appreciated.

  221. This piece of advice has been EXCELLENT! it was really easy to follow and worked! It is the only advice I have used that has been simple and worked at the same time

  222. This was a great article! Thank you for taking the time to document all the steps. I could not find anything else as comprehensive and detailed as your walkthrough. You took a complex process and explained every step. Thanks again!

  223. Thank you for the instructions. It is very helpful but could have been a bit more clear
    I think it would have been much more helpful with screenshots of the steps.
    For example when you say “We need to reverse the bar chart’s secondary vertical axis”. I have no idea how it’s done. I looked for the “plot in reverse order” you mentioned in the comments but I could not find it (Excel 2007)
    Also, not sure what you mean or how it’s done when you say “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.”
    Judging by the comments I am not the only one that is lost when trying to fix the axis labels.

    Anyway, appreciate you spending the time to put these instructions together.

  224. I need to create a clustered and stacked bar chart on one graph…but not in the way that you described on this page. I need two series to be stacked and then the other two series to be next to them in the clustered layout. When I go to change data series and try to do it that way, it changes all of the series to a clustered chart…not just the one that is highlighted. Any suggestions? Or do you need more info / clarity?



  225. Caitlin –

    As the article points out, you can’t mix Excel’s built-in clustered and stacked chart types. But you can follow the protocol in this article, where you have three stacks per cluster. The first stack has two bars stacked up, the second has only one, and the third also only has one. You arrange the data like this to get the chart below:

    Cluster of three stacks with two, one, and one bar per stack

    I thought it might make the data layout clearer if I rotated the chart and aligned it with the rows containing the data:

    Cluster of three stacks with two, one, and one bar per stack

  226. Is there a way to create a stacked column graph that shows the relationship of 2 values. The example above does not really show what I am after. I want to show numerator and denominator relationship in a stacked column. so for example if I have 80 surveys (denominator) and 55 of those surveys were listed as “very good” (numerator). So I want my stacked column to show my Y axis maximum of 80 and the lower 55 in a different color for my very good. When I look at the graphs above and when I try to show my data as a stacked column, it shows my Y axis value as 135 total.

  227. Becca –
    You need to plot 55 stacked with 25 (i.e., 80 minus 55).

  228. Hi Jon,

    Just used this to create a graph for work. Really good stuff! It’s a little confusing to read through the multiple instructions depending on Excel version and/or graph type, but overall I’m amazed at the technique. I will definitely be adding this to my Excel repertoire. Thanks so much for sharing!!

  229. First of all, this is a great article. It is so helpful and also explained in such a simple fashion. I was able to quickly create the chart using the instructions.

    I had a follow-up question…
    I am trying to add another data point to the chart, say Q3 Budget and Q3 Actual to the chart already created. I tried a few ways to add, but it somehow doesn’t work. Could you please help with the steps to follow to add another data point to an existing chart?

    Thanks a lot in advance

  230. Hi,
    Thank you for all.
    You’re my new hero :p
    This topic is wonderful.
    Almost one hour to make my first (big) clustered-stacked chart, but the result is amazing.

  231. If I wanted to use a similar technique using stacked lines, is there a way to have the lines skip over blank data points? The typical technique of using #N/A does not seem to work in this scenario. I would really appreciate any insight!

  232. Nevermind! Figured it out using your other blog entry. Shout out to that!

  233. Thanks Buddy

  234. I’m trying to determine the best way to produce an overlay bar/column graph that will help me transform a boring data dump into an infographic style dashboard initiative. I have been able to create 1-1 bar over bar, but am struggling to diversify the forward data. In my example:
    1 – assume 4 quarters (Q1-Q2-Q3-Q4), each represented separately to create the large background bars
    2 – in each quarter, assume 3 unique vendors (A-B-C) need to be represented on the graph (A-B-C does not generally comprise 100% of Qn data.
    3 – I would like the quarter data to stand side by side (contained) in front of the quarter data, rather than as a stacked bar, for labeling purposes.

    Is trial and error through spacing the only way to create this chart? Have you built any charts similar to this before?

  235. Chris –

    I’m not sure exactly what you’re looking for, but this might help.

    Here’s some dummy data, the quarterly chart for the primary axis, and the company chart for the secondary axis, with guesstimates for primary and secondary gap widths.

    Here is the sequence for making the chart. First, plot all the data, which starts on the primary axis, and set the primary axis gap width. Second, move the company data series to the secondary axis. Third, set the secondary axis gap width.

    Note that having overlapping data plotted on either axis will cause much confusion, and it’s not clear whether the three company’s data sum to the total quarterly data (which they don’t).

    A clustered-stacked chart might be a better way to show the data.

  236. I have been able to format a stacked cluster chart using your instructions. Unfortunately my horizantal axis does not display all of the values in range on my chart. Where can I upload or show you an image of what I have?

  237. Jim –
    Upload it to Dropbox, Google Drive, or another of the myriad file hosting services, and send me a link.

  238. Thanks for your help. Another question: Is there a way to create a trend line instead of stacked cluster using the data in the 3rd column of the stacked cluster. When I try to change the type to a line the line jumps up and down where there is no value recorded. Seems like there should be a way to smooth out the line or have it ignore the zero/no values. I do realize I will have to have two lines to represent the two values in the stacked column.

  239. Jose Luis Auces says:

    Thank you very much for this…

    I knew it could be done, but you helped me to find other ways of using charts in Excel..

    AWESOME !!!

  240. You could also just use two different clustered column bar graphs and overlay the two – making the background transparent. This allows you to link the graphs to pivots etc for easy updating.

  241. Many ways to skin a cat. I don’t like stacking up multiple charts with transparent backgrounds for at least two reasons. First, the alignment is tricky to maintain, since changing the data may cause the space needed to accommodate axis labels and the like to change. Second, you can’t select any charts behind the first one, so you have to keep sending charts to the back, then edit the one you need, then restore the order of the charts.

  242. Hi Jon – Like Jose and Ellen, my labels (equivalent of Coffee, Tea, Milk) are bunched up on the left. Cant get them to spread out, and couldn’t find a response to this problem. Thank you for getting me so close to finished! Bumping into this wall is holding me back from delighting my boss. Your help is appreciated.

  243. Kate –
    Away from work for a couple days. Did any of the remedies I may have shared with them help you? Did you try again, taking care to do each step in turn? If so, which step seems to have caused the problem?

  244. Hi Jon
    Thank you very much for this tutorial! It was easy for my boss to say “I want a graph like this” but not when I say the same thing to Excel. I was at the point of thinking how about just draw the graph by hand and scan it…

  245. Hi Jon,
    This is indeed a very helpful demonstration. One thing i still could not figure out although it seems ver simple thing. How
    Reduce the gap between columns/bars to give the chart a clustered appearance ? Can you please or anyone tell me how to do that.

  246. When you format a bar or column series, one of the properties you can adjust is Gap Width. It’s on the first tab/screen/pane that you encounter when the dialog/task pane opens. Set gap width equal to zero.

  247. Hi there Jon,

    when u mention “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”. how do u reduce the gap between the vertical stacked columns?


  248. Bala –

    Select one series of columns, then press Ctrl+1 (numeral one) to open the formatting dialog. In the first screen you see (“Series Options”) change the entry for Gap Width to zero.

  249. Your instructions are very frustrating. I’ve recreated your data, and when I create a column graph, the rows/columns are the reverse of your example. Fine, I’ve switched them. Now the category labels are in reverse order. You simply state: “So the vertical axis has to be formatted to make the categories go in reverse order.” HOW!?! The usual advice is to invert the vertical axis, but that inverts my chart as well. Setting the crossing point of the horizontal axis merely moves the x-axis back to the bottom, but the chart is still inverted. I only want to invert the category labels.

  250. You only have to fiddle with the axis of a horizontal bar chart, not a vertical column chart.

  251. Hello Jon. Thanks for the prompt response. I don’t have to, but my boss wants it, and therfore, I need to find a way to do it. We’re displaying the data table below the column chart, so he wants the left column of the data table (1,2,3,4,5 vertically) to show as the category labels in the same order to the left of the column chart. By default, they show up in reverse order (5,4,3,2,1 vertically) and I can’t see a way to invert the category labels. Do I set up a dummy label list?

  252. Yeah, data tables. In any but the simplest charts, it’s hard to get them the way you want, and the formatting is always a pain. If there is a hidden series in the chart (necessary for many of the funky effects my tutorials provide) it can’t be hidden from the data table. If you want to mess up a chart, add a data table.

    I suggest that people make a table in the range of cells adjacent to the chart. People can then see the values numerically, and you can arrange and format it however you like.

  253. Thank you very much, Jon!!

    Everything was good and I was really shocked that excel can do it, until I moved to the part: “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”.

    I really can’t do this!!)) I tick ‘Values in reverse order’ for both vertical axes, however my data turn around (I mean they go from the top to the bottom) but horizontal axes are switched over…

    I can suggest that the problem lies in the fact that I use monthly data… I tried to find solution in the comments and found that it can be impossible to have more detailed infotmatio than quaterly data

    I really need your help!!

  254. Artyom –

    When you reverse the axes, the scales reverse, and the crossing axes also switch. But you can format the axes with the reversed scales to change them back. In the setting for Horizontal axis crosses, switch one axis from automatic to maximum and the other from minimum to automatic.

  255. Brad Sinclair says:

    I’m looking for a way to represent tracking Scheduled Received/Actual Received drawings from a table into the chart. However, when the table gets populated with dates I want the bar chart to change color to green from yellow. This will show my client that we have received all documents (once green) or otherwise will remain yellow if we haven’t received anything.
    Any information would be appreciated.

  256. Brad –

    For each bar that changes color, you need two bars in the chart. Before you get the documents, the yellow bar plots the value and the green bar plots a zero; afterwards, the green bar plots the value. This can be managed with a simple formula in the worksheet:

    yellow value’s cell: =IF(C1,0,B2)
    green value’s cell: =IF(C1,B2,0)

    where C1 contains TRUE or FALSE depending upon receipt of the documents, and B2 contains the value to be plotted.

    This is an application of my tutorial Conditional Formatting in Excel Charts.

  257. Hey Jon, your site has one of the most advance charting tutorial I can find. Saw your comment on Lenny’s post on a chart which I am trying to create right now which I think is impossible.

    Screen shot :

    Vertical axes will be Company and Horizontal axes can be START Date or Duration. Objective is to create a chart that allow me to visualize for each company how long it takes to complete a product. I can ignore tha P1, P2, P3 for now because it is dynamic. Probably by assigning them a value such as P1 will be easier to plot.

    I believe to plot a chart successfully, it is just the knowledge on how to arrange the data or produce the data that Excel able to read and translate. Going to read through all the tutorials to get some ideas …

  258. Thank you so much! I was about to lose it when I stumbled across your post. you saved me a hell lot of time!

  259. I’m having difficulties following the steps to label my Axis. I get lost where the bottom horizontal axis changes from 1 -10 to rading Coffee Tea and Milk. Was hoping you could further clarify your instructions.

    Also might be interested in the Utility to do this as I will have to peform this type of chart multiple times. Clicking on the link at the beginning of this blog does nothing for me.

    Thanks Jon. This is a great resource!

  260. Could you elaborate much more on this?:

    “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.”

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


    Kind Regards,


  261. Hi,

    I have followed the procedure through and have managed to make the stacked columns work. However I need to add some lines at the top & this is where I have encountered a problem. I added the data in a coumn next to the original data as you suggested but if I add this data on the primary axis it only spreads the line across some of the x axis categories (as if it is using the hidden primary axis). If I use the seconary axis it extends the line correctly but adds another axis in the middle of my data & there seems no way to remove this other than removing the secondary axis data labels which I need to be able to show. Any suggestions?

  262. Hi
    I have a problem right at the begning

    When I try to copy the area as you said I get the message that the command can not be used on multiple selections.

    What am I doing wrong?

    Best regards Stefan from denmark.

  263. Hi

    I have a problem with copying

    When i try to do as you told I get the message that this command cannot be used on multiple sellections

    What am i doing wrong?

    I am using office 365

    Best regards Stefan

  264. Stefan –

    You can copy and paste multiple selections, but the multiple selection must look like a selected rectangle with unselected whole rows or columns.

    For example, any of these will work:

    Successful Multiple Selected Ranges

    Notice that the top left cell has to be selected, even if it is blank. The following will not work:

    Unsuccessful Multiple Selected Ranges

  265. Hi Jon,

    I really like the way you have described the procedure to come to great clustered and stacked column and bar charts. I really like ‘m! My question is, when I want to add an extra column to each cluster, how should I proceed? I’ve tried, but it is not so easy, or am I overlooking something? I work with Excel 2010, if that would matter.



  266. Jeroen –

    It’s hard to go back and add a new column to an existing chart. But when starting from scratch, you just need more data, and stagger it appropriately.

  267. I am unable to move forward in the Manipulating the Axes section.
    I’ve added the axis and successfully placed it on the secondary axis, but I can’t move forward at this direction: “Now add the secondary category axis, which is secondary horizontal in 2007 column charts.”
    I’m using Excel 2013.
    Note that when I placed it on the secondary axis, it disappeared (although I know it’s still in the range).

    Any help would be appreciated!


  268. Krista –

    I assume you successfully reached the stage where you had one horizontal axis and two vertical axes. When you add the secondary horizontal axis (in Excel 2013, use the plus icon floating beside the chart or Add Chart Element on the Chart Tools > Design tab), nothing should disappear, and the axis should appear at the top of the chart.

  269. super and nice thank you so much

  270. Thank you for this. You were a lifesaver!

  271. That took a bit of following because options only appear as stated if you pick the right element to highlight (duh, sounds daft but it is quite hard to work out). However, the result is excellent. Thanks for this terrific tutorial.

  272. You just saved my butt, my sanity, and the better part of my week! Thank you so much for this post, it was amazingly helpful!!!

  273. Excellent!. This is exactly what I needed for a current analysis. Worked well in Excel 2010 for me to create a stacked and clustered horizontal chart. Is there a way to set the bar width (wider)?


  274. Bert –

    Haqve you already set the gap width to zero? That makes adjacent bars touch each other.

  275. Yes I have. under Format Data series – Series options – Gap Width.
    I set the slider to 0%.

    Not sure how to add a screen shot of the dialog box like was done in this thread by you and Lenny Z have done.

  276. Hi Jon, love your website. I am trying to incorporate the AXIS but when I copy/past special I get the same old pop-up, not one that matches your chart pop-up. I am using Excel 2010.

    Thank you

  277. Julie –

    You don’t see the small Paste Special dialog like the one I showed? What have you selected before going to Paste Special? Is the chart selected?

  278. Thank you very much. This article is very useful to me ^^

  279. Hi Jon,

    Could you please suggest conditional chart as actual/budget variance or ratio analysis change in file, it should automatically change the colour of bulb or symbol beside the charts as warning.
    • Suggest ranges for “good”, “caution”, “warning”
    o it based on the targets we suggest, then what constitutes good ie equal or greater than xxx, etc
    • How do we formula drive these calculations to automatically give us a “green bulb” or “caution” or “warning”
    o if we don’t want to have to manually input the image every time we run this report

  280. KM –

    You should look at my post Conditional Formatting of Excel Charts

  281. Brilliant Stuff Jon. Thanks a lot!

  282. Thanks for this article! Having a problem though, the “Paste Special” option does not appear when I right click the chart. Just “Paste Options” with simple paste underneath. Any advice? I’m using Excel 2013. Thanks!

  283. Cecilia –

    You get toe Paste Special from the Home tab of the ribbon > Paste dropdown > Paste Special. I’ve updated to instructions above.

  284. Thanks John. This tip worked great for me. I created a perfect stacked and clustered graph. However, when I tried to change the Chart Layout (Chart Tools/Design/Chart Layouts) to Chart Layout 5 (with data table below graph), the data table has all of the extraneous data in it and it is formatted into each small column. I can delete some of the data to clean it up but if I delete the “axis” data, I lose my horizontal axis labels. I need a fix to format the table columns and to hide some of the extraneous data. Thanks.

  285. Jake –

    Yep, many of those chart layouts are awful, and data tables in charts are a feature guaranteed to make you cry. Data tables do not allow you to select what to display, and they do not allow for much formatting either. Set up a range near the chart, put in it the data you want to show, and format it to suit.

  286. Hi,

    How can i show Total on each stack bar, this is urgent please help.

  287. I’m trying to do this exact same thing but using a pivot table – is this easy to accomplish? I’m not sure how to generate the extra spaces to format the data, etc.

  288. Will –

    You can’t do it directly with a pivot table, because you can’t rearrange the pivot table into a sparse data layout like this chart requires. But you can set up a range nearby, and use formulas to link to the pivot table, so the data actually looks the same, and then arrange the linked data as this tutorial shows. Or you could simply copy the pivot table, and paste it elsewhere (or on top of itself) as values, and rearrange it however you like.

  289. Hi,

    Thanks, very useful.

  290. You have the coolest blog ever.

    I have figured the answer to my problem, but not why. It’s possible posting the problem will help another find the solution more quickly than I did, and also — perhaps you can explain wtf?

    I have a complicated graph using hybrids of what you’ve taught elsewhere, for starters 11 clusters of 4 bars each, labelled as per instructions above. That is, using a dummy axis with all values set to zero and a separate, associated column with labels.
    >Then I went to add two new sets of bars to the clusters (resulting in 11 clusters of 6 bars each) – purpose here was to set a “goal” with an error bar as you’ve showed elsewhere (actual revenue to contrast with forecast revenue).
    >Here’s what’s weird: adding these additional 2 columns of data representing the resulting additional 2 sets of bars, makes the dummy axis variable balloon. Why? (It goes from 11 values to 51)
    >Then when I went to look at the source data I notice the axis labels are involuntarily associated with the two newly added variables. I cannot erase that association. (That is, I can nominally erase it but functionally it remains). And clicking within the graph and adding a line to see wtf I realize the old “axis” series now thinks it includes the two additional ones. Why? I added these to the graph and not to that series. And on the data sheet it doesn’t show any association of “axis” with either new data column.

    The fix is that all column data must go into the chart’s data _before_ the axis dummy variable gets added: Why? Because it seems that adding data after the dummy axis is associated makes all subsequent data series be associated involuntarily with the dummy axis….

    I just don’t get this, am glad (after hours!) I’ve figured out the problem, but thought you might have insight?

    I also wanted to thank you for what you do: So so so cool. I’ve long hated excel because (I thought) it has zero graphing capability but … I think I’m wrong!

  291. >>>As these comments seem to be moderated I hope you will modify my above submission.
    It turns out the order the additional data columns are added is not the issue, what happened is that for some reason excel involuntarily added the additional columns to the secondary axis. It took me a very, very long while to figure that out; it is what was messing things up. The apparent series-extension was not that, the problem was the huge number of “bins” on the second axis strung everything waaaaaay out.

    It really is annoying that so much of excel is hidden from view; it makes troubleshooting very, well, troublesome.

  292. But again, Excel is trying to be helpful. It remembers changes you’ve made to the chart, for example, changing the axis that a series is plotted on, and even changing the chart type of that series. When you add another series, Excel will apply these later settings. I’ve had many occasions where I’ve wondered how Excel knew I wanted the series to be a line chart plotted on the secondary axis, until I remember that I recently changed another series in this way.

    When you are aware of this behavior, it’s not hard to realize why the new series looks like it does, and that all you need to do is make the reverse changes to this series. But it takes years to develop this knowledge of Excel’s behavior.

  293. Hi Jon,
    You are amazing and this is such a great tutorial! It looks like you still are actively responding to our comments, so I thought I’d tell you about my problem and see if you know what is going on :)

    Last month, I made the perfect chart for the board at my work. I created a chart that had 3 month’s (“YTD” as of March) worth of data for 6 office locations. The first column is what was invoiced to our clients, and the second column is what our clients paid. Each of those columns is separated (stacked column) by month, so we can see how much was invoiced and paid for each month as well as seeing the TOTAL YTD for both invoiced and paid.
    Then, using the secondary axis, the line showed how much had been paid at “this time last year.”
    Everything looked AMAZING — and I had it set up so any future month is INCLUDED in the chart, but will be HIDDEN until that month’s data is available.

    NOW — I have received April’s data and went to UNHIDE the column for April so I could enter it, BUT the chart made the secondary axis scrunched up again (like in “Manipulating the Axis” section). I went through and tried to match the previous settings to this months YTD data chart secondary axis, but I cannot get it to stretch out…

    Any thoughts?

    Thanks for your time!

  294. The months are stacked? Unhiding did not add any new labels along the category axis? In this case I don’t know what went wrong.

    What is plotted along the category axis?


  1. […] reason I developed this was to help in creating a Multi Clustered stacked barchart, equivalent to Clustered and Stacked Column and Bar Charts | Peltier Tech Blog | Excel Charts. My method doesn't require messing about aligning and inserting lines etc in the raw data. Again […]

  2. […] When I put your data in to Excel I get a graph that seems to be as you described. Perhaps you want clustered & stacked graphs, the procedure for creating them is discussed here: Clustered and Stacked Column and Bar Charts | Peltier Tech Blog | Excel Charts […]

  3. […] dynamic clustered stacked column graph. I've been using the fantastic guide provided by Jon Peltier which has gotten me 1/2 way to my goal, but I can't get the chart to work dynamically (it's missing […]

  4. […] More examples of clustered and stacked bar graphs illustrate several different ways to represent data with more than one categorical variable. Note, above all, that a clustered bar graph is not always an appropriate way to represent two categorical variables. With that said, a clustered bar graph is often a very elegant solution when used properly: this discussion briefly presents, as one of its examples, a clustered bar graph as a solution to a complex data set. Also note its use of a second labeling system as a possible alternative to complementing the first labeling system with color. […]

  5. […] my tutorial about Clustered-Stacked Column Charts. Hint: one of your stacks has 3 series, the other has […]

  6. […] So you want a cluster for each month, where each cluster has three stacked bars? Try Clustered and Stacked Column and Bar Charts. […]

  7. […] story. Usually the three highest pages (by far) are Error Bars in Excel 2007 Charts, followed by Clustered-Stacked Column Charts and Installing an Add-In in Excel 2007. On Wednesday, the day I first noticed the spike, the top […]

  8. […] Clustered-Stacked Column Charts I showed how to create a chart that combined clustered columns with stacked columns. It’s […]

  9. […] 44,612 – Clustered-Stacked Column Charts […]

  10. […] Peltier kiváló Excel guru oldalán pedig olvashattok egy nagyon részletes leírást ugyanerről a grafikonról, rengeteg további […]

  11. […] document.write(''); Is this what you want? Clustered and Stacked Column and Bar Charts – Peltier Tech Blog […]

  12. […] document.write(''); If you want a more rigorous treatment of clustered/stacked charts, check out my tutorial, Clustered and Stacked Column and Bar Charts. […]

  13. […] document.write(''); See this Clustered and Stacked Column and Bar Charts – Peltier Tech Blog […]

  14. […] document.write(''); Hi Jon Peltier has an article about this type of chart here: Clustered and Stacked Column and Bar Charts – Peltier Tech Blog […]

  15. […] The team names aren't centered under the clusters of columns. You can read my tutorial about Clustered and Stacked Column and Bar Charts to see how to make the chart above look better (below). Alternatively I've written Excel VBA […]

  16. […] document.write(''); This technique may give you what you want: Clustered and Stacked Column and Bar Charts – Peltier Tech Blog […]

  17. […] actual data. MS Office doesn’t offer this chart type yet, but you can follow these step-by-step instructions as laid out by the Peltier Tech Blog to create […]

  18. […] Welcome to the forum. See for example, Jon Peltier's article here: Clustered and Stacked Column and Bar Charts – Peltier Tech Blog or Andy Pope's here: Stacked […]

  19. […] document.write(''); Hi Clustered and Stacked Column and Bar Charts – Peltier Tech Blog […]

  20. […] Joe left a comment on the Clustered-Stacked Column Charts article asking how to make column charts with variable width columns. Stacked versions of these […]

  21. […] After much research and testing…I learned that if you have quite a few bars – you can’t tweak your graph with spacing and bar width.  If you have only a few bars of data – you can use this method.  Small combo stacked/clustered chart […]

Speak Your Mind


Peltier Tech Charts for Excel 3.0