0 values on pie charts

[frogman7]

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

 

[Pete_UK]

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

 

[Jim Thomlinson]

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...

 

[frogman7]

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...

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

 

[Peo Sjoblom]

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

 

[Jim Thomlinson]

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

 

[frogman7]

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












- 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

 

[Pete_UK]

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

Pete

 

[frogman7]

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

Pete






- 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.

 

[Pete_UK]

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

Pete

 

[frogman7]

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

Pete





- Show quoted text -

One more little problem which may only be fixable with a macro
I have 12 team members and each on has a pie chart. Is there a way to
have AMTrix always be say blue even if it is not present in the chart
data so we can have consistant chart colors?


Bob
product Num of tickets open
Activator V4
AMTrix
Composer 1
FTPCS
Gateway 5
Gateway Interchange
Integrator 1
PassPort

Jim
Activator V4
AMTrix
Composer
FTPCS
Gateway
Gateway Interchange 10
Integrator 2

 

[Pete_UK]

I don't know of a way to do that - I think Excel allocates the colours
it uses from a sequence (which you can change), so the second visible
slice of the pie chart will be whatever the second colour in its
sequence is. Obviously in your case the second visible slice will not
always relate to the same product for each team member.

Pete