Sometimes, you need to do something with a cell if it is not blank. If you are searching for a Microsoft Excel function that checks if a cell is blank or not, then you have come to the right place. The ISBLANK function checks if any cell is blank or not. In this article, I will explain how to use the Excel ISBLANK function in a range. The ISBLANK function can be used for a particular cell only or for a range as well. I will show different examples of using the Excel ISBLANK function in a range.
How to Use Excel ISBLANK to Identify Blanks in Range: 5 Suitable Examples
I will give nine examples of using the Excel ISBLANK function. If you want to use the ISBLANK function in a range, you can merge the ISBLANK function with another function. Follow the examples given below so that you can achieve a clear understanding of the ISBLANK function.
Example 1: Use the Excel ISBLANK Function in a Range to Know If a Cell Is Blank
In this example, I will show the simplest way to use the ISBLANK function in a range. Follow the steps below:
- Write the formula in cell C5. Write the cell range inside the formula for which you want to see the result.
=ISBLANK(B5:B9)- Now, press Enter.
- Thus, Excel ISBLANK returns TRUE or FALSE based on whether the cell is empty or not. Here, the B8 cell is blank, so it gives TRUE in the C8 cell.
Formula Breakdown
- =ISBLANK(B5:B9) is a formula that checks whether any of the cells in the range B5:B9 are blank. It will return TRUE if the cell is blank and FALSE if any cell in the range contains a value.
Read More: How to Use ISBLANK Function in Excel for Multiple Cells
Example 2: Use the Excel ISBLANK Function in a Range to Know Whether One or More Cells Are Blank or Not
Suppose I have a dataset of student mark sheets. Some of the students were absent from certain exams. I want to check if the student was absent from any exams or not. If the marks for any subject are blank, you can say that the student was absent in that subject.
In this case, you can use the ISBLANK and OR functions together. If any one cell is blank in the given range, it will return TRUE.
- Firstly, write the formula in cell G5.
=OR(ISBLANK(C5:F5))
- Hit the ENTER button.
- As there is no blank cell in the C5:F5 range, the formula returns FALSE in cell G5.
Formula Breakdown
- ISBLANK(C5:F5) tests each cell in the range C5:F5 and returns a TRUE or FALSE value depending on whether the cell is blank or not. If a cell is blank, the formula returns TRUE for that cell, and if a cell is not blank, it returns FALSE.
- OR(ISBLANK(C5:F5)) takes the array of TRUE/FALSE values returned by the ISBLANK function and returns TRUE if any of the values in the array are TRUE. In other words, if any cell in the range C5:F5 is blank, the formula will return TRUE. If all cells in the range have a value, the formula will return FALSE.
- Afterward, copy the formula through cell G10 using AutoFill.
- As a result, if any student is absent from a single or multiple exams, it returns TRUE.
Read More: How to Use ISBLANK Function for Conditional Formatting in Excel
Example 3: Using the ISBLANK Function to Determine If All the Cells in a Range Are Empty
With the same dataset, I will show another example. If you want to know if any student was absent from all the exams, you have to know if all the cells are blank.
Here, you can use the AND function with the ISBLANK function. When all the cells in the given range are blank, it will return TRUE. Otherwise, it will give FALSE.
- To begin, write the formula given below on cell G5.
=AND(ISBLANK(C5:F5))
- Press ENTER to see the result.
- Copy the formula down to cell G11 using the AutoFill feature of Excel.
- You can see that for the C11:G11 range, the result is TRUE as all the cells are blank in this range.
Formula Breakdown
- ISBLANK(C5:F5): This part of the formula checks if any of the cells in the range C5:F5 are blank. It returns an array of TRUE/FALSE values, where each element corresponds to a cell in the range.
- AND(ISBLANK(C5:F5)): The AND function takes the array of TRUE/FALSE values returned by ISBLANK(C5:F5) and returns TRUE if all of the values are TRUE, and FALSE if any of them are FALSE. In other words, it checks if all of the cells in the range C5:F5 are blank. If they are, it returns TRUE, and if any of them are not blank, it returns FALSE.
Read More: How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel
Example 4: Apply Conditions with the Excel ISBLANK Function If Any Cell in Range is Empty
This is the dataset of a gadget store. If any product is sold, the Selling Date is mentioned in the D column. If it is not sold, Selling Date is not written, or it is blank, you can say. I want to get a status based on this.
I will merge the IF function and the ISBLANK function to write Sold or Available status in the Status column.
- Write the formula in cell E5.
=IF(ISBLANK(D5:D11),"Available", "Sold")
- Press ENTER.
- As a range is mentioned in the formula, you will get the result for all the cells at once.
Formula Breakdown
- ISBLANK(D5:D11): This function checks whether the cells D5 to D11 are blank or not. It returns an array of TRUE or FALSE values, with TRUE indicating that the corresponding cell is blank, and FALSE indicating that the corresponding cell is not blank.
- IF(ISBLANK(D5:D11),”Available”, “Sold”): This is an IF function that takes the array of TRUE or FALSE values from the ISBLANK function as its first argument. If any of the values in the array are TRUE, then the function returns “Available“. Otherwise, it returns “Sold“.
Example 5: Apply Conditions with the Excel ISBLANK Function If Any Cell in Range is Not Empty
If you want to know if any cell is not blank, you can merge the NOT function with the ISBLANK function. To get the status, I used the IF function.
- Write the formula in cell E5.
=IF(NOT(ISBLANK(D5:D11)),"Sold", "Available")
- Press ENTER, and for the non-blank cells, the IF function returns “Sold”.
Formula Breakdown
- ISBLANK(D5:D11): This function checks if the cells in the range D5:D11 are blank.
- NOT(ISBLANK(D5:D11)): The NOT function is used to invert the logical result of the ISBLANK Function. In other words, it returns TRUE if there is at least one non-blank cell in the range, and FALSE otherwise.
- IF(NOT(ISBLANK(D5:D11)),”Sold”, “Available”): This is the full formula. If the result of the NOT(ISBLANK(D5:D11)) expression is TRUE, the formula returns the string “Sold“. If the result is FALSE, the formula returns the string “Available“
Alternatives to Excel ISBLANK Function to Find Blanks in a Range
1. Combining the IF and the SUMPRODUCT Functions
There is an alternative to the ISBLANK function in Excel. It is the SUMPRODUCT function. By merging the IF and the SUMPRODUCT functions, you can get the same result as the ISBLANK function. Follow the steps below:
- Copy this formula to cell G5.
=IF(SUMPRODUCT(--(C5:F5=""))>0,"Retake","Promoted")
- Then, hit ENTER.
- As there is no blank cell in the range C5:F5, Remarks is “Promoted”.
- Drag the AutoFill handle to copy the formula up to cell G11.
- The range that has one or more blank cells, is “Retake”.
Formula Breakdown
- IF: This is a logical function that tests whether a condition is true or false and returns one value if the condition is true, and another value if the condition is false.
- SUMPRODUCT: This function multiplies the corresponding elements in two or more arrays and returns the sum of those products. This formula is being used to count the number of empty cells in the range C5:F5.
- —: This is a double negative operator that converts the logical test (C5:F5=””) to a numerical value of 1 or 0, depending on whether the condition is true or false.
- C5:F5=””: This is a logical test that checks whether each cell in the range C5:F5 is empty. If a cell is empty, the result of the test is true, otherwise, it is false.
2. Merging the COUNTBLANK and the IF Functions
The same thing can be done by using the COUNTBLANK function with the IF function. The COUNTBLANK function returns the number of blank cells.
In this dataset, if any student is absent in more than 1 subject, I want to write “Retake”. Otherwise, “Promoted” remarks will be written. To do this, follow the steps below.
- Write the formula in cell G5.
=IF(COUNTBLANK(C5:F5)>1,"Retake","Promoted")
- Hit the ENTER button.
- As a result, “promoted” is written as the number of blank cells is zero.
- Now, copy the formula up to cell G10 using AutoFill.
- As the number of blank cells in ranges C7:F7 and C9:F9 is greater than 1, “Retake” is written.
Formula Breakdown
- COUNTBLANK(C5:F5): This function counts the number of blank cells in the range C5:F5.
- >1: This comparison operator checks if the result of the COUNTBLANK function is greater than 1.
- IF(COUNTBLANK(C5:F5)>1,”Retake”,”Promoted”): This is the full formula. If the result of the comparison is true (i.e., there is more than one blank cell in the range), the formula returns the string “Retake”. If the result of the comparison is false (i.e., there is at most one blank cell in the range), the formula returns the string “Promoted.”
Things to Remember
- The ISBLANK Function does not recognize cells that contain formulas that return empty strings, such as =””. Therefore, if a cell contains such a formula, the function will return FALSE even though the cell appears to be empty.
- The ISBLANK function also does not recognize null values. If a cell contains a null value, the function will return FALSE, even though the cell appears to be empty.
- You can combine the ISBLANK function with other functions such as IF, AND, OR, etc. to create more complex formulas.
- It is essential to distinguish between a cell that is blank and a cell that contains a blank space. If a cell contains a blank space, it is not considered empty, and the ISBLANK function will return FALSE.
- When using the ISBLANK function with a range of cells, it returns an array of boolean values, one for each cell in the range.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it yourself.
Conclusion
In this article, I have concluded the examples where the Excel ISBLANK Function can be used effectively in range. Moreover, the alternative function in Excel that can be used to check if there is any blank cell in the given range is explained with an example. Follow the formula breakdowns to understand the functions clearly. I hope this article has given you a clear idea about the Excel ISBLANK function. Modify the formula according to your dataset. Don’t forget to leave a comment if you have any questions regarding this article.
Related Articles
<< Go Back to Excel ISBLANK Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
