Don't plot zero values

[Guest]

I am currently using Excel XP. I have a scatter chart and would only like to
plot non zero values. These are not null values. Is there a chart setting I
can do to skip plotting zero values or do I need to somehow filter/sort the
data first and then plot?
TIA
George

 

[Jon Peltier]

Hey George -

If there are true zeros in the data, perhaps the easiest way to exclude them from
the chart is to use an autofilter on the data that hides the rows with zeros.

Alternatively, you could insert a column to hide the zeros. Say the range with zeros
is in B1:B10. Select C1:C10, and enter this formula into C1:

=IF(B1=0,NA(),B1)

Hold CTRL while pressing Enter, which enters the formula into all selected cells.
This puts ugly #N/A errors into the sheet, but the chart doesn't plot the points.
Hide the errors with conditional formatting, as Debra Dalgleish shows here:

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

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

 

[Guest]

Thank you, this is very helpful info.

Hey George -

If there are true zeros in the data, perhaps the easiest way to exclude them from
the chart is to use an autofilter on the data that hides the rows with zeros.

Alternatively, you could insert a column to hide the zeros. Say the range with zeros
is in B1:B10. Select C1:C10, and enter this formula into C1:

=IF(B1=0,NA(),B1)

Hold CTRL while pressing Enter, which enters the formula into all selected cells.
This puts ugly #N/A errors into the sheet, but the chart doesn't plot the points.
Hide the errors with conditional formatting, as Debra Dalgleish shows here:

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

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