Results 1 to 10 of 10

How to ignore empty cells in a chart

This is a discussion on How to ignore empty cells in a chart within the Excel Questions forums, part of the Question Forums category; If I have a row of data that I want to line graph but I have random blank cells in ...

  1. #1
    New Member
    Join Date
    Jul 2003
    Posts
    2

    Default How to ignore empty cells in a chart

    If I have a row of data that I want to line graph but I have random blank cells in this data how can I tell the graph to ignore these empty cells?

    Thanks.

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: How to ignore empty cells in a chart

    Using Excel XP.

    1. Click the chart to highlight it.
    2. Go to the Tools menu | Options | Charts.
    3. Click the radio button for “Plot empty cells as Not Plotted (leave gaps)”.

    Regards,

    Mike

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: How to ignore empty cells in a chart

    =IF(ISBLANK(ref),#N/A,ref)

  4. #4
    New Member
    Join Date
    Jul 2003
    Posts
    2

    Default Re: How to ignore empty cells in a chart

    I tried the =IF(ISBLANK( ref ),#N/A, ref ) in the Source Data Series Values

    and it states Function is not valid. I also tried the Tools-Options-Chart-Plot Empty cells as not plotted but I still get points thta drop to zero when the cells is blank.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: How to ignore empty cells in a chart

    Quote Originally Posted by TerryH
    I tried the =IF(ISBLANK( ref ),#N/A, ref ) in the Source Data Series Values

    and it states Function is not valid. I also tried the Tools-Options-Chart-Plot Empty cells as not plotted but I still get points thta drop to zero when the cells is blank.
    Are your cells truly blank or do they contain the empty text string, ""? The formula...

    =IF(ISBLANK(ref),#N/A,ref)

    ...should be used in a worksheet cell -- not directly in your chart's SERIES function. Replace ref with an appropriate cell reference.

  6. #6
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: How to ignore empty cells in a chart

    Terry,

    I tested my answer with the following data (B5:C16), using a line chart:

    Jan 100
    Feb 125
    Mar
    Apr 130
    May 110
    Jun 115
    Jul
    Aug 125
    Sep 140
    Oct 135
    Nov 140
    Dec

    Worked perfectly - points do not drop to zero when the cells are blank. Did you highlight the chart when you clicked the radio button for “Plot empty cells as Not Plotted (leave gaps)”?

    Regards,

    Mike

  7. #7
    New Member
    Join Date
    Jun 2004
    Posts
    4

    Default Re: How to ignore empty cells in a chart

    Here’s my dilemma... I’m creating a column chart of a data range that changes from time to time. Within that range there will be blank cells (#N/A) from time to time. The chart still creates labels for the #N/A rows, even though there is no data. I’ve tried “” and #N/A, but still the graph plots them. I’ve also tried “tools > options > chart > plot empty cells as” with no luck.

    As an example of what I’m getting create the follow data (labels in column A and values in column B):

    blue 1276
    red 1245
    orange 1189

    green 1304

    Highlight the 5 rows and two columns and press F11. Note how the blank row is still plotted.

    Alternatively is there a way to plot the following data set without displaying "pink" as it has no data against it.

    blue 1276
    red 1245
    orange 1189
    pink
    green 1304

    Any ideas?

    Thanks in advance,
    Jason

  8. #8
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,946

    Default

    filter the column to get rid of blanks - default is to plot visible cells only

  9. #9
    New Member
    Join Date
    Jun 2004
    Posts
    4

    Default

    Quote Originally Posted by PaddyD
    filter the column to get rid of blanks - default is to plot visible cells only
    Using autofilter to remove the blanks works a treat - however I have to manually remove and replace the filter to make the graph update when the dataset changes...

  10. #10
    New Member
    Join Date
    Jun 2004
    Posts
    4

    Default Re: How to ignore empty cells in a chart

    Found a workable solution.

    First eliminate blanks from the list using :
    http://www.cpearson.com/excel/noblanks.htm

    Then plot a chart using dynamic ranges:
    http://www.tushar-mehta.com/excel/ne...ynamic_charts/

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com