XL: Hide chart labels if value Zero 3

[xlhelp]

Hi.

I haven't found a way yet. I have four stack column chart where some of the values in the stack are Zero. I wish to label the stacks with Series name, but want to hide the label if the value is Zero.

Anyone ever come across something like this?

Thanks

Member- AAAA Association Against Acronym Abusers

 

[kjv1611]

I've not done it, but I would think something like that could be done if you built the chart on the fly using VBA.

'Course that don't mean it'd be easy, either.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

Untested

dim sc as seriescollection, pt as point, i as integer
with activechart
  for each sc in .seriescollection
    i = 1
    for each pt in sc.points
      with pt.datalabel
'you might need to also index the sereiscollection to get the proper range
         if Range("YourDataRange")(i) = 0 then
            .showvalue=false
         else
            .showvalue=true
         end if
      end with
      i = i + 1
    next
  next
end with

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[AnotherHiggins]

I don't suppose it would be acceptable to just do a Find/Replace to get rid of all the zero values and replace them will nulls?

That's the only way I can think to make the chart look the way you want without VBA.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlhelp]

Thanks everyone.

John, I had tried the null part, the label still shows up.

Skip, on initial try the code didn't work. I need to debug it. But, I'll have to do that at leisure, right now I am under the gun. Thanks

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Wait - if you don't mind the gaps in your data set, replacing zeros with nulls will work.

Make sure you have the offending chart visible, then go to Tools > Options > Chart and under the Active Chart section, change Plot empty cells as to Not plotted (leave gaps).

Viola!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

That will work!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlhelp]

Wait - if you don't mind the gaps in your data set, replacing zeros with nulls will work.

You are right, it does...if it's a column chart. It doesn't work for stacked columns. Just tried it again with Null, #N/A, custom formatting etc. Sigh....

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

I just tried it before that last post to make sure, and it works for me - provided that you make the changes outlined in my last post.

What version of Excel are you running?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlhelp]

Hmmmm.... works for you, but not for mois....may be something else needs to be tweaked?

XL 2003 SP2

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

2003 SP3 here, but I just confirmed this on a machine with Excel 2000 as well.

Just for clarification:

You can see that the blank doesn't have a label on the chart, but the zero does.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlhelp]

I have all the right check marks etc and I don't get the results you get. I can't post an image since I am not allowed to access sites like imageshack while connected to the network. And Iam not in a position to disconnect just yet. This is weird. Anyway I had a 4 pm ET deadline, so I had to send the file with the legend showing, rather than labels. They are happy.... I am not. I will investigate this further.

Thanks so much for your efforts, I am truly grateful.

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

I'm sorry that didn't work for you. Strange....

I'll poke around and see if I can find any other settings that might affect this.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

It must ACTUALLY be an EMPTY cell. A Formula that returns "" will not work; ANY formula.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[kjv1611]

So what formula could you use to return a truly BLANK cell? I've always wondered that myself, b/c of other quirks like that. Is there such a formula?

--

"If to err is human, then I must be some kind of human!" -Me

 

[xlbo]

No formula can ever return a blank cell as it has the formula in it and the best you can return is ""

Only way to do it is to run a routine to clear the contents of any cell that has len > 0

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlhelp]

One more deep s i g h......

This explains it:

http://support.microsoft.com/kb/215854

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

It's a FEATURE!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}