Excel Pie Chart - Want all 0.00% to show for consisentcy

**RxMiller** · 03-19-2009, 04:49 PM

Excel 2003 VBA object reference called from Access 2003

I have included a snapshot jpg of the pie chart with the data and a txt file of VBA that creates the pie chart.
The excluded code just grabs data from SQL Server via a snapshot in Access. Code builds the Excel sheet shown, than creates the pie chart.

My problem is two fold:
The data points (identified on the jpg) tend to be crowded and crossed up. Is there any way to spread the data lables across the chart area for readability?

The 0.00 value formats as 0% instead of 0.00%. And, the additional 0.00 values are missing in the pie chart. Although they are not always 0.00%, when they are, my accountant wants them to be clearly shown as 0.00%.

I am a coder, so any ideas would be appreciated.

**teylyn** · 03-19-2009, 06:56 PM

Hi,

pies are best when they are for eating. For charting they normally stink, for all number of reasons (your problems included). Try bar charts instead, which will show ALL data points, even if zero, and give you more leeway for labelling. Also, bar or column charts are much easier on the reader, since they allow easy comparison of values. Pie wedges are almost impossible to evaluate correctly, thus the need to include the value labels.

Another hint: drop the 3d effect. It does not add any value to the chart, in fact, it makes it even more difficult to correctly interpret the data.

Read up on Edward Tufte, Stephen Few, and Jon Peltier, who are the visualisation experts. Jon has a great website with lots of info on Excel charts and VBA.

http://www.peltiertech.com

cheers

**RxMiller** · 04-08-2009, 06:52 PM

Thanks for the references. I will check them out.
Wonder if I start with a bar chart, then try to change the format to a pie chart.
I think the problem in trading is that every one wants a peice of the Pie (chart)
But, me.. I do like Bars more than I like Pie.

**teylyn** · 04-08-2009, 08:14 PM

I think the problem in trading is that every one wants a peice of the Pie (chart)

If you need help convincing them that pie charts are not the best choice, squizz through the following:

If you read nothing else, read this:
Stephen Few, Save the Pies for Dessert
http://www.perceptualedge.com/articl...or_dessert.pdf

Run a few of the tests he suggests ...

Pie charts: a neverending discussion
http://charts.jorgecamoes.com/pie-ch...ng-discussion/

Peltier Loves Pie- Jon Peltier's PTS Blog (read the article and follow all the links therein!)
http://peltiertech.com/WordPress/peltier-loves-pie/

Pie Chart for Pi Day - Jon Peltier's PTS Blog
http://peltiertech.com/WordPress/pie-chart-for-pi-day/

Pie Chart Rounding in Excel - Jon Peltier's PTS Blog
http://peltiertech.com/WordPress/pie...ding-in-excel/