Can a Script be added to a bar chart to FORCE its Axis value? RRS feed

  • Question

  • I use openXML to update a barchart on a word doc.  When it was just the value 0 - 100% it worked fine.  now?  it MUST show the Percent % Axis on the bottom to be scaled to the largest value

    But in Excel?  its like this:

    The colors are off, but the blue/orange bar chart shows up right in Excel.  In word? it insists on scaling it.  If the scaling is changed from auto? then it sets that as the maximum limit.  It may be 105 or 150 or 220 for a value and it needs to show the axis accordingly.  if a value is 112?  the bottom axis value must end at 120.

    But if its 100?  then it cannot extend.  for some reason Word wants to auto scale the axis to 120% if the value is 100%

    On a 0-100% barchart?  if you exceed 100 then the bar shrinks and the 100% axis mark doesnt change

    So is there a way to add some custom script to a word document?  To modify the barchart after its been stuffed with values?

    Bradley :)

    Tuesday, October 11, 2016 8:10 PM

All replies

  • Hi Bradley Rogers,

    According to your description, unfortunately, I am not able to reproduce this issue. I have created one 100% Stacked Bar chart in Excel and Word manually, then change value, but the chart does not insist on scaling it.

    After change value:

    I find that your first screenshot is not 100% Stacked Bar chart, but Stacked Bar. So I suggest that you could check whether chart type is 100% Stacked Bar chart.

    Thanks for your understanding.

    Wednesday, October 12, 2016 7:59 AM
  • David,  I'm sorry for not writing more clearly!  I was surprised to see your answer, and checked and I did not fully explain the problem.

    I was in the process of writing and was distracted, then came back, thought it was done and hit Submit.

    I have tried 2 different stacked horizontal charts, 1)  0-100% and  2) "normal".  With the normal chart, I set the numeric section to Percentage, then its Format Code to be 0% (it defaults to 0.00%)

    Now the scale at the bottom defaults to Automatic.  If the values for the stacked bar add up to 100? the scale becomes 120.   I think it must be 94 before the scale drops back to 100 (max).

    changing from 100% Stacked bar, the scale at the bottom scrambles

    So here with a NORMAL stacked chart its configured as percent, then I chenged the values in the excel

    which were 20, 30, 50  to become .2, .3, .5 and now its a 0-100 stacked chart that can be scaled.  Almost working!  But notice I set Axis Bounds Maximum to 1.0001 ?  if 1.00 it would leave out 100%  if I get the values in the chart to be 100?  it lines up perfectly on 100.  But what if the value is 120?  The chart stays at 100%

    Ok then lets set the Axis bounds to Automatic

    ok NOW the chart scale stretches as I need it to, this is PERFECT!  but what if all 3 of my values are less than 100 ?

    ok its now RUINED!!  Worthless!  cannot use it!  why does it FORCE the axis to scale to 120??  I want to force the chart's inner hidden logic to say do NOT scale until the values hit greater than 101.  Right now?  that hidden number is 96

    This is the FAILURE of the bar chart.  Somewhere it has some setting to say if barchart Value >95 then expand out.  I want to change this to be > 100, so at 101 it can expand.

    If the 100% Stacked barchart is used?  it never expands.  But does this clarify the problem?  Thank you for your help

    Bradley :)

    Wednesday, October 12, 2016 2:30 PM
  • Hi Bradley Rogers,

    Sorry, I am still not clear about your real issue and requirement.

    As far as I know that Stacked bar charts show the relationship of individual items to the whole. You can use a stacked Bar chart when you have multiple data series and when you want to emphasize the total.

    100% stacked bar charts compare the percentage that each value contributes to a total across categories.  You can use a 100% stacked bar chart when you have three or more data series and you want to emphasize the contributions to the whole, especially if the total is the same for each category.

    For more information about there are differences between Stacked bar chart and 100% stacked bar charts, you could post your question on the Technet forum for Excel IT Pro Discussions

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Thursday, October 20, 2016 1:24 AM
    Thursday, October 13, 2016 6:02 AM