Junaed-Ar-Rahman
ISBLANK is the function that we use to check whether a cell in Excel is empty or has value. We may use this for a cell or in a range of cells. In this article, we’ll show you how it is possible to apply the ISBLANK function in Excel for multiple cells. We have used Microsoft 365 to perform all these.
How to Use ISBLANK Function for Multiple Cells in Excel: 4 Suitable Examples
In order to illustrate, we will use a sample dataset. For instance, the following dataset represents the regular customers of a super shop. Depending on the sales of a particular day, the salesmen of that shop may bring several changes to it. We will use this dataset for the first 2 methods.
Method 1: Applying ISBLANK Function for Checking Multiple Blank Cells
We can use the ISBLANK function to find all the blank cells in a dataset. Suppose we wish to find all the blank cells in the above dataset. If the cell is blank, the result will be FALSE. Else it will be TRUE.
Steps:
- First, we apply the following formula in the G5.
=ISBLANK(B5:E13)
- Then, if we hit ENTER, we get the whole result.
Formula Breakdown
- ISBLANK(B5:E13) looks for blank values in the range B5:C13.
- The result is a new dataset consisting of the same number of rows and columns as the input dataset.
- In the result, dataset, G5 is the result corresponding to the value B5, H5 to C5 and so on.
Read More: How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel
Method 2: Merging ISBLANK, MATCH, and INDEX Functions to Find the First Non-Blank Value of an Array
The unique 3 functions ISBLANK, MATCH and INDEX combinedly help us find the first non-blank value in an array. Suppose we want to find the first value in the C10:C13 array. We will follow the following steps to do that.
Steps:
- Firstly, we apply the following formula in the G8.
=INDEX(C10:C13,MATCH(FALSE,ISBLANK(C10:C13),0))
- Then, we hit ENTER and get 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) gives the value 3 that implies it found FALSE in the third row.
- INDEX(C10:C13,MATCH(FALSE,ISBLANK(C10:C13),0)) looks for value from C10 to C13 and returns the value of C12 because the third entity of C10:C13 is C12.
Method 3: Combining ISBLANK and IF Functions for Finding Current Status
Often, we have to make decisions based on the availability of information. For example, in the following example we will check if any course is still available for registration by ISBLANK and IF functions. The dataset is like below.
Steps:
- First, we apply the formula at the E5.
=IF(ISBLANK(D5),"Open","Closed")
- Hitting the ENTER key, E5 gets its value.
- After that, a Plus sign appears in the lower corner of the cell.
- Finally, we will drag it down and the AutoFill feature fills the remaining cells.
Formula Breakdown
- ISBLANK(D5) looks for value in D5 and returns TRUE as there is a value in D5.
- IF(ISBLANK(D5),”Open”,”Closed”) returns ‘‘Open’’ because the argument of IF is TRUE
Method 4: Nesting ISBLANK, NOT, CONCATENATE, and IF Functions for Joining Text with Blank Cells in Excel
ISBLANK along with CONCATENATE function provides a special feature to join texts in Excel. In some cases, we need to combine first names with surnames. However, surnames may not always be available. In that case we will show just the first name. Otherwise, we will combine first and surname. The dataset looks like the following image.
Steps:
- At first, we put the formula in D5.
=IF(NOT(ISBLANK(C5)),CONCATENATE(B5," ",C5),B5)
- Secondly, we will hit ENTER and D5 gets value.
- To get the final result, we drag down and the AutoFill feature fills the value up.
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 in concatenates B5 and C5 with a space between them.
Download Practice Workbook
You may download the following workbook to practice yourself.
Conclusion
Though there are a lot of examples of ISBLANK functions in Excel for multiple cells, we have shown just a few of them. However, in real life you may find different types of problems and some of them you find difficult to solve. Don’t forget to let us know. We are ready to assist you whenever you need. Have a good day.
