In this article, we will talk about Excel ISBLANK function. The ISBLANK function looks if a certain cell or a range of cells is blank or not. If the cells are blank, then the function returns True otherwise it returns False. We can use the ISBLANK function with different functions to pull off different operations. The following gif represents the use of the ISBLANK function in Excel.
Excel ISBLANK Function: Syntax and Argument
In this section, we will explain the syntax and argument of the ISBLANK function.
ISBLANK Function of Excel (Quick View)
Summary:
Excel ISBLANK function takes in a cell reference and evaluates if the cell is blank then returns a TRUE. Otherwise, FALSE.
Available from Excel 2007.
- Generic Syntax
=ISBLANK(value)- Argument Description
| Argument | Requirement | Explanation |
|---|---|---|
| value | Required | This is the cell which the function is trying to evaluate if it is blank or not. |
Return Value:
Returns a Boolean value (TRUE or FALSE). TRUE if the cell is blank, FALSE otherwise.
ISBLANK Function in Excel: 3 Suitable Examples
In this article, we will illustrate 3 handy examples of the usage of the Excel ISBLANK function. Firstly, we will use the function with conditional formatting. Secondly, we will filter values using the function. Finally, we will use the function to find the value of the first non-empty cell of a dataset.
1. ISBLANK Function with Condition
In this example, we will apply condition to a dataset by using the ISBLANK function. We will combine the function with the IF function to execute the operation. Look at the set of data below. We have the names of some students and their marks in Physics in the Annual Examination. Now we will decide for each student, using the ISBLANK function, that he was present or absent in the examination.
Steps:
- Firstly, select the D5 cell and enter the following formula,
=IF(ISBLANK(C5),"Absent","Present")- Then, hit Enter.
- As a result, we will get whether the person is present or not.
- Finally, lower the cursor down to the last cell to autofill.
Formula Breakdown:
- ISBLANK(C5): returns a TRUE if the cell C4 is blank, and returns a FALSE if it is not
- IF(ISBLANK(C6),”Absent”,”Present”): returns “Absent” if it finds a TRUE, and returns “Present” if it finds a FALSE.
2. ISBLANK Function with FILTER Function
In this instance, we will make a list of the absent students using the ISBLANK function. We will do so, by combining the ISBLANK function with the FILTER function. This combination will allow us to find out the name of the absent students as a list.
Steps:
- To start with, select the F5 cell and type,
=FILTER(B5:B15,ISBLANK(C5:C15))- Then, hit the Enter button.
- As a result, we will have a list of students who were absent.
Formula Breakdown:
- ISBLANK(C5:C15): returns an array of TRUE or FALSE, TRUE when it faces a number, FALSE when it faces a blank cell in the range C4 to C20.
- FILTER(B5:B15,ISBLANK(C5:C15)): returns the name from the range B4 to B20 when it faces a TRUE.
3. Finding First Non-Blank Cell Value Using ISBLANK Function
In this illustration, we will find the first non-blank cell of a dataset using the ISBLANK function. We will combine the ISBLANK function with the INDEX and MATCH functions to get the job done.
Steps:
- To begin with, click on the F5 cell and enter the following formula,
=INDEX(C5:C15,MATCH(FALSE,ISBLANK(C5:C15),0))- Press Enter.
- Consequently, we will get the first non-blank value of the dataset.
- ISBLANK(C5:C15): This expression returns an array of True and False for the C5:C15 range. If the formula finds any cell in the range blank, then it will return TRUE otherwise it will return FALSE.
- MATCH(FALSE,ISBLANK(C5:C15),0): The MATCH function looks for a lookup value within an array and returns its relative position. Here, the function looks for FALSE in the array returned by the ISBLANK function. The 0 (zero) means the MATCH function will return the position of the first value that is exactly macthes with FALSE in the ISBLANK(C5:C15) array. In this case, it will be 2. Since, the ISBLANK function will have a FALSE array in the second position.
- INDEX(C5:C15,MATCH(FALSE,ISBLANK(C5:C15),0)): The INDEX function takes the index of a value within an array and returns the value. Here, the index function will get 2 from the MATCH(FALSE,ISBLANK(C5:C15),0) expression. So, the INDEX function will look for the 2nd value in the C5:C15 range. So, it will return 90.
Conclusion
In this article, we have discussed about Excel ISBLANK function. Using these methods, you can use the ISBLANK function of Excel to determine whether a cell is blank or not. If you have any questions regarding this essay, feel free to let us know in the comments.
