Pie Charting with Missing and Changing Data

Hi All

I am trying to create a dynamic pie chart (has to be a pie chart) that can automatically take into account the fact that the number of series to be graphed each time changes, and that there may be missing data within the list of data

The 'Plot Empty Cell' section under Tools -> Options does not seem to apply to Pie Charts as I cannot select any of the options here

I have tried using the formula =OFFSET('Sheet1'!$B$7,1,0,COUNTA('Sheet1'!$B:$B)-1,1) in the named range for the data called Spend, and =OFFSET(Spend,0,-1) in the corresponding data labels, but I this still graphs areas where there are missing cells

Does anyone have any suggestions how to achieve this

Thanks

Karen

Hi,

Here is an example that does not plot missing data.
http://www.andypope.info/charts/piezeros.htm

Cheers
Andy

ksp wrote:
> Hi All
>
> I am trying to create a dynamic pie chart (has to be a pie chart) that
> can automatically take into account the fact that the number of series
> to be graphed each time changes, and that there may be missing data
> within the list of data
>
> The 'Plot Empty Cell' section under Tools -> Options does not seem to
> apply to Pie Charts as I cannot select any of the options here
>
> I have tried using the formula
> =OFFSET('Sheet1'!$B$7,1,0,COUNTA('Sheet1'!$B:$B)-1,1) in the named
> range for the data called Spend, and =OFFSET(Spend,0,-1) in the
> corresponding data labels, but I this still graphs areas where there
> are missing cells
>
> Does anyone have any suggestions how to achieve this
>
> Thanks
>
> Karen
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I just downloaded and test the workbook and it functions for me.
Zero items are removed from pie, data labels and legend.

To get rid of zero data labels use a custom number format (whole values
only)
0;;;

Cheers
Andy

ksp wrote:
> Hi Andy
>
> Thanks for that - It seems to work however I have to manually delete
> any data labels for that line that has missing data
>
> Do you know of a way to get the data labels to also update with the
> changing data? The link that you gave me says that it updates the
> legand but not for me.
>
> Thanks
>
> Karen
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I have to confess I was guilty of not reading the instructions properly - something I give my users a hard time for

Thanks for your help this is going to save one of my users days worth of work

Ta

KP

No problem glad it helped and thanks for your honesty

Cheers
Andy
ksp wrote:
> I have to confess I was guilty of not reading the instructions properly
> - something I give my users a hard time for
>
> Thanks for your help this is going to save one of my users days worth
> of work
>
> Ta
>
> KP
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info