+ Reply to Thread
Results 1 to 9 of 9

Skip blank cells in chart data

  1. #1
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Skip blank cells in chart data

    Hi,

    I have a column of data that I want to display as a chart. However, there are some blank cells in the column. When I use a simple line chart, the chart drops the line all the way down to zero for the blank cells.

    If the blank cell is B4 in column B, is it possible to make excel ignore that cell and connect B3 and B5 with a straight line?
    Last edited by mehulkar; 01-29-2011 at 05:54 PM.

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

    Re: Skip blank cells in chart data

    Assuming that "blank" cell is actually a "" result from a formula in that cell - change your formula to show NA() instead of "".

    If that cell is actually blank you can tell Excel to ignore blank cells.

  3. #3
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Skip blank cells in chart data

    Quote Originally Posted by Cutter View Post
    Assuming that "blank" cell is actually a "" result from a formula in that cell - change your formula to show NA() instead of "".

    If that cell is actually blank you can tell Excel to ignore blank cells.
    Thanks for the quick reply. How do I tell Excel to ignore blank cells?

    The cells are not a result of a formula. they are data points manually entered.

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

    Re: Skip blank cells in chart data

    Right click within the chart area and then click Select Data

    Then click on the Hidden and Empty Cells button

    Click on Connect data points with line

    Click OK, then OK again

  5. #5
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Skip blank cells in chart data

    Thank you!!

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

    Re: Skip blank cells in chart data

    You're welcome.

    Don't forget to mark your thread as SOLVED.

    Click on the FAQ button at top of forum page for directions.

  7. #7
    Forum Contributor
    Join Date
    05-29-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    143

    Re: Skip blank cells in chart data

    this didn't seem to work for me.. it does nothing when i click on Connect data points with line

  8. #8
    Registered User
    Join Date
    07-26-2015
    Location
    tehran
    MS-Off Ver
    2013
    Posts
    1

    Re: Skip blank cells in chart data

    hi
    i had the same problem with excluding null and Na() cells from a chart. but when i am trying to use the solution above i cannot check the "connect data points with line" it is greyed out and not available to choose.
    how can i solve the problem??
    Last edited by azor ahai; 07-26-2015 at 02:44 AM.

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Skip blank cells in chart data

    azor ahai,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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