ISBLANK Function
Evaluates a cell to check if it has any information or is empty.
The ISBLANK function in Excel evaluates a cell to check if it has any information or is empty.
Blank cells can be a nuisance if you work on a large dataset. This is why we have a few functions that can identify and take care of those nuisances for you.
The COUNTBLANK function checks the number of blank cells in a row or a referenced column. So if we have a function that identifies empty cells, we would have another function that takes action on them.
The function that we are talking about is none other than ISBLANK.
But wait! If you have read many of our Informational functions, you know that prefix 'IS' means that it will only evaluate the cell to check whether it's empty.
This is where the most versatile IF statements come into play that would help to return customized results if a blank cell is identified.
Well, we will speak more about that later. But first, let us see how to use the ISBLANK function and a couple of examples.
ISBLANK function - What is it?
The ISBLANK is categorized as an Information function that checks whether the reference cell is empty. The result is returned as a boolean value where TRUE equals to empty cell, whereas FALSE means the cell has some value stored in it.
For example, suppose you have three cells where two of them have either text or numerical values. Then, when you use the function, only the empty referenced cell would return as TRUE, while the other two would return the result as FALSE.
The syntax for the function is
=ISBLANK(value)
where
value - (required) the value or referenced cell that the function will evaluate.
Suppose you have the data in Excel as illustrated below:
To evaluate whether the cells are empty or not, we will use the formula =ISBLANK(B3) in cell C3 and drag it down to cell C7, which gives the result:
As you can see, whenever we had an empty cell in column B, the result equals TRUE. On the other hand, if the cell has any 'value,' then the result is equal to FALSE.
That's it! That's all there is to the function. All it would do is point out the blank cells. But it's also a useful tool since there would always be empty cells working on big data or Excel models.
Example for ISBLANK function
Previously, we saw how to use the function based on a simple example. In this section, we will see how the function interacts with different types of values in Excel.
The different types of values that you usually encounter in Excel are date and time values, formulas, text strings, special characters, etc.
Suppose you have the data in Excel, as illustrated below:
Here, we will use the formula =ISBLANK(B3) in cell C3 and drag it down till cell C11, which gives the result:
Well, it's understandable why all the cells except range B7:B9 have the result as FALSE. But what's with the range B7:B9?
All three cells are empty, yet we only see the boolean value as TRUE for one of those cells.
The reason is that the other two cells have hidden values due to the nature of those values. For example, cell B7 has space characters, which is why the result is FALSE despite the empty cell.
On the other hand, cell B9 appears empty, but in reality, it has a conditional formula that makes it act in such an aberrant manner.
This way, you can ensure that the cells you see are 'actually' empty and do not have any values invisible to the naked eye.
ISBLANK with the IF function
We have said it a thousand times and will say it again - IF is the most versatile function you would find in the entire function's library.
IF function works on the principle that if a condition is fulfilled, then Excel returns one or another value if the condition is not met.
Since the ISBLANK function returns the result as boolean values, we can leverage the power of IF statements to return different customized text strings or values.
Suppose you have the data in Excel, as illustrated below:
We will use the formula =IF(ISBLANK(B3),"Cell is Empty","Cell has value") in cell C3 and drag it down till cell C11, which gives the result:
If we write a more simplified version of the formula, it will read as follows:
=IF(Cell is Empty?,return TRUE,Else return FALSE)
When we correlate both the formulas, we see that on most occasions, the formula returns as FALSE, which is why we see the result as 'Cell has value.'
Similar to our previous example, all the cells have value except cell B8, which is 'truly' empty. What customized text string or number value you use in the formula entirely depends on you. One thing is clear - the function works well in finding those empty cells.
ISBLANK and Conditional Formatting Tool
Wait, we have already read your mind. How can you apply the ISBLANK criteria to many cells based on a couple of clicks?
You cannot always create an additional column and then use the formula to find whether the cells are empty.
This is where the conditional formatting tool comes in.
Suppose you have the data in Excel, as illustrated below:
To use the conditional formatting tool, first, select the range C3:E12. Then, press the Alt + H + L + N, which opens the window:
Here, we will click on 'Use a formula to determine which cells to format and input the formula =ISBLANK(C3), select the fill color from the Format menu, and finally click on Ok.
This will give you the result:
As you can see, wherever we had empty cells, the conditional formatting tool would highlight those cells and make distinguishing the open from non-empty cells easier.
If you do not wish to follow all these steps, we have another trick that you can use to select all the empty cells quickly.
The same as before, select the range C3:E12 and press the F5 key or Ctrl + G key, which opens the Go To window, as illustrated below:
Click on Special > select the radio button for blanks and finally click on Ok.
You will see that all the empty cells in the range C3:E12 are selected. After this, you can directly use the Fill tool to input color into the empty cells, similar to the earlier scenario below:
You can use either method to highlight the cells, and you will still get the same result!
Scenario-based Example
Suppose there are empty cells in the dataset. Then, if you directly calculate the SUM or AVERAGE for the range of numbers, you might never find that empty cells exist in the dataset.
That's because the function will still work and give the result in the selected cell. However, what if you wanted to be 110% sure that no empty cells exist in the data?
In this case, we will use the IF statements and the SUM / AVERAGE and ISBLANK functions.
We will use the formula =IF(OR(ISBLANK(C3:C9)),"",SUM(C3:C9)) in cell C10 and press the Ctrl + Shift + Enter key to give the result:
Since it is an array formula, we will only get the correct condition-based results if you press those magic keys. For example, the formula will calculate the sum directly while ignoring the blank cells in column C.
Only when we input all the prices in range C3:C9, do we calculate the sum in cell C10. Otherwise, all we see is the blank cell signifying that the condition for the array-based formula is not fulfilled.
The formula works on a really simple principle:
- The combination of OR and ISBLANK will evaluate all the cells for empty cells. If a blank cell is found, the boolean value will be TRUE, triggering an empty cell resulting from the conditional IF statements.
- Only when the array for all the cells is {TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}, we get the sum of the prices in cell C10.
Key Takeaways
- The ISBLANK is an Information function that evaluates whether the cell is empty.
- The function returns the result as a boolean value, i.e., TRUE when the cell is empty and FALSE when the cell has a value.
- You can combine the function with the IF or IFS function to return multiple results based on your input criteria.
- The conditional formatting or Go To Special tool can also identify blank cells in a selected range.
- The COUNTBLANK function counts the number of blank cells in a referenced range.
- ISBLANK and COUNTBLANK will allow you to make most of the operations on empty cells.
or Want to Sign up with your social account?