Skip Blanks on Line Graph or Scatter Plot (Excel 2007)

**Ctolson2344** · 01-13-2011, 05:25 PM

Hello,

I have a spreadsheet which has a lot of data on it, and several data points, plotting date on x axis, and the data on the y axis. Through the spreadsheet formulas, I have had to filter some data out, leaving many cells blank. I want lines on either a scatter plot or line graph to terminate for blanks, and start again when the data begins again. Here is what I have tried:

Blanks left in: values are plotted as 0, even when "plot blanks as gaps" is selected.

Replacing the blanks with na() values: This creates an interpolated line between the first and last data points, even when the "plot blanks as gaps" is selected

The same thing happened when I tried these options with line plots

This is only a snapshot of the data, there are a lot more points, so stopping a data series and starting it again later would not be ideal

Thanks in advance for the help

**teylyn** · 01-13-2011, 08:02 PM

Can you post a smaller file? Or zip it to reduce it?

If the blanks are the result of a formula, like =IF(condition,number,""), then the charting engine will treat them like zeros, not blanks.

You may want to look at Andy Pope's article: Broken Lines http://www.andypope.info/charts/brokenlines.htm

Looks like you have the same setup.

**Cutter** · 01-13-2011, 08:11 PM

Your "blank" cells are not blank.
If you double click on the lower border of a cell containing a value it will take you to a truly blank cell. In your case it takes you to the last row of values, skipping over all those seemingly blank cells.

Here is your file with those "blank" cells converted to actual blanks (by selecting them and hitting delete) and your graphs are as you want them.

**Ctolson2344** · 01-14-2011, 11:22 AM

Thanks both of you for the reply,

You were correct Teylyn, the data points were created using an if statement using if(___,value,""). I had copied and pasted values, thinking that the "" value would paste as an empty cell. I didn't realize that this wasn't the case, and I will just have to go into the sheets and delete all of the blank rows as Cutter suggested.

**jasperdog** · 12-18-2012, 08:36 PM

Originally Posted by Cutter

Your "blank" cells are not blank.
If you double click on the lower border of a cell containing a value it will take you to a truly blank cell. In your case it takes you to the last row of values, skipping over all those seemingly blank cells.

Here is your file with those "blank" cells converted to actual blanks (by selecting them and hitting delete) and your graphs are as you want them.

This is no solution at all when the desired blank sections of a plot are the result of complex formula calculations! You really want me to manually select a few hundred cells every time I want a gap in my plot? I can't believe that after searching the internet for hours, every "Excel expert" gives the same stupid answers that don't work, ("Use na() blah, blah, blah"). This is a serious defect in Excel that Microsoft apparently refuses to fix.

The fact is, Excel is incapable of placing gaps in line plots where data is invalid or incalculable.

**benj84** · 02-15-2013, 04:04 AM

Originally Posted by jasperdog

This is no solution at all when the desired blank sections of a plot are the result of complex formula calculations! You really want me to manually select a few hundred cells every time I want a gap in my plot? I can't believe that after searching the internet for hours, every "Excel expert" gives the same stupid answers that don't work, ("Use na() blah, blah, blah"). This is a serious defect in Excel that Microsoft apparently refuses to fix.

The fact is, Excel is incapable of placing gaps in line plots where data is invalid or incalculable.

Not true. Excel will plot a value if the value #N/A is seen, even if that value is provided from an IF formula.