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.)

DoubleZ wrote:
>
> 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?

--

Dave Peterson

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)


--
HTH...

Jim Thomlinson


"DoubleZ" wrote:

> 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?

If I am not mistaken there is a way to accomplish your request in Excel 2007:
http://office.microsoft.com/he-il/he...485551033.aspx
In former versions:
After activating (selecting) the chart you can declare NOT to present empty
cells as zeros (Tools > options > chart > empty cells with WITH INTERPULATION)
Micky


"DoubleZ" wrote:

> 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?

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)
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> 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.)
>
> DoubleZ wrote:
> >
> > 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?
>
> --
>
> Dave Peterson
> .
>

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...
--
HTH...

Jim Thomlinson


"מיכאל (מיקי) אבידן" wrote:

> If I am not mistaken there is a way to accomplish your request in Excel 2007:
> http://office.microsoft.com/he-il/he...485551033.aspx
> In former versions:
> After activating (selecting) the chart you can declare NOT to present empty
> cells as zeros (Tools > options > chart > empty cells with WITH INTERPULATION)
> Micky
>
>
> "DoubleZ" wrote:
>
> > 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?

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



"Jim Thomlinson" wrote:

> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "מיכאל (מיקי) אבידן" wrote:
>
> > If I am not mistaken there is a way to accomplish your request in Excel 2007:
> > http://office.microsoft.com/he-il/he...485551033.aspx
> > In former versions:
> > After activating (selecting) the chart you can declare NOT to present empty
> > cells as zeros (Tools > options > chart > empty cells with WITH INTERPULATION)
> > Micky
> >
> >
> > "DoubleZ" wrote:
> >
> > > 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?

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.

"Jim Thomlinson" wrote:

> 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)
>
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "DoubleZ" wrote:
>
> > 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?