I recently discovered a curious "feature" of Excel. If you create a pie

chart, and label the slices of the pie with data labels that include the

percentage, Excel sometimes adjusts the values in unexpected ways.

As far as I can tell, this only happens when the percentages are formatted

as whole-number percents (e.g., "57%"). In this situation, Excel will

adjust values so that the sum of all of the percentages around the pie is

exactly 100%.

I discovered this situation when I created a pie chart that had 7 slices.

The percentage associated with one of the slices appeared in the data as

"0.572702...". However, Excel displayed the value in the pie chart as

"56%". It did not seem to matter if I generated the pie from actual data,

or if I manually converted the data into percentages. As it happened,

several of the other values had been rounded up, so Excel took it upon

itself to change this value to compensate and make the percentages add to

exactly 100%.

Here are the percentages for each of the slices in the pie. The first

column of percentages is what's displayed if I format for 4 digits; the

second column is the display if I reduce to 1 decimal digit; and the third

column is the display if I show only whole-number percents:

1 - 2.0687% 2.1% 2%

2 - 3.7651% 3.8% 4%

3 - 0.7778% 0.8% 1%

4 - 3.0802% 3.1% 3%

5 - 18.5323% 18.5% 19%

6 - 57.2702% 57.3% 56% <===

7 - 14.5058% 14.5% 15%

Total 100.1% 100%

As you can see, when I format the data labels to display a decimal digit,

then Excel correctly rounds the displayed value for Slice 6 to "57.3%".

With this format, the sum of the percentages around the pie did not add up

to exactly 100% -- the sum of displayed alues was 100.1% in this case. If I

formatted the data lables to display no decimal digits, but forced the

display to contain a decimal point, then Excel rounded the value for Slice 6

to "57.%". The unauthorized adjustment occurred only when I formatted for

whole-number percents.

Has anyone else out there observed this kind of behavior?

-- David Benson

## Bookmarks