Prev: Variable Axis Length
Next: Automatically vary range in graph depending on #rows data entered?
From: TJ on 1 Jul 2008 15:06
Have you tried setting the format to Custom, with '0%;;;' (three semicolons
following the formatting)? This is for the data labels only and will hide the
0% values you are referring to.

I am having a similar issue that I cannot solve. I am trying to keep data
labels that have blank values from showing up in the chart. I also would like
to do this automatically as the data I am using can change frequently. I have
two columns but not every row with a name in column A has a value in column
B. The cells are blank, and those that fall into this category still show up
in the pie chart as name-only. Because I have my formatting set as described
above there are no 0% values showing up, but I cannot figure out how to get a
non-value label to not show up without deleting it manually. Does anyone know
of a way this can be done?

"Bob" wrote:

> Sandy,
> Thanks. I see what you did. However, I was hoping to accomplish the same
> thing automatically without any manual intervention (or macros).
> Bob
>
>
> "Sandy" wrote:
>
> > Bob
> > I have tried setting up a Pie Chart in the way you are describing with the
> > "Percentage" checkbox selected in Data Labels and it works fine for me
> > (unless I am at total cross purposes).
> > Have a look here
> > http://www.webstruct.co.uk/webstruct/piechart/PieChart.xls
> > I have uploaded the simple chart I tested.
> >
> > Sandy
> >
> > "Bob" <Bob(a)discussions.microsoft.com> wrote in message
> > news:CE408F4A-F333-47A4-9014-CFBF31492AB3(a)microsoft.com...
> > > Sandy,
> > > Thanks for the suggestion. Unfortunately, since I have selected the
> > > Percentage checkbox for Data Labels (which I need to show), Excel still
> > > shows
> > > "0%" when the pie slice's value is zero.
> > >
> > > What I'm trying to figure out is how to not have the "0%" be displayed and
> > > the corresponding pie slice not displayed when the pie slice's value is
> > > zero.
> > >
> > > Bob
> > >
> > >
> > > "Sandy" wrote:
> > >
> > >> Why not just filter the data (showing only >0) and use the entire
> > >> (unfiltered) range as source data.
> > >> The chart will not show hidden values - only the filtered values.
> > >> Sandy
> > >>
> > >> "Bob" <Bob(a)discussions.microsoft.com> wrote in message
> > >> news:91337249-7D87-4CCC-8B06-D1174A5AF212(a)microsoft.com...
> > >> > My data labels contain the Value and Percentage. I can hide the Value
> > >> > of
> > >> > a
> > >> > slice (when its value=0) when I set its format to ;000. But I can't
> > >> > figure
> > >> > out how to not display the slice since the data label Percentage still
> > >> > shows
> > >> > up as 0%.
> > >> > Any help would be greatly appreciated.
> > >> > Thanks,
> > >> > Bob
> > >>
> >
From: Bob on 1 Jul 2008 20:52
TJ,

Yes, I set the Custom format to '0%;;;' and that seemed to do the trick. As
far as blanking out the data label when the corresponding value is 0%, I
wrote a simple formula in the cell containing the data label:

Assume cell A1 = data label = "Widgets Sold"
Assume cell B1 = data value = 0%

In cell A1, input =IF(B1=0,"","Widgets Sold")

Bob


"TJ" wrote:

> Have you tried setting the format to Custom, with '0%;;;' (three semicolons
> following the formatting)? This is for the data labels only and will hide the
> 0% values you are referring to.
>
> I am having a similar issue that I cannot solve. I am trying to keep data
> labels that have blank values from showing up in the chart. I also would like
> to do this automatically as the data I am using can change frequently. I have
> two columns but not every row with a name in column A has a value in column
> B. The cells are blank, and those that fall into this category still show up
> in the pie chart as name-only. Because I have my formatting set as described
> above there are no 0% values showing up, but I cannot figure out how to get a
> non-value label to not show up without deleting it manually. Does anyone know
> of a way this can be done?
>
> "Bob" wrote:
>
> > Sandy,
> > Thanks. I see what you did. However, I was hoping to accomplish the same
> > thing automatically without any manual intervention (or macros).
> > Bob
> >
> >
> > "Sandy" wrote:
> >
> > > Bob
> > > I have tried setting up a Pie Chart in the way you are describing with the
> > > "Percentage" checkbox selected in Data Labels and it works fine for me
> > > (unless I am at total cross purposes).
> > > Have a look here
> > > http://www.webstruct.co.uk/webstruct/piechart/PieChart.xls
> > > I have uploaded the simple chart I tested.
> > >
> > > Sandy
> > >
> > > "Bob" <Bob(a)discussions.microsoft.com> wrote in message
> > > news:CE408F4A-F333-47A4-9014-CFBF31492AB3(a)microsoft.com...
> > > > Sandy,
> > > > Thanks for the suggestion. Unfortunately, since I have selected the
> > > > Percentage checkbox for Data Labels (which I need to show), Excel still
> > > > shows
> > > > "0%" when the pie slice's value is zero.
> > > >
> > > > What I'm trying to figure out is how to not have the "0%" be displayed and
> > > > the corresponding pie slice not displayed when the pie slice's value is
> > > > zero.
> > > >
> > > > Bob
> > > >
> > > >
> > > > "Sandy" wrote:
> > > >
> > > >> Why not just filter the data (showing only >0) and use the entire
> > > >> (unfiltered) range as source data.
> > > >> The chart will not show hidden values - only the filtered values.
> > > >> Sandy
> > > >>
> > > >> "Bob" <Bob(a)discussions.microsoft.com> wrote in message
> > > >> news:91337249-7D87-4CCC-8B06-D1174A5AF212(a)microsoft.com...
> > > >> > My data labels contain the Value and Percentage. I can hide the Value
> > > >> > of
> > > >> > a
> > > >> > slice (when its value=0) when I set its format to ;000. But I can't
> > > >> > figure
> > > >> > out how to not display the slice since the data label Percentage still
> > > >> > shows
> > > >> > up as 0%.
> > > >> > Any help would be greatly appreciated.
> > > >> > Thanks,
> > > >> > Bob
> > > >>
> > >
 | 
Pages: 1
Prev: Variable Axis Length
Next: Automatically vary range in graph depending on #rows data entered?