+ Reply to Thread
Results 1 to 6 of 6

100% stacked bar with spacer

  1. #1
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    49

    100% stacked bar with spacer

    Hi All

    I need help in solving a frustrating charting problem. I have created a 100% stacked bar and I am looking for a way to display the total at the end of the chart with a spacer between the stacked bar and the total.

    Ideally, I'd like to be able to display the values "1" and "2" as % and "Total" as number. I can set the values as number rather than percent and insert a text box, but was looking for a better way. Below is an image of what I'd like achieve and I have attached my spreadsheet and attempt at the chart. For my example I just added a shape and filled in text.

    Thanks in advance for any help!

    mockup.png
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Homeless. I am not kidding.
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,134

    Re: 100% stacked bar with spacer

    To insert a gap just insert a helper column in your source table and assign a constant value for each category. Your total looks confusion can't help you with that one.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    49

    Re: 100% stacked bar with spacer

    Thanks for the quick reply. This serves the function of having a space between the 100% stacked bar and the total, but ideally I'd like to see something like the picture below.

    mockup.png

    So it would look like this:
    100% stacked bar chart made up of values in columns E&F, rows 80-83.
    Then a spacer.
    Then totals made up of values in cells H80-83. The totals came from the tables in the worksheet (sum of within local control and other for each table).
    Attached Files Attached Files
    Last edited by Jnehra; 10-04-2019 at 10:42 AM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,011

    Re: 100% stacked bar with spacer

    It is unclear to me what difficulty you are having. If it helps, I took your most recent file/chart, and simply changed the chart type from 100% stacked bar to a regular stacked bar. That gave me something very similar to your picture. Is that closer to what you want?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,772

    Re: 100% stacked bar with spacer

    The space series is distorting the 100% stack.

    Try this. Remove space series
    Move Total series to secondary axis and change chart to Bar chart
    Add Secondary horizontal axis
    Adjust the maximum of the primary horizontal axis
    adjust the min and max of the secondary horizontal axis

    If you want the axis labelled you can use a custom number format to hide the parts not required
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    49

    Re: 100% stacked bar with spacer

    @Andy Pope - That is exactly what I was looking for.
    Cheers mate!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Insert something two rows below the last entry, using VBA? Ignoring spacer rows (Example)
    By LIL2606 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2019, 11:49 AM
  2. Replies: 1
    Last Post: 08-24-2016, 04:15 PM
  3. Pasting based on Current Region but want spacer row betwen
    By liquidmettle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-23-2015, 06:17 PM
  4. Replies: 1
    Last Post: 05-30-2013, 10:03 AM
  5. Using the AutoFilter with a spacer column
    By penfold1992 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2012, 06:10 PM
  6. Replies: 0
    Last Post: 04-24-2006, 08:45 AM
  7. [SOLVED] To create a stacked column chart and group the stacked bars togeth
    By Jacqueline in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-18-2005, 10:06 AM

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