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. Here, we will show 3 examples of how we can use the ISBLANK function.
Download Practice Workbook
3 Examples of Excel ISBLANK Function
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.
- Firstly, select the D5 cell and enter the following formula,
- 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.
- 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.
- How to Use ISNUMBER Function in Excel (7 Examples)
- How to Use ISERROR Function in Excel (With 5 Examples)
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.
- To start with, select the F5 cell and type,
<span style="font-size: 14pt;">=FILTER(B5:B15,ISBLANK(C5:C15))</span>
- Then, hit the Enter button.
- As a result, we will have a list of students who were absent.
- 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
- To begin with, click on the F5 cell and enter the following formula,
- 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.
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. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.