Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Attached Images Attached Images
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stacked Cluster Graph (2007)

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

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:
    Attached Images Attached Images
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Stacked Cluster Graph (2007)

    Here is an example file...

    Steve
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stacked Cluster Graph (2007)

    wow, thanks steve... did you follow similar steps as the link Hans provided to get to that graph?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •