#How to create pie chart in excel 2007 free#

Note that John’s formula and Excel’s chart show the same percentages for each wedge as with unsorted data. The sorted data and the updated charts are shown below. I discovered this when I sorted the data to check whether John’s formula or Excel changed their displayed percentages. In fact, the funny Excel percentage feature means you can’t even tell me the order of Beta, Delta, and Zeta if you consider the percentage labels. Beta is obviously much larger than Delta, which is larger than Zeta: I defy anyone to tell me this based on a pie chart without labels. I’ve included the column chart merely to show how superior is is in showing relative differences in values. My version of the pie chart is shown below the table, alongside a column chart. Of course, Excel only deducts 1 percentage point from this percentage and 1 percentage point from one of the 21% percentages. John’s formula knocks two percentage points off the greatest percentage in the list to make them add to 100. Labels are shown in column B, values in column C, percentages rounded to whole numbers in column D, and John’s clever formula in column E. I’m talking about John’s second example, recreated below in Excel 2003. Follow the link to see John’s formula, which I will not discuss other than to say it works for simple pie chart percentages, but I don’t think even Richard Feynman could have predicted how Excel would deal with more complicated Excel pie chart percentages.

John Mansfield has followed the path of greatest resistance, and has proposed a formula to recreate the percentages reported in the Excel pie charts. A third approach that occurs to me is to use VBA to extract the percentage values from the data labels and insert them into the worksheet.

#How to create pie chart in excel 2007 free#

There are two very good, free Excel utilities that can be used to apply custom labels in a worksheet to a chart: Rob Bovey’s Chart Labeler and John Walkenbach’s Chart Tools. There are two ways I would have dealt with this issue: (1) using a number format of 0.0%, as in the right chart above, coincidentally also the right-hand chart above, and (2) using custom data labels from the worksheet that show the correctly rounded percentages. My colleague John Mansfield has noticed in meetings with these same pointy-haired administrators that they compare the percentages on these pie charts with the percentages calculated in the worksheet, and they get just as exercised when these percentages don’t match as they do when the total percentage adds to 99 or 101 due to rounding error. I suspect this obviously intentional misstatement of percentage was implemented to mollify the high level pointy-haired managers, who deal with transactions worth billions of dollars, but worry that the percentages in their pie charts add to 100. The second chart, with one added decimal digit of precision, correctly displays 33.3% for all three wedges. However, the first chart reports percentages of 34%, 33%, and 33%. Each pie wedge is 1/3 of the total, 33.333333…%, rounded to 33%. Both charts below use the same data range, three cells each containing the value 1. This only occurs if the precision of the labels is unit percentages (number format of 0%), not if more precision is allowed (number format of 0.0%). The phenomenon is that Excel will place incorrect percentage labels onto the wedges of a pie chart, simply to ensure that the displayed percentages add to 100. This is a specific illustration of a strange and wonderful phenomenon in Excel pie charts. I do have one (or more) of those rants posts in me somewhere, but this isn’t one. This isn’t the usual “Pie Charts Suck” entry that we see so many of.