hiding zero values

  • Thread starter Thread starter Geo Siggy
  • Start date Start date
G

Geo Siggy

Hi,

how to hide zero and empty values in a chart (x-y-plot), and how t
program it in vba.

Sigg
 
Method 1: rather than empty cells use =NA() - the cell will be ignored in
the chart
Method 2: click on chart which has empty cells in its data; use
Tools|Options and Open Chart tab, now specify how blank cells are to be
handled. You could record a macro to find the VBA code to do this.
There is nothing you can do to have 0 treated as anything else.

Best wishes
Bernard
 
True blanks won't show up. If you're making a cell look blank by using
"" in a formula, that will plot as a zero. Excel's XY charts ignore
#N/A, so use this:

=IF(A1=0,NA(),A1)

This is also how you skip a zero, by converting it to #N/A.

- Jon
 
Unfortunately, Excel has no BLANK() or NULL() worksheet function. The
NA() is helpful to prevent a missing point from causing the plotted line
to dive to zero, but it won't give you a gap between plotted points.

Andy Pope (http://andypope.info) and Tushar Mehta
(http://tushar-mehta.com) both have workarounds to this issue.

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