ISBLANK Function in Excel
ISBLANK in Excel is a logical function that checks if a target cell is blank or not. It returns the output “true” if the cell is empty (blank) or “false” if the cell is not empty. It is also known as referencing worksheet function and is grouped under the information function of Excel
ISBLANK Formula in Excel
The formula is stated as follows:
Value: It is an argument (cell reference) which we want to check.
How to Use the ISBLANK Function in Excel?
The ISBLANK function is very simple and easy to use. Let us understand the working of this function with the help of some examples.
Example #1
In the table given below, the cells A2 to A5 contain values. The ISBLANK function returns the value “false.” But the cell A6 is empty (no value). Hence, the ISBLANK function returns the value “true” (as shown in the succeeding image).
Example #2
A list of first and last names is given in the table below. The last names of some people are not listed. Let us identify the names that are incomplete or without the last name, using the ISBLANK function.
Now, we will understand how the formula of the ISBLANK function works. If the last name is blank, the ISBLANK function will return the value “true.” Then, using this “value” and IF function, let us check the names that are incomplete or without the last name.
The following syntax helps to identify the incomplete names.
“=IF(ISBLANK(B2),“InCompleteName”,“Complete Name”)”
When the ISBLANK returns “true;” the IF function returns “InComplete Name.” That is, the last name is not provided. Similarly, if the ISBLANK returns “false” then the output of the IF function is “Complete Name.” This indicates that the last name is provided.
The subsequent image shows how the above formula is applied to the rest of the cells.
The output is shown in the below image.
Example #3
A range of datasets (B2 to H11) is provided in the table below. We want to highlight the blank cells. That is, we need to highlight the cells B5, C4, etc.
Now, let us understand how conditional formatting and the ISBLANK function are used to highlight the blank cells.
- Select the data range from B2:H11. Then, under the Home tab, select the conditional formatting in ExcelConditional Formatting In ExcelConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more (as shown in the succeeding image).
Below are the steps used to highlight blank cells using ISBLANK function-
- Select “Home” tab.
- Select “Styles.”
- Select “Conditional Formatting.”
- Then select the “New Rule.” The “New Formatting Rule” window will pop up.
Now, we will use the below formula to determine the blank cells to format.
“=ISBLANK(B2:H11)”
- Next, select the “format” and choose the highlighting color (e.g., yellow), and press “OK.”
The output showing highlights of the blank cells (indicated in the below image).
Example #4
Consider the below table with some values in column A. We note that A4 is an empty cell and cellA5 contains an empty string. An empty string is a special case where the text value has no value. That is, the length of the string is zero. It is represented as empty double quotes (“”).
Now, let us differentiate between an empty cell and a cell containing an empty string using the ISBLANK function.
In Excel, both the cells A4 and A5 seem to be empty, but we need to identify whether they are empty or not. In this example, cell A4 is empty. In contrast, cell A5 appears to be empty, but it is not. It has a formula that returns an empty string (“”), making it appear empty.
In this scenario, the ISBLANK formula will return “true” for cell A4 and “false” for cell A5, indicating that cell A5 is not empty.
We will use the ISBLANK function along with the IF function to check for the empty cell.
The IF function in Excel checks if data in a cell meets a given condition and returns one value for a “true” result and another value for a “false” result.
The formula that we will use is,
“=IF(ISBLANK(A2),“Is Empty”,“Not Empty”)”
In the case of an empty string, the ISBLANK function returns a “false” value. When a cell is empty or null it returns a “true” value.
The subsequent image shows how the above formula is applied to other cells.
For cell A4, the formula returns “true” (Is Empty); hence it is an empty cell. For other cells that have some values, it returns the output “false” (NotEmpty).
Note: If a cell has an empty string (“”), the ISBLANK function will return “false,” as they are not blank.
The output showing the empty cell and the cell with an empty string is indicated in the below image.
Example #5
A list of items with their SKU codes is provided. Column A contains the list of items. Column B contains their SKU code. In column B, the items for which the SKU codes have not been assigned are left blank. Column D has a list of items not arranged in order. We need to find their SKU code. If the SKU code is not assigned, then enter the formula that returns “Assign SKU Code.”
The ISBLANK function along with the VLOOKUP and IF are used to check and assign the SKU code for the selected list (the formula is indicated below).
“=IF(ISBLANK(VLOOKUP(D2,$A$2:$B$21,2,0)),“Assign SKU Code”,VLOOKUP(D2,$A$2:$B$21,2,0))”
VLOOKUP function in Excel fetches the required data in a table arranged vertically.
The succeeding image shows the above formula applied to other cells in the table.
The output that returns the SKU code of the items is shown in the table below. If the SKU code is not assigned, it displays “Assign SKU Code.”
Frequently Asked Questions (FAQs)
The ISBLANK function is used to test if a specified cell is blank (empty) or not. For example,“=ISBLANK(G1)” will return “true” if G1 is empty, and “false” if G1 contains text or formula that returns an empty string.
The ISBLANK formula is mentioned as:
“=ISBLANK(Reference Cell)”
The “Reference Cell” is the target cell or a range of cells on a worksheet that is tested if it is empty or not.
ISBLANK function can be nested along with conditional formatting to find blank cells and format the cells accordingly.
Key Takeaways
- ISBLANK Excel function is a logical function in Excel that verifies if a target cell is blank or not.
- It is also a type of referencing worksheet function which takes a single argument, the cell reference.
- The ISBLANK formula is “=ISBLANK(Reference Cell)”
- It can be used for conditional formatting along with other Excel functions like IF, VLOOKUP, etc.
Recommended Articles
This is a guide to the ISBLANK function in Excel. In this article, we have discussed how to use this function along with step-by-step examples and a downloadable template. You may also look at the below useful functions in Excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers
Leave a Reply