Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    mixing chart types

    Let's say I have a chart with 4 data series (quarters of the year) containing 4 data points each (3 months of the quarter and a quarter guesstimate/projection).

    I want the 3 months data of each series to be in a stacked column and the 4th data point to be a column that displays beside the stack. I'd end up with 4 sets of stacked column/single column pairs. The stacked column showing the cumulative quarter data and the individual column beside it showing the projection for that quarter.

    I can use the dual-axis chart type to get the stack and the column, but they display on top of each other. I've tried playing with the gap width but the two columns are still centered over each other. I've tried playing with the overlap setting but then the stacked column isn't anymore.

    Is there something like an "offset" setting that would let me separate the columns?

    Thanks!
    Sue

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: mixing chart types

    Have you considered combination charts. Using this technique is a fairly easy way to achieve what you seem to be striving for. There is just one draw back, as I do not think both chart types can be the same. So if are are happy to use your stacked bar charts together with a line chart for the projections you can do it easily. Set up your chart in the usual way, including the projections as the last item in each range. This should give a stacked bar chart with five segments. Select the segment that refers to the projections, right click and select chart type. then select a line chart, (or possibly an area chart), and you should be left with a combination chart - the stacked columns and a line across for the projections.

    If you do not like that approach, there is a tricky procedure that you could follow and see if it gets what you want. Create 2 charts, one on the actual data and stacked as you want. Create another on the projected data. Make sure they are to the same scale and are the same size. What you do next depends on determined you are. Move your projections chart away from anything else. set the background color of the plot area and the chart area to none. Select a region of cells to include the chart. Next turn off gridlines. Then hold Shift, select edit and Copy Picture. (or if you have the camera tool on your toolbar, just click it). Next paste the picture away from anything else. Next, with the picture of the chart selected, use the crop tool on the picture toolbar, to shrink it down to the plot area only. Then drag the result over to the original chart and align to give the effect you want. There might some trial and error involved, but anything worthwhile is never easy. This is all theory, as I have never actually tried it.

    Before doing the above, you might like to set the column width of the projections chart to about half the width as the other chart, to assist in overlaying it.

    Hope that helps you.

    Andrew C

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: mixing chart types

    Hi Sue,
    You can sort of do what you want by adding a blank column (or row, depending on how your data is set out) and plotting effectively 2 points for each month. The second point for each month should be blank for the first 3 months and for the fourth should contain the data you want plotted, while the first point for each month contains data for the first 3 months and is blank for the fourth. I realise that's probably not entirely clear so I've attached a sample. It's not perfect by any means but I think it's close to what you're after.
    Hope it helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: mixing chart types

    Rory,

    That's as close as I've been able to get. I'd really like the pairs of columns to be butted up next to each other with the axis tick marks between the paired columns, but this will work!

    Thanks!
    Sue

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: mixing chart types

    Andrew,

    The combination chart just wasn't working the way I wanted it to...

    I thought about superimposing charts but decided that was just too much trouble.

    I got another suggestion that worked well enough...making the 2 sets of data 2 different data series. Not exactly the end result but close enough.

    Thanks!
    Sue

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: mixing chart types

    How about extending Rory's idea and insert another column, with no values. That inserts space between the pairs of data. Then right click on a data column and select Format Data Series, Options and set the gap width to zero.

    I have done that with Rory's file and am attaching it.

    Andrew
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: mixing chart types

    That's perfect!!! Thanks!!

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: mixing chart types

    nice touch Andrew!
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: mixing chart types

    Andrew,

    Let's push this one step further - I need what you said couldn't be done.

    I need 2 sets of stacked bars. I have 2 company's that I want to compare over a 4-yr period. But for each bar, I want it to be stacked. So company 1's bar has components "a" and "b" in each year as does company 2. Doing just the total should be easy but doing stacked bars - I don't have a clue.

    I liked this solution but the other one (cut and paste, etc.) - I think I'd do it by hand. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thks

    Fred

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: mixing chart types

    Hi Fred,
    The attached is as close as I can get - it's a variation on the original theme. The datasheet itself isn't exactly pretty but the chart is I think more or less what you want. To get it, I plotted the data as a line-column on 2 axes chart, changed the chart type for each data series on the second axis to Stacked Column and then reduced the gap width to zero. The blank columns are there as separators for the graph.
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: mixing chart types

    Hi Rory,

    Thks. I think I follow what you say but I found a way which accomplishes what I need that I think is easier.

    But just to make sure I understood your solution. I did what you said. I also had to change the chart types for the data series on the first axis to stacked bar also to get your result. I assume you meant that also.

    As far as my "easier" solution: I started with the solution you and Andrew came up with for the other problem. The worksheet basically looked like:
    1st quarter....
    m1 20
    m2 25
    m3 50
    m4 100

    Numbers are not important.

    All I did was fill in some additional numbers for m1, m2, and m3 in the 2nd sub-col (for all quarters; altho my "quarters" are "years") and set the m4 value to 0. That gave me exactly what I needed. There doesn't seem to be a way to get this with the wizard directly.

    Now we just need to fix the legends. I'm copying/pasting this into a ppt file so I'll just hide the Excel legend with a ppt text box and create my own legend.

    That's about it. Thks for the help.

    Fred

Posting Permissions

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