Charts: Deleting zero values


Posted by jssp on July 23, 2001 10:38 AM


Hello everyone,

Is there an option in Excel so that, for a given graph,
it skips (does not show in the graph) any value that is
zero?

Another question:

Is it possible that it skips this point even in
the scale?

For example, if I'm graphing values for a Date, if nothing
happened on July 22 (value =0), how do I ask it to not even show this
day on the scale?

Thanks in advance for all your help!



Posted by Mark W. on July 23, 2001 2:33 PM

> Is there an option...[that] skips any value is zero?

Don't chart the value containing zeroes. Instead,
chart a transform created using the formula,
=IF(B2,B2,#N/A), where B2 is a reference to the
first original values. Copy this formula down
to transform all of the original values.

> Is it possible that it skips this point even in
> the scale?

Since only filtered rows will be plotted you could
dispense with the transformation described above
and apply an AutoFilter that excludes records whose
value equal 0. But, keep in mind that numeric
date values will be interpeted as such and depending
on your X-axis scale settings AutoFiltering may not
prevent a given date from appearing as an X-axis label.
If your X-axis contained categories (as would be the
case if you converted your dates to text values) then
you wouldn't see your excluded date value under any
circumstance; however, your X-axis labels would
no longer be a time series.