How to Use ISBLANK Function in Excel for Multiple Cells

Get FREE Advanced Excel Exercises with Solutions!

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.

 Overview Image


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.Sample dataset


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)

Applying Excel ISBLANK Function for Multiple Blanks

  •  Then, if we hit ENTER, we get the whole result.

Result of Excel ISBLANK function for all Blanks in a Range of Multiple Cells

 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))

Merging of ISBLANK, MATCH, and INDEX Functions to Find First Value in an Array

  • Then, we hit ENTER and get the result.

Result of Merging ISBLANK, MATCH, and INDEX Functions

 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.Sample datasetSteps:

  • First, we apply the formula at the E5.
=IF(ISBLANK(D5),"Open","Closed")

Combining ISBLANK and IF Functions for Multiple Cells

  • Hitting the ENTER key, E5 gets its value.

 Initial Result of Combining ISBLANK and IF Functions

  • 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.

Combined ISBLANK and IF Functions Result

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.

sample dataset

Steps:

  • At first, we put the formula in D5.
=IF(NOT(ISBLANK(C5)),CONCATENATE(B5," ",C5),B5)

 Nesting ISBLANK,NOT,CONCATENATE and IF Functions

  • Secondly, we will hit ENTER and D5 gets value.

Initial Result of Nesting ISBLANK,NOT,CONCATENATE and IF Functions for Multiple Cells.

  • To get the final result, we drag down and the AutoFill feature fills the value up.

Final Result of nesting Excel ISBLANK with Other Functions for Multiple 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 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.


Related Articles


<< Go Back to Excel ISBLANK Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo