How to ignore empty cells in a chart

TerryH

New Member
Joined
Jul 1, 2003
Messages
2
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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.
 
Upvote 0
TerryH said:
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
PaddyD said:
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...
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,782
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top