The Excel ISBLANK function lets you check if the cell is empty or not. It returns TRUE or FALSE as the output.
- TRUE if the cell is empty
- FALSE if the cell is not empty
For example, you can identify the students who didn’t appear for the given test in the following table.
Our formula would be as follows:
=ISBLANK(B2)Where B2 is the cell reference, the content of B2 will be checked if blank or not.
The results are displayed in the following GIF,
Note that here I am using the Conditional Formatting feature in Excel to highlight the absent students.
Syntax
The ISBLANK is a simple function. It is mostly combined with the IF function in Excel.
=ISBLANK(value)Where the value argument needs to be replaced with the cell reference.
Important Notes:
- For accurate results, make sure the cell contains absolutely nothing (no spaces, tabs, or carriage returns).
- If the cell contains the formula that returns an empty string, it is not treated as a blank cell by the function.
- Be extra cautious when using ISBLANK within a spreadsheet that is imported from other sources, including the internet, as it can have hidden content.
How to use the ISBLANK Function in Excel
Download the example spreadsheet used to demonstrate the ISBLANK function in the upcoming section. Practice is the key to success!
Example 1: Combining ISBLANK with the IF function
Suppose that you are an HR Manager. You have been training newly hired employees since last month. Most of them could successfully complete the training as shown in the following table.
Our task is to identify and mark the employees whose training is still pending.
Here are the steps,
- Select the cell C2
- Type =IF
- Select the first option from the popup
- Type ISBLANK
(As ISBLANK is a built-in function in Excel, you will see the following popup) - Double-click the ISBLANK command from the list
- Specify the cell reference B2
- Close the bracket for the ISBLANK function using )
- Type , to move to the next argument of the IF function
- Specify “Pending”
- Type ,
- Specify “Completed”
- Close the parenthesis for the IF function using )
- Hit the Enter key
Our final formula is as follows,
=IF(ISBLANK(B2), "Pending", "Completed")Where,
- ISBLANK(B2) is the formula to find if the cell B2 is blank
- Pending is the desired output if cell B2 is blank
- Completed is the desired output if cell B2 is not blank
The results are displayed in the following image,
To learn more about the IF function, please read our article: How to use Excel IF Function (With Examples)
Example 2: Highlighting blank cells within the given table (array) using Conditional Formatting and the ISBLANK function
Consider the following dataset. We have monthly revenue data for each client covering the entire year.
Our task is to highlight the months where there was no revenue from the given client.
Here are the steps,
- Select the entire array
- Go to the Home tab
- Click the Conditional Formatting icon
- Choose the New Rule option from the list
- You will see the following popup
- Choose Use a formula to determine which cells to format option
- Enter the formula =ISBLANK(B2)
- Next, click the Format button
- You will see the following popup
(For the sake of this demo, I am choosing the light orange color in the Fill tab) - Press the OK button
- You will be redirected to the Conditional Formatting dialog box
- Click the OK button
It will instantly highlight all the blank cells within the given array. Refer to the following image.
Smart Applications of the ISBLANK Function and Alternatives in Excel
1. Find the Cells that are Not Empty (or contain a value)
You can combine the ISBLANK with the NOT function to reverse the logic. It allows you to find the cells that are not blank.
For example, you can find the students who appeared for the given tests using their scores. Refer to the following image,
Our formula would be as follows:
=NOT(ISBLANK(B2))Where,
- B2 is the cell reference
- ISBLANK(B2) is the formula to find if the cell is empty
By combining the NOT with the ISBLANK function, you create a formula to find the cells that contain values (text, numbers, spaces, etc.).
The results are displayed in the following GIF.
2. Find Blank Cells
Actually, this method is an alternative to the ISBLANK function. Instead of typing the formula, you can simply use the double quotation marks to find the empty cells.
For example, consider the following ISBLANK formula to find if the cell B2 is empty or not.
=ISBLANK(B2)The above formula is similar to the following:
=B2=""Both give us the same results. Refer to the following GIF.
2. Find Non-blank Cells
As discussed earlier in this article, we can combine the ISBLANK and NOT functions to find the cells that contain values (text, numbers, spaces, etc.). The Not Equal To comparison operator is the alternative to this combination.
It allows you to find the non-blank cells quickly.
For example, consider the following combination of the NOT and ISBLANK functions to find whether cell B2 contains any values.
=NOT(ISBLANK(B2))It is similar to the following:
=B2<>""The results are displayed in the following GIF.
I am using conditional formatting above to highlight the FALSE results.
The formulas to find both blank and non-blank cells are almost equal. All you need to do is replace equal to (=) with not equal to (<>) comparison operator.
Takeaway
Learning the ISBLANK function and its alternatives to find blank and non-blank cells is a critical step in learning Excel. It helps you organize and analyze datasets more efficiently.
The function is mostly combined with the IF function in Excel. To learn the IF function, click here.
I hope this article taught you all the bells and whistles of the ISBLANK function. Please comment below if you are stuck or encounter any particular error while finding the blank and non-blank cells in Excel. I will answer your questions as soon as possible.
Additional Resources:
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.