How to ignore empty cells in a chart

[TerryH]

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.

 

[Ekim]

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

 

[Mark W.]

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

 

[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.

 

[Mark W.]

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.

 

[Ekim]

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

 

[jasestu]

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

 

[PaddyD]

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

 

[jasestu]

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...

 

[jasestu]

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/newsgroups/dynamic_charts/