This is an overview.
This is the sample dataset.
Method 1 – Applying the ISBLANK Function to Check Multiple Blank Cells
Steps:
- Enter the following formula in G5.
=ISBLANK(B5:E13)
- Press ENTER.
- This is the output.
Formula Breakdown
- ISBLANK(B5:E13) looks for blank values in B5:C13.
- The result is a new dataset consisting of the same number of rows and columns.
Read More: How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel
Method 2 – Merging the ISBLANK, MATCH, and INDEX Functions to Find the First Non-Blank Value in an Array
You want to find the first value in C10:C13.
Steps:
- Enter the following formula in G8.
=INDEX(C10:C13,MATCH(FALSE,ISBLANK(C10:C13),0))
- Press ENTER to see the result.
Formula Breakdown
- ISBLANK(C10:C13) returns an array (4 by 1) of 4 cells containing TRUE and FALSE.
- MATCH(FALSE,ISBLANK(C10:C13),0) returns 3: FALSE was found in the third row.
- INDEX(C10:C13,MATCH(FALSE,ISBLANK(C10:C13),0)) looks for the value in C10:C13 and returns the value of C12 because the third entity of C10:C13 is C12.
Method 3 – Combining the ISBLANK and the IF Functions to Find the Current Status
Check if any course is still available for registration:
Steps:
- Enter the formula in E5.
=IF(ISBLANK(D5),"Open","Closed")
- Press ENTER and see the value in E5.
- A Plus sign appears at the lower corner of the cell.
- Drag it down to AutoFill the rest of the cells.
Formula Breakdown
- ISBLANK(D5) looks for the value in D5 and returns TRUE.
- IF(ISBLANK(D5),”Open”,”Closed”) returns ‘‘Open’’ because the argument of IF is TRUE.
Method 4 – Nesting the ISBLANK, NOT, CONCATENATE, and IF Functions to Join Text with Blank Cells in Excel
This is the sample dataset.
Steps:
- Enter the formula in D5.
=IF(NOT(ISBLANK(C5)),CONCATENATE(B5," ",C5),B5)
- Press ENTER.
This is the output.
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula Breakdown
- ISBLANK(C5) gives the value FALSE.
- NOT(ISBLANK(C5)) converts the value of ISBLANK into TRUE.
- CONCATENATE(B5,” “,C5) appends Visha and Long with a space.
- IF(NOT(ISBLANK(C5)),CONCATENATE(B5,” “,C5),B5) the argument of IF function is TRUE and it concatenates B5 and C5 with a space between them.
Download Practice Workbook
Download the following workbook to practice.
Related Articles
- How to Use ISBLANK Function for Conditional Formatting in Excel
- How to Use Excel ISBLANK to Identify Blanks in Range
- Excel ISBLANK vs IsEmpty
<< Go Back to Excel ISBLANK Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
