Example 1 – Use the Excel ISBLANK Function in a Range to Know If a Cell Is Blank
We have a list of values in B5:B9.
- Use the formula in cell C5. Write the cell range inside the formula for which you want to see the result.
=ISBLANK(B5:B9)- Press Enter.
- 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
We have a dataset of student mark sheets. Some of the students were absent from certain exams. We want to check if the student was absent from any exams.
- Use this 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.
- Copy the formula through cell G10 using AutoFill.
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
- Use the formula given below in cell G5 to check the range C5:F5.
=AND(ISBLANK(C5:F5))
- Press Enter to see the result.
- Copy the formula down to cell G11 using the AutoFill feature of Excel.
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
We have the dataset of a gadget store. If any product is sold, the Selling Date is in the D column. If it is not sold, the value is blank. We want to put an item’s availability based on its Selling Date.
- Use this 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
- Use this formula in cell E5.
=IF(NOT(ISBLANK(D5:D11)),"Sold", "Available")
- Press Enter.
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 the ISBLANK Function to Find Blanks in a Range
Alternative 1 – Combining the IF and the SUMPRODUCT Functions
- Copy this formula to cell G5.
=IF(SUMPRODUCT(--(C5:F5=""))>0,"Retake","Promoted")
- 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.
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.
Alternative 2 – Merging the COUNTBLANK and the IF Functions
In a dataset, if any student is absent in more than 1 subject, we want to write “Retake” as Remarks. Otherwise, “Promoted” will be written.
- Use this formula in cell G5.
=IF(COUNTBLANK(C5:F5)>1,"Retake","Promoted")
- Hit the Enter button.
- Copy the formula up to cell G10 using AutoFill.
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.
- 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 the Practice Workbook
Related Articles
<< Go Back to Excel ISBLANK Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
