If not blank then calculate in Excel

If you work with lots of data you will come across cases where you need something to happen if a cell is blank, or the opposite i.e. if not blank then calculate in Excel. Perhaps if the Excel cell is blank the formula must result in a zero, or only cells that are not blank should be calculated.

If not blank then calculate in Excel

What you want to do after finding a blank or not blank cell can be handled via a normal IF function. The key to this is how to get Excel to check for a blank.

“” for Excel to differentiate between blank and non blank

First option is to use two inverted DIRECTLY next to each other i.e. “”. Notice NO space inbetween. Remember in Excel this ( “” ) and this (” “) are different. The first one is blank. The second one contains a space, and although humans see it as blank, Excel sees it as a not blank cell.

So to test if a cell is not blank it would look something like this

=IF(A5<>””,”Not Blank”,”Blank”)

ISBLANK

There is a useful function in Excel called ISBLANK that does what it says. It looks at a cell and says True if it IS BLANK and false if it IS not BLANK.

You can use this within your IF function, so

=IF(ISBLANK(A5),”Not Blank”,”Blank”)

Cell is blank, BUT it has a ‘ in the cell

Some software exports blank cells with a ‘ in the cell (we have seen this with some Pastel systems). When you look at the cell if seems blank but if you look in the formula bar you can see the ‘ (the ‘ tells Excel to treat whatever is after as text, so useful if you enter a phone number to keep the leading zeros- in the formula bar you will see 011 849 1234 but in the cell you will see 011 849 1234).

In this case the ISBLANK does not seem to work but the “” seems to work.

For safety, perhaps you can run another check using the LEN function. LEN tells you the LENgth of the contents of a cell, so if we did

=LEN(A5)

it will return 0 if it is blank!