Microsoft Excel Pie Chart bug

Question

Pie chart percentage not calculated correctly by excel. In the picture you can see that the c and d values are exactly the same, but for some reason "c" has a higher percentage denoted to it and I can't figure out why.

The values are a-21; b-5; c-11; d-11; e-3; f-5; g-1; h-39. On the pie chart the percentage received is a-22%; b-5%; c-12%; d-11%; e-3%; f-5%; g-1%; h-41%

Because all your numbers don't add up to 100, they add up to 96. So your chart needs to make everything up to 100% without using 11.458% like c and d actually are, so one gets rounded to compensate. For example, say i ask you to create a pi chart for the numbers a=1, b=1 and c=1, but you can only use whole number percentages, you'd use 33%, 33% and 34%, so it makes up 100% even though the values should all be the same. — Nick stands with Ukraine
Is there a way to avoid that? Which does not involve adding an extra decimal place. — Anatolii Mokrousov
You can go into "Format Data Labels" in the context menu when you right click, under label options, uncheck percentage and check value instead. @Luuklag I had to resist saying that ^^ — Nick stands with Ukraine
Yeah, I know that, but was trying to get a percentage value. I guess i will just add extra decimal place. — Anatolii Mokrousov

Nick stands with Ukraine Nick stands with Ukraine · Accepted Answer · 2017-08-08T13:25:01

While not the ideal solution, if you right click on one of the labels and press the Format Data Labels option, you can change the Number display type to percentage, this will increase the number of decimal places in the percentage shown but give you the accurate result asked for.

The problem is caused by your actual percentages being:

Name   Val     %
a      21      21.875
b      5       5.208333333
c      11      11.45833333
d      11      11.45833333
e      3       3.125
f      5       5.208333333
g      1       1.041666667
h      39      40.625

As you can see these numbers can't be exactly represented as a (whole number) percentage, the compensations have to be made somewhere. It just so happened that the compensations were made on numbers that should be the same.

Another possible option would be to round your percentage results:

Name   Val     %             Rounded %
a      21      21.875        22
b      5       5.208333333   5
c      11      11.45833333   11
d      11      11.45833333   11
e      3       3.125         3
f      5       5.208333333   5
g      1       1.041666667   1
h      39      40.625        41

The sum of these values is now 99 instead of 96 as in your original, which results in a better graph:

You can do this using the formula =ROUND(num,0) for each of your calculated percentages.

Microsoft Excel Pie Chart bug

1 Answers