2

Please look at the image below: How can orange bar in the first chart be that big. This pattern repeats even if I make both values half like 105, 21 or 53, 10 etc.

enter image description here

As soon as value is value is 43 the chart starts behaving fine.

Thanks.

Kashif
  • 14,071
  • 18
  • 66
  • 98

2 Answers2

4

Excel is changing the axis (that you don't have displayed) automatically to what it thinks is a relevant range. However, this range is probably something like 75% to 100% as in the following picture (with the axis displayed):

enter image description here

While this seems to not make sense for the purpose of a 100% stacked bar chart, this is nonetheless what Excel tries to do.

To fix it, go to Primary Horizontal Axis options and change the Minimum from "Auto" to "Fixed" and set the value to 0. You might also want to set the Maximum to "Fixed" and set the value to 1. See options pictured below:

enter image description here

elmer007
  • 1,412
  • 14
  • 27
  • Thanks, If I just move the small value on top the chart starts working fine. Other thing I noticed is that if the top value is divided by the values below and the result is greater than equal to 5 the bar starts from 75% instead of 0% for example put 4 on top cell and 1 on bottom its fine now just change the value of top cell to 5 and chart bar is wrong. – Kashif Dec 13 '16 at 21:20
  • @Kashif You're welcome. I don't know why Excel chooses the axis ranges that it does (sometimes it's helpful, sometimes it's not). It makes sense to me that a 100% stacked bar would *really* need to have the proportions shown correctly, but alas... – elmer007 Dec 13 '16 at 21:26
0

Excel seems to start doing its auto axis adjustment once one of the numbers is less than about 20% of the other number.

Shane
  • 13
  • 4