Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    South Australia, Singapore
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating Complex Chart (XP)

    Can I combine a cluster column, stacked column and line chart type in one Chart?

    Can anyone please help me to do the chart (file as attached) so that TR & AT are stacked column, OP is cluster column and EB is line chart.

    I've posted this question in Powerpoint forum but was told that I should try to post this in Excel forum as someone may well have a clever workaround in Excel.

    Thanks
    Min

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

    Re: Creating Complex Chart (XP)

    The experts will probably come up with something better, my attempt didn't quite succeed. I played around with
    - Adding a dummy series to the chart.
    - Using a primary and secondary value axis.
    - The overlap and gap width of the series.
    See attached version.

  3. #3
    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: Creating Complex Chart (XP)

    If you right-click and press Chart options
    Axes
    Check "Category X"
    Uncheck "Y-Axis"

    You will use 2 categories and not require 2 y-axes
    Then dbl-click to format the top "X-Category"
    In Patterns tab, click "None" for major/minor tick type and for tick label
    and you will not see the sec axis at all.

    Steve

  4. #4
    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: Creating Complex Chart (XP)

    The way I do it is attached. Mine uses no dummy series, but requires a revamp of the dataseries to create a stacked column for all the columns. The line is a second category.

    Steve

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

    Re: Creating Complex Chart (XP)

    Thanks, that's better than what I did.

  6. #6
    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: Creating Complex Chart (XP)

    Also, you are probably aware (but in case Minyi does does not know)
    if you dblclick the the OP series
    "series order" tab
    select OP and <move down>
    you can "move" the clustered column OP after the stacked TR/AT columns instead of before if that is desired.

    Steve

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

    Re: Creating Complex Chart (XP)

    Yep, good point.

  8. #8
    New Lounger
    Join Date
    May 2003
    Location
    South Australia, Singapore
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Complex Chart (XP)

    Thanks everyone for the help but I need to clarify something. AT is part of TR so in the stacked column the total amount for FY98/99 is $43,331 (with comprise of $39,124 for AT) and not $43,331 plus $39,124.

    Hope to hear you guys soon.

    Thank you.

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

    Re: Creating Complex Chart (XP)

    So your first post was misleading. You don't want a stacked chart after all...

    You can do two things:
    1. Keep Steve's chart with a stacked column for AT and TR. Subtract the values of AT from those of TR.
    2. Keep the data as they are and use a normal column chart instead of a stacked column chart. Make AT and TR overlap 100% (horizontally). I have attached this version.

  10. #10
    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: Creating Complex Chart (XP)

    Another way (in addition to Hans' suggestions):
    Copy the TR row to another location: eg Copy [do not move] A2:G2 to A7:G7 in Hans' sheet (note: row 6 is Hans' "dummy data")
    Then change B2 to:
    =B7-B3
    copy B2 to C2:G2

    Then you will prbably need to change the order of of AT and TR:
    dbl-click either range
    Series Order
    Select TR and <move down>

    As some explanation: Stacked charts add each of the individuals together. If you data is "totals" you must subtract the totals to get the "incremental increase"
    Steve

  11. #11
    New Lounger
    Join Date
    May 2003
    Location
    South Australia, Singapore
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Complex Chart (XP)

    Thank you Steve and Hans for your great help. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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