I am working in Excel 2007 and have many pie charts. I have 2 issues
I would like help on.

1. I want the values of the data to display on the pie chart but this
puts 0 for all the 0 values in the data. This might be fixed if I can
figure how to get question 2 to work.

2. I set up a column beside each data column for the values that
don't have 0 in them to display =IF(C150<>0,A150,"") This take the
text off of the chart but leaves the color swatch.

Thanks for the help

You can apply autofilter just to cover the range of your data (plus
heading). Assume this is C149:C160, then highlight this range and Data
| Filter | Autofilter (check). Now from the filter pull-down select
Custom... and set it to Does Not Equal then 0 (zero). This will hide
the rows with zeros in, and consequently the Legend entries for those
items.

Hope this helps.

Pete

On Aug 29, 2:30 pm, frogman7 <frogm...@googlemail.com> wrote:
> I am working in Excel 2007 and have many pie charts. I have 2 issues
> I would like help on.
>
> 1. I want the values of the data to display on the pie chart but this
> puts 0 for all the 0 values in the data. This might be fixed if I can
> figure how to get question 2 to work.
>
> 2. I set up a column beside each data column for the values that
> don't have 0 in them to display =IF(C150<>0,A150,"") This take the
> text off of the chart but leaves the color swatch.
>
> Thanks for the help

Change your formula to
=IF(C150<>0,A150,#N/A)

Now you can use conditional formatting to hide the #N/A's if you want to...
--
HTH...

Jim Thomlinson


"frogman7" wrote:

> I am working in Excel 2007 and have many pie charts. I have 2 issues
> I would like help on.
>
> 1. I want the values of the data to display on the pie chart but this
> puts 0 for all the 0 values in the data. This might be fixed if I can
> figure how to get question 2 to work.
>
> 2. I set up a column beside each data column for the values that
> don't have 0 in them to display =IF(C150<>0,A150,"") This take the
> text off of the chart but leaves the color swatch.
>
> Thanks for the help
>
>

On Aug 29, 8:32 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:
> Change your formula to
> =IF(C150<>0,A150,#N/A)
>
> Now you can use conditional formatting to hide the #N/A's if you want to...
> --
> HTH...
>
> Jim Thomlinson


I keep seeing that you can hide items using conditional formatting but
all I see is changing color or basic stuff. How do I hide cells based
on conditional formatting.
My data is complex:

BILL BOB JOHN

Activator 4


Composer 1
Composer 1

Gateway 3 Gateway 7
Gateway 12
Integrator 2
PassPort 3
Sentinel 5




CFT 5

If I hide cells will it mess up the data

You can "hide" an error by selecting white fonts thus making it impossible
to see the value in the cell itself (of course in the formula bar it will
still be visible), That was what Jim meant

You need a macro to hide cells


--
Regards,

Peo Sjoblom



"frogman7" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Aug 29, 8:32 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
> This-.com> wrote:
>> Change your formula to
>> =IF(C150<>0,A150,#N/A)
>>
>> Now you can use conditional formatting to hide the #N/A's if you want
>> to...
>> --
>> HTH...
>>
>> Jim Thomlinson
>
>
> I keep seeing that you can hide items using conditional formatting but
> all I see is changing color or basic stuff. How do I hide cells based
> on conditional formatting.
> My data is complex:
>
> BILL BOB JOHN
>
> Activator 4
>
>
> Composer 1
> Composer 1
>
> Gateway 3 Gateway 7
> Gateway 12
> Integrator 2
> PassPort 3
> Sentinel 5
>
>
>
>
> CFT 5
>
> If I hide cells will it mess up the data
>
>
>
>

I was (in my mind anyway) alluding to using conditional formatting to hde
error values. Check out this link...

http://www.contextures.com/xlCondFormat03.html#Errors
--
HTH...

Jim Thomlinson


"frogman7" wrote:

> On Aug 29, 8:32 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
> This-.com> wrote:
> > Change your formula to
> > =IF(C150<>0,A150,#N/A)
> >
> > Now you can use conditional formatting to hide the #N/A's if you want to...
> > --
> > HTH...
> >
> > Jim Thomlinson
>
>
> I keep seeing that you can hide items using conditional formatting but
> all I see is changing color or basic stuff. How do I hide cells based
> on conditional formatting.
> My data is complex:
>
> BILL BOB JOHN
>
> Activator 4
>
>
> Composer 1
> Composer 1
>
> Gateway 3 Gateway 7
> Gateway 12
> Integrator 2
> PassPort 3
> Sentinel 5
>
>
>
>
> CFT 5
>
> If I hide cells will it mess up the data
>
>
>
>
>

On Aug 29, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:
> I was (in my mind anyway) alluding to using conditional formatting to hde
> error values. Check out this link...
>
> http://www.contextures.com/xlCondFormat03.html#Errors
> --
> HTH...
>
> Jim Thomlinson
>
>
>
> "frogman7" wrote:
> > On Aug 29, 8:32 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
> > This-.com> wrote:
> > > Change your formula to
> > > =IF(C150<>0,A150,#N/A)
>
> > > Now you can use conditional formatting to hide the #N/A's if you want to...
> > > --
> > > HTH...
>
> > > Jim Thomlinson
>
> > I keep seeing that you can hide items using conditional formatting but
> > all I see is changing color or basic stuff. How do I hide cells based
> > on conditional formatting.
> > My data is complex:
>
> > BILL BOB JOHN
>
> > Activator 4
>
> > Composer 1
> > Composer 1
>
> > Gateway 3 Gateway 7
> > Gateway 12
> > Integrator 2
> > PassPort 3
> > Sentinel 5
>
> > CFT 5
>
> > If I hide cells will it mess up the data- Hide quoted text -
>
> - Show quoted text -

Now that conditional formatting is out is there a way to display the
values on the pie chart but if the value is 0 don not display the
category or 0 value on the pie chart. I found something earlier but
don't quite understand how it works because it uses range names. I
know that range name can be created but usually when you select the
range it diplays the range name in the upper left hand corner where
the cell name is displayed by default. This is the example I found
http://www.andypope.info/charts/piezeros.htm if someone could help me
understand what and how Excel is doing that would help me a lot. I am
also researching this on my own and will post my finding.

Thanks
Ken

You seem to have overlooked my earlier posting - have you tried it? It
works for me.

Pete

On Aug 30, 3:41 pm, frogman7 <frogm...@googlemail.com> wrote:
> On Aug 29, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
>
>
>
>
>
> This-.com> wrote:
> > I was (in my mind anyway) alluding to using conditional formatting to hde
> > error values. Check out this link...
>
> >http://www.contextures.com/xlCondFormat03.html#Errors
> > --
> > HTH...
>
> > Jim Thomlinson
>
> > "frogman7" wrote:
> > > On Aug 29, 8:32 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
> > > This-.com> wrote:
> > > > Change your formula to
> > > > =IF(C150<>0,A150,#N/A)
>
> > > > Now you can use conditional formatting to hide the #N/A's if you want to...
> > > > --
> > > > HTH...
>
> > > > Jim Thomlinson
>
> > > I keep seeing that you can hide items using conditional formatting but
> > > all I see is changing color or basic stuff. How do I hide cells based
> > > on conditional formatting.
> > > My data is complex:
>
> > > BILL BOB JOHN
>
> > > Activator 4
>
> > > Composer 1
> > > Composer 1
>
> > > Gateway 3 Gateway 7
> > > Gateway 12
> > > Integrator 2
> > > PassPort 3
> > > Sentinel 5
>
> > > CFT 5
>
> > > If I hide cells will it mess up the data- Hide quoted text -
>
> > - Show quoted text -
>
> Now that conditional formatting is out is there a way to display the
> values on the pie chart but if the value is 0 don not display the
> category or 0 value on the pie chart. I found something earlier but
> don't quite understand how it works because it uses range names. I
> know that range name can be created but usually when you select the
> range it diplays the range name in the upper left hand corner where
> the cell name is displayed by default. This is the example I foundhttp://www.andypope.info/charts/piezeros.htmif someone could help me
> understand what and how Excel is doing that would help me a lot. I am
> also researching this on my own and will post my finding.
>
> Thanks
> Ken- Hide quoted text -
>
> - Show quoted text -

On Aug 30, 8:14 am, Pete_UK <pashu...@auditel.net> wrote:
> You seem to have overlooked my earlier posting - have you tried it? It
> works for me.
>
> Pete
>
> On Aug 30, 3:41 pm, frogman7 <frogm...@googlemail.com> wrote:
>
>
>
> > On Aug 29, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
>
> > This-.com> wrote:
> > > I was (in my mind anyway) alluding to using conditional formatting to hde
> > > error values. Check out this link...
>
> > >http://www.contextures.com/xlCondFormat03.html#Errors
> > > --
> > > HTH...
>
> > > Jim Thomlinson
>
> > > "frogman7" wrote:
> > > > On Aug 29, 8:32 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
> > > > This-.com> wrote:
> > > > > Change your formula to
> > > > > =IF(C150<>0,A150,#N/A)
>
> > > > > Now you can use conditional formatting to hide the #N/A's if you want to...
> > > > > --
> > > > > HTH...
>
> > > > > Jim Thomlinson
>
> > > > I keep seeing that you can hide items using conditional formatting but
> > > > all I see is changing color or basic stuff. How do I hide cells based
> > > > on conditional formatting.
> > > > My data is complex:
>
> > > > BILL BOB JOHN
>
> > > > Activator 4
>
> > > > Composer 1
> > > > Composer 1
>
> > > > Gateway 3 Gateway 7
> > > > Gateway 12
> > > > Integrator 2
> > > > PassPort 3
> > > > Sentinel 5
>
> > > > CFT 5
>
> > > > If I hide cells will it mess up the data- Hide quoted text -
>
> > > - Show quoted text -
>
> > Now that conditional formatting is out is there a way to display the
> > values on the pie chart but if the value is 0 don not display the
> > category or 0 value on the pie chart. I found something earlier but
> > don't quite understand how it works because it uses range names. I
> > know that range name can be created but usually when you select the
> > range it diplays the range name in the upper left hand corner where
> > the cell name is displayed by default. This is the example I foundhttp://www.andypope.info/charts/piezeros.htmifsomeone could help me
> > understand what and how Excel is doing that would help me a lot. I am
> > also researching this on my own and will post my finding.
>
> > Thanks
> > Ken- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

I had to rearrange most of my data to use this method but this seems
the easiest to do. I thank you for all your help. A quick note: If
you want to use this method on several pieces of data you will have to
put them in the same column and run the filter on all of them as you
can only have one filter on a column.

Thanks for feeding back - glad you got it to work.

Pete

On Aug 30, 6:51 pm, frogman7 <frogm...@googlemail.com> wrote:
> On Aug 30, 8:14 am, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
>
>
> > You seem to have overlooked my earlier posting - have you tried it? It
> > works for me.
>
> > Pete
>
> > On Aug 30, 3:41 pm, frogman7 <frogm...@googlemail.com> wrote:
>
> > > On Aug 29, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
>
> > > This-.com> wrote:
> > > > I was (in my mind anyway) alluding to using conditional formatting to hde
> > > > error values. Check out this link...
>
> > > >http://www.contextures.com/xlCondFormat03.html#Errors
> > > > --
> > > > HTH...
>
> > > > Jim Thomlinson
>
> > > > "frogman7" wrote:
> > > > > On Aug 29, 8:32 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
> > > > > This-.com> wrote:
> > > > > > Change your formula to
> > > > > > =IF(C150<>0,A150,#N/A)
>
> > > > > > Now you can use conditional formatting to hide the #N/A's if you want to...
> > > > > > --
> > > > > > HTH...
>
> > > > > > Jim Thomlinson
>
> > > > > I keep seeing that you can hide items using conditional formatting but
> > > > > all I see is changing color or basic stuff. How do I hide cells based
> > > > > on conditional formatting.
> > > > > My data is complex:
>
> > > > > BILL BOB JOHN
>
> > > > > Activator 4
>
> > > > > Composer 1
> > > > > Composer 1
>
> > > > > Gateway 3 Gateway 7
> > > > > Gateway 12
> > > > > Integrator 2
> > > > > PassPort 3
> > > > > Sentinel 5
>
> > > > > CFT 5
>
> > > > > If I hide cells will it mess up the data- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Now that conditional formatting is out is there a way to display the
> > > values on the pie chart but if the value is 0 don not display the
> > > category or 0 value on the pie chart. I found something earlier but
> > > don't quite understand how it works because it uses range names. I
> > > know that range name can be created but usually when you select the
> > > range it diplays the range name in the upper left hand corner where
> > > the cell name is displayed by default. This is the example I foundhttp://www.andypope.info/charts/piezeros.htmifsomeonecould help me
> > > understand what and how Excel is doing that would help me a lot. I am
> > > also researching this on my own and will post my finding.
>
> > > Thanks
> > > Ken- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> I had to rearrange most of my data to use this method but this seems
> the easiest to do. I thank you for all your help. A quick note: If
> you want to use this method on several pieces of data you will have to
> put them in the same column and run the filter on all of them as you
> can only have one filter on a column.- Hide quoted text -
>
> - Show quoted text -