Not charting empty cells

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a row of figures (that are calculated by formulae)
to act as the x axis, but for certain conditions, I want
some of the figures to show blank ie "". Is there any
way these figures could be completely ignored within the
data source (ie not plotted at all) without having to
specify the data source again??

Thanks in advance for any comments.
 
Chris -

The problem is, "" isn't a blank after all. It's a short text string,
and Excel treats it as zero, like all text strings. Change "" in the
formula to NA(), and Excel will change those nonblanks into #N/A errors,
which might look unpleasant in the sheet, but which aren't charted. You
don't have a choice to leave a gap in the #N/A occurs between values,
Excel will only interpolate the line to connect the points on either
side of the #N/A.

To hide the #N/A errors on the worksheet, you can use conditional
formatting. There are instructions on Debra Dalgleish's web site:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top