Can blank cells be omitted from graph?

  • Thread starter Thread starter DoubleZ
  • Start date Start date
D

DoubleZ

I have created a graph in Excel 2007 that has 4 data series. However, in
many instances I will only have data entered for 1 or 2 of those series.
Currently, the blank series are being plotted as zero values.

Is there a way to define the data series so that if they are blank they will
be completely omitted from the graph?
 
I use a helper column and use that as the source of the graph.

The helper column contains a formula like:
=if(a2="",na(),a2)
(and drag down)

(I'd hide the column if it's irritating.)
 
Blanks are treated as zeros. Just the way it is. To get around it you need to
have the zeros turned into #N/A.

=if(B2 = 0, #N/A, B2)
 
If you hide the column be sure to uncheck

Tools -> Options -> Charts | Plot Visible Cells Only

(The option to leave blanks for gaps in the data only works for constants.
If your series is based on formulas that return blanks then your zeros will
be plotted.)

If you don't want to hide the column and just format the #N/A to not show
you can do that with conditional fomatting with a formula similar to.

=isna(B2)
 
That does not work if the data series value is the result of a formula that
returns a blank. It does work for constants by selecting Leave Gaps (which is
the default selection).

Unless you have some trick to make it work? ... I hope...
 
May I quote a part of the question:
"... I will only have data entered for 1 or 2 of those series.
Currently, the blank series are being plotted as zero values."
No one - including DoubleZ mentioned anything about values that are a result
of formulas.
I'm pretty aware of Excels limitations as well of its advantages as far as
charts are concerned.
*** Some of my replies do not match the exact question mainly because
English is not my mothers tongue - but I try my best...
Micky
 
Thanks Jim, Dave, and Micky.

I should have specified that all of the blank cells do have formulas in
them.

So, if I return #N/A it fixes the issue of graphing the data, but it still
shows the data series title and marker type of the series with blank cells.
Is there a way to have this removed automatically if the series is blank?

Thank you again for your help.
 
Back
Top