Can blank cells be omitted from graph?

Discussion in 'Microsoft Excel Misc' started by DoubleZ, Dec 11, 2009.

  1. DoubleZ

    DoubleZ Guest

    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, Dec 11, 2009
    #1
    1. Advertisements

  2. 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
     
    Dave Peterson, Dec 11, 2009
    #2
    1. Advertisements

  3. 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?
     
    Jim Thomlinson, Dec 11, 2009
    #3
  4. If I am not mistaken there is a way to accomplish your request in Excel 2007:
    http://office.microsoft.com/he-il/help/HA100485551033.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?
     
    מיכ×ל (מיקי) ×בידן, Dec 11, 2009
    #4
  5. 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, Dec 11, 2009
    #5
  6. 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/help/HA100485551033.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, Dec 11, 2009
    #6
  7. 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/help/HA100485551033.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?
     
    מיכ×ל (מיקי) ×בידן, Dec 11, 2009
    #7
  8. DoubleZ

    DoubleZ Guest

    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?
     
    DoubleZ, Dec 11, 2009
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dennis
    Replies:
    8
    Views:
    911
    Maistrye
    Jul 20, 2006
  2. Guest
    Replies:
    0
    Views:
    268
    Guest
    Aug 14, 2006
  3. Guest

    Zeroes omitted from the end of imported currency

    Guest, Aug 17, 2007, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    166
    David Biddulph
    Aug 17, 2007
  4. Guest

    How to display figures with thousand omitted

    Guest, Aug 31, 2007, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    283
    Guest
    Sep 1, 2007
  5. BRB

    Bar graph and stacked bar graph on same graph

    BRB, Aug 22, 2008, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    462
    akphidelt
    Aug 22, 2008
Loading...

Share This Page