Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

# 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.

## 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

=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!

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.

Ignore zeros in average