Manipulating Excel Chart labels - Zero vs. N/A

[Anthea]

Please could someone give me some help

I need to make the label on an Excel chart show something other than the value.

Example .... the value is zero but the zero is actually a N/A. How do I make the data label show N/A without having to physically edit each and every one and then turn it back the next time the data changes?

 

[GlennUK]

Would having a second range that refers to the actual data be possible? You could an IF function there to convert 0 to N/A.

 

[Anthea]

Thanks! that is what I worked out after I had posted yesterday ... I have made each data label pick up the correct cell.

 

[GlennUK]

That's great!

 

[Anthea]

Having got that to work for Column and line graphs .... how do I get it so work in a Data Table attached to a chart?