Share via

dont graph blanks

Anonymous
2014-05-20T20:32:57+00:00

Good Day

I am working in Excel from Office 365.  I have a series of numbers that I want to plot on a line graph

Column 1          Column 2

5                         

10

15

20

25

30                   

                              35

                              40

                              45

I dont want to plot the last 3 blank values from Col 1 nor the first 6 blank values from Col 2.  

The cells are calculated using a formula.  I have tried making the formula return a "" or an #N/A.  Those get plotted as a zero.

I have the graph set to show empty cells as gaps. Excel is still plotting them as zeros. 

Any help would be greatly appreciated.

John

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2014-05-21T02:59:08+00:00

    If #NA approach is not working (which should work), you may try following approach -

    1. Right Click on Chart and take Select Data.

    1. Click on Hidden and Empty Cells.

    1. Here, you can control the behaviour. You current behavior is 0. I think what you want is a behavior like "Gaps".

    Was this answer helpful?

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    2014-05-20T22:51:13+00:00

    Hello,

    What do you mean when you say you run Office 365?

    Office 365 is about licensing and sales channel.  The real question is not what license you use, but where you run the Excel file.  

    Do you run Excel on your desktop?  

    Do you run the file in the browser?

    Here is a screenshot of the data and the chart working correctly in Excel 2013. Whether you pay for your Office license with a 365 subscription or whether you bought the boxed version does not make a difference. 

    I suspect that the trouble is with your file. 

    Can you please share the file on your OneDrive public folder and post a link here?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-05-20T21:44:19+00:00

    No, I'm sorry I am using Office 2010.  Hopefully someone using 365 will respond...

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-05-20T21:34:15+00:00

    Eric

    Thanks, yes I am using the NA() function just as you laid out in your sample formula. Are you using the version of excel that comes with MS Office 365? Does it work for you?

    Thanks

    John

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-05-20T21:04:58+00:00

    Are you sure you are using the NA() function correctly?  Your formula should look something like this:

    =IF(X21-Z33=0,NA(),X21-Z33)

    Note the use of the function NA().  This function returns the error "#N/A".  Excel will skip cells with that error in them.  You cannot simply enter "#N/A" in a cell because Excel interprets that as a text string.

    Hope this helps,

    Eric

    Was this answer helpful?

    0 comments