+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Chart showing wrong if cell is blank

  1. #1
    Registered User
    Join Date
    05-23-2011
    Location
    Dammam
    MS-Off Ver
    Excel 2003
    Posts
    7

    Chart showing wrong if cell is blank

    My question is:
    In one sheet I'm entering some date data in a1:a10. these cells are linked to another cells F1:F10 and made a line chart using the range F1:F10. But when a cell becomes blank in the data range (a1:a10) that cell data becoming as zero and corresponding date value is taken and chart shows wrong result. How can I keep the cell blank and chart shows a gap only while there's no data for an item?

    Sherif Mannur

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Chart showing wrong if cell is blank

    There is an option on the Select Data Source screen called Hidden and Empty Cells. When you click it, it brings up a dialog showing different options for empty cells. It sounds like you want Gaps or Connect data points with line.

    Um... not sure if this is different on Excel 03.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Registered User
    Join Date
    05-23-2011
    Location
    Dammam
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Chart showing wrong if cell is blank

    Quote Originally Posted by sherifpv View Post
    My question is:
    In one sheet I'm entering some date data in a1:a10. these cells are linked to another cells F1:F10 and made a line chart using the range F1:F10. But when a cell becomes blank in the data range (a1:a10) that cell data becoming as zero and corresponding date value is taken and chart shows wrong result. How can I keep the cell blank and chart shows a gap only while there's no data for an item?

    Sherif Mannur
    I tried with the options in select data source message box, but it doesn't work with the linked data.

  4. #4
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Chart showing wrong if cell is blank

    Ok, can you use a formula in your linked cells where if the cell being linked from = 0, show "" (empty string), otherwise show the linked cell's data?

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Chart showing wrong if cell is blank

    If you series is based on cells containing formula you will not be able to break the line.
    Best you can do is suppress the data marker on a line chart and truncate the ends of the lines when multiple cells are 'empty'

    In order to do this the formula must return #N/A rather than "", as this will be treated as text and all text is plotted as zero.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    05-23-2011
    Location
    Dammam
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Chart showing wrong if cell is blank

    Quote Originally Posted by Andy Pope View Post
    If you series is based on cells containing formula you will not be able to break the line.
    Best you can do is suppress the data marker on a line chart and truncate the ends of the lines when multiple cells are 'empty'

    In order to do this the formula must return #N/A rather than "", as this will be treated as text and all text is plotted as zero.
    I tried all methods, nothing works. Any solution please?

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Chart showing wrong if cell is blank

    There are no methods to create a gap in a line where the data source is formula.

    This may help is your data fits.
    http://www.andypope.info/charts/brokenlines.htm

    Otherwise the only approach is to remove the 'empty' cells content via VBA

+ 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