# Thread: Stacked Cluster Graph (2007)

1. ## Stacked Cluster Graph (2007)

I'm wondering how I can make a graph as shown in the attached image in Excel. It seems like it would be a stacked cluster graph because there are 2 series across the x-axis using a stacked bar to show the difference based on financial contribution differences into investments. But Excel has either a stacked graph or a cluster graph option - no combination of the two. I can't seem to figure it out. Is this possible to do?

2. ## Re: Stacked Cluster Graph (2007)

See Creating a Clustered-Stacked Column Chart on Jon Peltier's website.

3. ## Re: Stacked Cluster Graph (2007)

I'm trying to follow the different tutorials but I can't get past step one in any of them. It seems like Excel 2007 interprets the data differently because just be selecting their data, i get a different result from what they show. Just by following step 1, I get this result:

4. ## Re: Stacked Cluster Graph (2007)

Sorry, all I had to do was click the "Switch Row/Column" button. I think I got it now, thanks Hans.

5. ## Re: Stacked Cluster Graph (2007)

Here is an example file...

Steve

6. ## Re: Stacked Cluster Graph (2007)

wow, thanks steve... did you follow similar steps as the link Hans provided to get to that graph?

7. ## Re: Stacked Cluster Graph (2007)

I think it was similar, though i am not sure since I did not use the articles, but just created it. I may have read those articles in the past so may have "absorbed the techniques". I have learned alot from Peltier's chart examples (both directly and indirectly)

The biggest problem was guessing at the data values to create the chart to mimic yours....

Steve

8. ## Re: Stacked Cluster Graph (2007)

yeah i was impressed <img src=/S/yep.gif border=0 alt=yep width=15 height=15> i could have just given you my spreadsheet i guess... but there are a ton of values so it was probably easier for you to make it up anyway. If anyone was wondering what its for (if it wasn't already obvious) its an investment calculator that gives you a feel for what you can expect to have at retirement depending on one contribution rate against another and provides the difference in estimated ROI.

9. ## Re: Stacked Cluster Graph (2007)

Steve, is there an easy way to make the data look the way you made it in your example from a solid data block? Or would that require a macro of some sort?

10. ## Re: Stacked Cluster Graph (2007)

The data have to be spaced as in the examples you can find from the link I posted, and in Steve's attachment, you cannot create a clustered stacked chart from a solid data block.
You could start with a solid data block and use formulas or a macro to create the spaced source range for the chart.

11. ## Re: Stacked Cluster Graph (2007)

That's what I figured. I don't know if I'll ever need to do it again so maybe I'll skip the headache of making a macro and just do it by hand.

12. ## Re: Stacked Cluster Graph (2007)

Manually there are some tricks: Using some intermediate columns, copying into different groups and sorting to space and add the blank rows.

It can also be done with the original dataset using formulas if you will always be pasting a "block" of data. the formulas will depend on the block setup and how they are clustered...

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•