Don't plot zero values

Discussion in 'Microsoft Excel Charting' started by Guest, Oct 21, 2004.

  1. Guest

    Guest Guest

    I am currently using Excel XP. I have a scatter chart and would only like to
    plot non zero values. These are not null values. Is there a chart setting I
    can do to skip plotting zero values or do I need to somehow filter/sort the
    data first and then plot?
    TIA
    George
     
    Guest, Oct 21, 2004
    #1
    1. Advertisements

  2. Guest

    Jon Peltier Guest

    Hey George -

    If there are true zeros in the data, perhaps the easiest way to exclude them from
    the chart is to use an autofilter on the data that hides the rows with zeros.

    Alternatively, you could insert a column to hide the zeros. Say the range with zeros
    is in B1:B10. Select C1:C10, and enter this formula into C1:

    =IF(B1=0,NA(),B1)

    Hold CTRL while pressing Enter, which enters the formula into all selected cells.
    This puts ugly #N/A errors into the sheet, but the chart doesn't plot the points.
    Hide the errors with conditional formatting, as Debra Dalgleish shows here:

    http://contextures.com/xlCondFormat03.html#Errors

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    George Wilson wrote:

    > I am currently using Excel XP. I have a scatter chart and would only like to
    > plot non zero values. These are not null values. Is there a chart setting I
    > can do to skip plotting zero values or do I need to somehow filter/sort the
    > data first and then plot?
    > TIA
    > George
     
    Jon Peltier, Oct 22, 2004
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Thank you, this is very helpful info.

    "Jon Peltier" wrote:

    > Hey George -
    >
    > If there are true zeros in the data, perhaps the easiest way to exclude them from
    > the chart is to use an autofilter on the data that hides the rows with zeros.
    >
    > Alternatively, you could insert a column to hide the zeros. Say the range with zeros
    > is in B1:B10. Select C1:C10, and enter this formula into C1:
    >
    > =IF(B1=0,NA(),B1)
    >
    > Hold CTRL while pressing Enter, which enters the formula into all selected cells.
    > This puts ugly #N/A errors into the sheet, but the chart doesn't plot the points.
    > Hide the errors with conditional formatting, as Debra Dalgleish shows here:
    >
    > http://contextures.com/xlCondFormat03.html#Errors
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    > George Wilson wrote:
    >
    > > I am currently using Excel XP. I have a scatter chart and would only like to
    > > plot non zero values. These are not null values. Is there a chart setting I
    > > can do to skip plotting zero values or do I need to somehow filter/sort the
    > > data first and then plot?
    > > TIA
    > > George

    >
    >
     
    Guest, Oct 25, 2004
    #3
    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. Diane
    Replies:
    1
    Views:
    1,498
  2. Kristin

    Don't want to plot zero values

    Kristin, May 19, 2004, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    357
    Debra Dalgleish
    May 19, 2004
  3. Guest

    How do i not plot values as zero?

    Guest, Mar 29, 2006, in forum: Microsoft Excel Charting
    Replies:
    3
    Views:
    312
    Kelly O'Day
    Mar 29, 2006
  4. Guest
    Replies:
    1
    Views:
    272
    Andy Pope
    Jun 24, 2006
  5. Re: blank cells plot as zero values

    , Jul 15, 2006, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    196
    Pete Nelson
    Jul 18, 2006
Loading...

Share This Page