+ Reply to Thread
Results 1 to 11 of 11

Diverging Stacked Bar Chart issue

  1. #1
    Registered User
    Join Date
    11-03-2012
    Location
    CO, USA
    MS-Off Ver
    Excel 2013
    Posts
    26

    Question Diverging Stacked Bar Chart issue

    I'm trying to create diverging stacked bar charts in Excel 2007.

    By making some of the values negative, I can get those values to display to the left of the vertical axis. However, columns B through F are in the wrong order on the chart. I highlighted one of the columns of data to make it easy to track them in the two examples.

    By reversing the order of columns B through F, I can make the chart display correctly (Example 2 in the attached file), but the legend isn't in the correct order.

    Diverging Stacked Bar Worksheet.xlsx

    Am looking for help on this, or if there's a better way to do the diverging stacked bar chart in Excel.

    Thank you!
    Lis

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,184

    Re: Diverging Stacked Bar Chart issue

    One way would be to use all positive values and a padding first series in order to align series 6.

    I have left the <PAD> text in place so you can see the construction.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    11-03-2012
    Location
    CO, USA
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Diverging Stacked Bar Chart issue

    This is awesome, thanks! Okay, editing this post because I figured out how to replicate what you did, using a regular stacked bar, not a 100% one (doh!)

    Now, I'm attempting to create a new overlapping bar that is going to stand in for the x-axis scale that is now gone, unless there is a way to have Excel create an x-axis that works with the new method?

    Thanks,
    Lis
    Attached Files Attached Files
    Last edited by Lis7; 06-04-2013 at 10:43 AM.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,184

    Re: Diverging Stacked Bar Chart issue

    what should the x axis scale look like? simply going from 0 to 100% does not make much sense.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,184

    Re: Diverging Stacked Bar Chart issue

    The chart type you used was 100% stacked rather than just Stacked.

  6. #6
    Registered User
    Join Date
    11-03-2012
    Location
    CO, USA
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Diverging Stacked Bar Chart issue

    Thanks, this is what I was trying to do (att.) using overlapped bars, if Excel won't create an x-axis like this:

    60%-50%-40%-30%-20%-10%-0%-10%-20%-30%-40%-50%-60%

    But I can't get the bars to overlap, and the labels aren't working out either.

    928269-b.xlsx

    Best,
    Lis

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,184

    Re: Diverging Stacked Bar Chart issue

    Add another series to the chart and move it to the secondary axis.
    You can then use the negative and positive value scale, custom number format to remove minus sign.

    You will need to play with the min/max/major and crosses values to get a decent layout affect.
    I have left both horizontal axis visible but you can hide the Primary one for the final chart.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-03-2012
    Location
    CO, USA
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Diverging Stacked Bar Chart issue

    Wow! I can make those tweaks, but is there a way to get the new secondary axis to display at the bottom (and not display the primary axis that runs from 0% to 140%)?

    Thanks so much, you rock!
    Lis

  9. #9
    Registered User
    Join Date
    11-03-2012
    Location
    CO, USA
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Diverging Stacked Bar Chart issue

    To get the new axis on the bottom, I tried to plot the new Scale series on the primary axis, and the rest of the series on the secondary axis. The 0% on the new primary axis intersects the new secondary axis at 70%, instead of 50%, can you help me figure out what I'm doing wrong? (My new chart starts at row 29.)

    Attachment 240600

    Thanks,
    Lis
    Attached Files Attached Files
    Last edited by Lis7; 06-04-2013 at 01:31 PM.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,184

    Re: Diverging Stacked Bar Chart issue

    Simplest thing is using the original double axis chart.

    Remove primary horizontal axis Line, Labels and tickmarks.
    Add secondary vertical axis. Set Horizontal Axes Crosses at Automatic.
    You can then remvoe formatting of secondary verical axis

    Example includes screen shot of axis dialog
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-03-2012
    Location
    CO, USA
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Diverging Stacked Bar Chart issue

    Andy, apologies for not posting sooner, I will try your suggestions in your latest post and let you know the results. Thank you!

    Best,
    Lis

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1