Working with empty cells in Excel

[jmasanganise]

Greetings everyone and compliments of the new season. I am kindly requesting for assistance. I am working with a large dataset in excel and some cells appear empty but when I use the ISBLANK function, it returns FALSE indicating that the cells are not empty. First, how do I see the contents of a seemingly empty cell? Second, I would like to create empty cells and have no idea how this is done. Third, I would like to graph the data so that the chat shows a line with gaps for the empty cells. I populated the seemingly empty cells with #N/A but Excel interpolates over the cells and does not produce gaps as required. What I want works if I manually delete the contents of the seemingly empty cells and graph the data but then deleting all such cells manually is difficult for a very large dataset. I am currently using Excel 2013. See sample data attached, column B contains the seemingly empty cells. Kindly assist. Thank you.

[AliGW]

Please downgrade your forum profile to Excel 2013.

Those cells probably contain non-breaking spaces.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[etaf]

you profile says 2016 - but this is for 2013 version

that should give gaps
have you tried using
NA()

A sample sheet would help here, WITH THE ISSUE INClUDED and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[HansDouwe]

A cell set to "" by a formula is not considered ISBLANK by Excel.

Simplest example: A1 ="" then =ISBLANK(A1) returns FALSE.

[deadlyduck]

The following could identify a 'blank looking' cell which isn't treated as blank by Excel (it returns TRUE if it meets the 2 criteria of zero length cell contents and not seen as blank by the ISBLANK function):

=AND(LEN(A1)=0,NOT(ISBLANK(A1)))

[jmasanganise]

Thanks, I have updated my profile.

[AliGW]

Any comment on any of the suggestions here?

[jmasanganise]

I thought it was good to upload a sample dataset as suggested and I have done so. Thanks.

[AliGW]

So I see - but that was after I posted!!!

And no, you should acknowledge all those who have offered insights and give feedback.

[jmasanganise]

Thanks etaf. Later versions of Excel do the work. Excel 2019 has an option for using #NA and gaps are created.

[jmasanganise]

Thanks HansDouwe. Indeed, "" is not considered blank.

[jmasanganise]

Thanks deadlyduck, the =AND(LEN(A1)=0,NOT(ISBLANK(A1))) does identify a seemingly blank cell yes!