+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

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

    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
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

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

    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.

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

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

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

  4. #4
    Registered User
    Join Date
    01-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

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

    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.

  5. #5
    Registered User
    Join Date
    12-18-2012
    Location
    san diego
    MS-Off Ver
    Excel 2007
    Posts
    1

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

    Quote Originally Posted by Cutter View Post
    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.

  6. #6
    Registered User
    Join Date
    02-15-2013
    Location
    Home
    MS-Off Ver
    Excel 2010
    Posts
    1

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

    Quote Originally Posted by jasperdog View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1