PC Review


Reply
Thread Tools Rate Thread

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?
 
Reply With Quote
 
 
 
 
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
 
Reply With Quote
 
 
 
 
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?

 
Reply With Quote
 
מיכאל (מיקי) אבידן
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?

 
Reply With Quote
 
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
> .
>

 
Reply With Quote
 
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?

 
Reply With Quote
 
מיכאל (מיקי) אבידן
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?

 
Reply With Quote
 
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?

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can surplus decimals be omitted when adding derived figures? DBB9 Microsoft Excel Worksheet Functions 2 29th Dec 2007 12:53 AM
Can I Detect When I have Omitted an Attachment? =?Utf-8?B?Sm9oblRoZVByZXNlbnRlcg==?= Microsoft Outlook Discussion 1 8th Feb 2006 02:50 PM
How to create a task view where completed tasks are omitted Paul Microsoft Outlook 1 2nd May 2004 03:08 AM
Which critical updates can be omitted if I'm behind a firewall? =?Utf-8?B?U2F2YWxvdQ==?= Microsoft Windows 2000 Security 3 12th Feb 2004 04:16 PM
Scenes omitted during render Rye Windows XP MovieMaker 1 25th Nov 2003 01:24 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:40 AM.