# Can blank cells be omitted from graph?

DoubleZ
Guest
Posts: n/a

 11th Dec 2009
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
Guest
Posts: n/a

 11th Dec 2009
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

Jim Thomlinson
Guest
Posts: n/a

 11th Dec 2009
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?

מיכאל (מיקי) אבידן
Guest
Posts: n/a

 11th Dec 2009
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?

Jim Thomlinson
Guest
Posts: n/a

 11th Dec 2009
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
> .
>

Jim Thomlinson
Guest
Posts: n/a

 11th Dec 2009
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?

מיכאל (מיקי) אבידן
Guest
Posts: n/a

 11th Dec 2009
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?

DoubleZ
Guest
Posts: n/a

 11th Dec 2009
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?

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post DBB9 Microsoft Excel Worksheet Functions 2 29th Dec 2007 12:53 AM =?Utf-8?B?Sm9oblRoZVByZXNlbnRlcg==?= Microsoft Outlook Discussion 1 8th Feb 2006 02:50 PM Paul Microsoft Outlook 1 2nd May 2004 03:08 AM =?Utf-8?B?U2F2YWxvdQ==?= Microsoft Windows 2000 Security 3 12th Feb 2004 04:16 PM Rye Windows XP MovieMaker 1 25th Nov 2003 01:24 AM

Features