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

    Combined Graphs (2000)

    Hi All,

    I'm attaching a workbook that I'm having some problems with.

    My data is on a sheet called Data.

    Sheets Chart5 and Chart6 show 2 col charts for the data in D1:E8 and A1:B8 respectively (shaded yellow). Ignore the other data - it's for the pie charts on the other sheets.

    What I'd like to do is combine these 2 charts into 1 col chart. The way I want this to look is 2 bars together for each town (1 for its income and 1 for its drop out rate). That town is followed by a sister town in the county. Then I'd like some horizontal spacing to separate these 2 towns (and 4 bars) in the first county from the next 2 towns in the next county.

    Since the drop out rates are in percentages, I used a secondary value axis for that.

    To that end, I created another copy of the data in G1:I15. The sheet "Combined Col Charts" is about as far as I can take it. What I can't seem to do is the following:
    - have the names of the towns be horizontal since there's plenty of room if the name can stretch across both bars
    - have the county name centered under its 2 towns (Union County goes with Berkeley Hts and Elizabeth, Monmouth County goes with Rumson and Asbury Park, Passaic County goes with Wayne and Paterson).
    - optionally, I'd like the legend box to be narrower so that each legend item goes across multiple lines.

    I also tried another approach reflected by the data in G18:I26 of the Data sheet. I scaled the incomes by /1000 and the drop out rates by *1000 so they could use the same value axis. The results are displayed on sheet "Combined Col Charts-2". This addresses some of the points above but:
    - I don't like the width between counties.
    - I really prefer the secondary axis so I don't have to scale and I can have "dimensions" for the numbers on my value axis (000$ for income and % for drop out).

    Any ideas?

    TIA

    Fred

  2. #2
    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: Combined Graphs (2000)

    Fred,
    I have amended your Combined Col Charts tab to give an alternative version. Note, I have added a semi-transparent autoshape to the Drop-out data series just to make it easier to see, but you can simply play with the gap width to make everything visible if you prefer. (there are some minor alignment niggles with the autoshape that I haven't resolved yet.
    Hope that gets you started.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    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: Combined Graphs (2000)

    Rory,

    Thanks a lot for the revised chart. This looks great.

    A couple of questions:
    1. When I initially added the secondary axis, I was going crazy bcs it looked like Excel had created a stacked col. Then I realized that one col was overlaying the other; depending on the 2 numbers for a given city, it could look like a stack or one was entirely hiding the other. That's when I started playing with the overlap and gap width. I know these are critical to adjusting the bars the way you want. But for the life of me, I could not figure out how to use these to get what I wanted. Help was of no help and Walkenbach's book on Excel charts was also of no help in explaining these controls. Do you have any insights on what these do and how to use them to get what you want? I did finally figure out the gap width: I guess each data point is allocated a width. A gap width of 0 uses the entire width so that adjacent cols touch while a bigger gap width makes the bars skinnier since the gaps between bars are bigger. But I couldn't figure the overlap - I'm sure this has something to do with the overlap of bars belonging to a single x-axis value but I couldn't figure this out (especially the use of negative numbers). What I was trying for was to have the income and dropout bars side by side (hence my ...-2 sheet).

    You mention playing with the gap width to make everything visible but how you do that remains invisible to me - that's what I was trying to do.

    2. The "pink" bars: just want to confirm how you got these. I see you created a bar in my Data sheet. Did you then take a screen shot of this and save to a file, then use that file as a picture for the fill effects of the drop out data series with the stretch effect?

    3. Is there some way to create extra spacing between the counties? I don't want a bar's worth of spacing - too much. I tried playing with this also by creating a blank row between the counties.

    Thanks again.

    Fred

  4. #4
    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: Combined Graphs (2000)

    Rory,

    thanks for the explanations. If you do have some time to post an example of how gap width and overlap work together, that would be great. I feel that I'm on the verge of grasping it but, as Costello said in the lead up to Who's on First, "it just keeps slipping away." I know exactly how he felt. (Don't worry about the anaology.)

    What you mentioned about plotting the dropout and income side by side on separate axes is exactly one of the problems I had with the original. I thought I was being clever to get the cols side by side by using a trick I had seen on the lounge - put them on separate rows in the data sheet. But, as you mentioned, the labels don't span the 2 cols (unless you repeat the label on each row).

    On the transparent bars and how you got them-that sure is a lot faster than what I had contemplated. Have to remember that Shift+Edit trick.

    Fred

  5. #5
    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: Combined Graphs (2000)

    Fred,
    You might want to look at the attached - it's one hell of a kludge, but it does get the series side-by-side with overlapping labels. (There are two invisible series plotted with zero values which is why the legend looks a little odd!)
    I will also post an example of overlap and Gap width shortly.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    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: Combined Graphs (2000)

    Fred,
    The overlap sets the width of the gap between data markers (i.e. bars in this case). In other words, for negative values, it will create a gap on each side of the data markers. (This will only work if you have two or more data markers plotted on the same axis, so has no effect if each series is plotted against a different axis.) For positive values, one set of data markers will overlap the other - e.g. for 100%, they overlap completely and one obscures the other. The gap width sets the gap between categories - e.g. there would a gap between the last data marker for Berkley Heights and the first bar for Elizabeth. (I hope that makes sense - if not, I can post an example.)
    Unfortunately, if you plot the two side by side but on separate axes, the category labels do not spread across the two values - e.g. you get Elizabeth marked for income but not drop-out, or else you end up with Elizabeth shown for both. When I said that the gap width could make everything visible, I only meant that you could adjust so that the bar "behind" was partly visible too.
    To get the pink bars I added a rectangle to the worksheet using Autoshapes, formatted it for fill and transparency (50% in this case), then did Shift-Edit, Copy picture, selected the data series and then pasted.
    I don't unfortunately know of any way to add a gap between categories without plotting a blank data series. I will experiment some more and see if I can come up with anything - perhaps plotting two charts on top of each other will work. (Edit - I don't think this will work as you can't make the chart area transparent, at least not that I have discovered!)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    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: Combined Graphs (2000)

    Fred,
    Please see attached for examples of overlap and gap width. Please note, I have just edited my original post about the two as I had put them the wrong way round! (my excuse is I was on the phone talking someone through reformatting a Word doc at the time <img src=/S/blush.gif border=0 alt=blush width=15 height=15>) Apologies for any confusion.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    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: Combined Graphs (2000)

    Rory,

    this is super. How did you do that? I was experimenting by deleting the invisible things from the legend. The more I deleted, the more the bars started to overlap. I'm sure this will be answered in your example.

    Since I'm taking a screen capture and inserting the result into ppt, I can omit the legend in excel and then create my own legend in ppt (or just take 2 additional screen captures in excel of the top and bottom 1/4 of the legend and put them together in ppt).

    Thanks.

    Fred

  9. #9
    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: Combined Graphs (2000)

    Fred,
    It just took a bit of tinkering with the overlaps and series orders so that the "real" data values for each axis plot over the zero values on the other axis. It's not perfect by any means but it's a start!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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