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.
Estimated reading time: 3 minutes
Table of contents
YouTube IF not blank then calculate in Excel
Watch, like, subscribe
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 commas 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
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
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
it will return 0 if it is blank!
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.