CODEWITHSUNDEEP
excelgoogle-sheets
poxxo
poxxo

Reputation: 31

how can I test whether a cell is blank or actually a reference to a blank cell in Excel or Google Sheets?

I have a large spreadsheet that's a mix of data cells, empty cells and cells referencing another sheet that also has a lot of empty cells.

I need to be able to distinguish between a "really" empty cell -- nothing in it -- and a reference to an empty cell in the other sheet.

I've tried a dozen things, but I can't find anything that doesn't treat the two as the same. I'm hoping to find something that works in both Excel and Google Sheets, but I'd be very happy if I find something that works in either one.

Upvotes: 2

Views: 353

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

to find truly empty in Excel:

=ISBLANK(A1)

Which will only return true if their is nothing, no formula or constants.

The you can use:

=A1=""

To test if it returning a blank.

So:

=IF(ISBLANK(A1),"NOTHING",IF(A1="","FORMULA RETURNING BLANK","VALUE"))

Note the order of operation here is important as A1="" will return TRUE if the cell is truly blank also. So we want to rule out the first before asking the second.

A2 is blank and A3 has the formula =""

enter image description here

Upvotes: 2

Related Questions