AJP Excel Information AJP Excel Information

Automatic removal of zero values in pie chart


You can see from the above images the effect of setting data items C and F to zero.
The workbook contains an example of a pie chart that automatically removes zero values from the pie and the legend.
The chart uses named ranges in order to dynamically expand or contract.
The formulas used to remove zero data points are courtesy of Doug Tyrrell (www.dotxls.com)
Updated to include formula to handle data laid out in rows rather than columns.
There is a bug in xl2007 that if you set all of the original data values to zero the named range  formula will report as reference error and then become detached from the chart. It will not automatically update the chart when new values are entered in to the data cells. To avoid this break of formula I have modified the named range formula to always include 1 point. This will allow the chart to automatically update when valid values are entered.
Note this does not remove the warning of invalid formula that will appear with all cells are zero.
Download xl2007 version of workbook

Created August 2004
Last updated 5th August 2014 

Return to main page Chart Section VBA section Fun and games section Forum files Tips section Links section Book section Site information Site Search RSS feed Top of page

Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2016 Andy Pope