How to Use ISBLANK to Identify Blanks in Range in Excel

This guide will discuss how to use ISBLANK to identify blanks in a range in Excel. 

When we want to determine whether there are blank cells in a range or array, we can easily do it using the ISBLANK function in Excel.

The rules for using the ISBLANK function in Excel are the following:

• The ISBLANK function will return TRUE if a cell is empty, and FALSE if a cell is not empty. 
• The ISBLANK function will return FALSE for cells that look blank but are actually not. This includes cells with zero-length strings from an external data source, spaces, apostrophes, or other non-printing characters, and an empty string (“”).

Excel offers several built-in functions that allow us to easily perform tasks that can be time-consuming on a larger scale such as identifying blank cells in a range or array.

Instead of manually checking through each cell across all sheets, we can quickly determine a blank cell using the ISBLANK function in Excel. 

Furthermore, we can combine it with the IF function to input any text or value we want in blank cells.

In this guide, we will provide a step-by-step tutorial on how to use the ISBLANK to identify blanks in range in Excel. Additionally, we will explore the syntax and a real example of using the function.

Great! Let’s dive right in.

The Anatomy of the ISBLANK Function

The syntax or the way we write the ISBLANK function is as follows:

=ISBLANK(value)

• = the equal sign is how we begin any function in Excel.
• ISBLANK() is our ISBLANK function. This function checks whether a cell reference is an empty cell. When the cell is empty, it will return TRUE. Otherwise, it will return FALSE.
• value is the only required argument. This is the cell or a name that refers to the cell we want to test. 

The Anatomy of the IF Function

The syntax or the way we write the IF function is as follows:

=IF(logical_test,[value_if_true],[value_if_false])

• = the equal sign is how we activate any function in Excel.
• IF() refers to our IF function. This function checks whether a condition is met and returns one value if TRUE, and another value if FALSE.
• logical_test is the only required argument. This refers to any value or expression that can be evaluated as TRUE or FALSE.
• value_if_true is an optional argument. This refers to the value we want the function to return if the logical_test is TRUE. When omitted or left blank, TRUE is returned.
• value_if_false is another optional argument. This refers to another value we want the function to return if the logical_test is FALSE. When omitted or left blank, FALSE is returned.

A Real Example of Using ISBLANK to Identify Blanks in Range in Excel

Let’s say we have a data set containing the list of products and their sales. Our initial data set would look like this:

In the spreadsheet above, we can see the list of products, the total number of products sold, and the total sales made for each product. We want to identify the blank cells in our data set.

The ISBLANK function is the most straightforward way to do this. We can simply input the formula and select a range of cells to check. The function will return TRUE for any cells that are empty otherwise, it will return FALSE.

We will be using the formula below to perform this task:

=ISBLANK(D2)

Since we have successfully identified the empty cells in the range, let’s try inputting a text or value for the empty cells. We can do this by simply combining the IF function with the ISBLANK function. 

For instance, we want to identify the blank cells in the Sale column. We will be using the formula =IF(ISBLANK(D2),"N/A","Available")

The ISBLANK function will identify the empty cells. To do this, we will create a new column (Status) to display the results. The IF function will input the appropriate value depending on the results. 

In our formula, we will return N/A if the cell is empty and return Available if the cell is not empty. You may input other values that are appropriate to your spreadsheet. 

Our final data set would look like this:

You can make your own copy of the spreadsheet above using the link below. 

Amazing! Now we can dive into the steps of using ISBLANK to identify blanks in range in Excel.

Alternative Method to ISBLANK Function

It is important to take note that Excel has a different definition of blank. Referring to a cell as “blank” can be misleading as it may appear as blank but it may not be empty. It is more appropriate to think of the ISBLANK function to mean empty cells.

Aside from that, the LEN function is another method we can use to count blank cells that include empty strings when we want to get the total of blank cells. 

We can use the formula to perform this task:

=LEN(D2)=0

Sometimes a formula that results in an empty string can be inputted into a cell. When this happens, the cell may appear blank, but the ISBLANK function will consider it not empty due to the formula. 

The LEN function will test for a length of zero, meaning any cell that contains zero characters or values will return TRUE. It will consider a cell empty even when it has a formula that results in an empty string. 

How to Use ISBLANK to Identify Blank in Range in Excel

1. First, we will create a new column to input the results.

2. We will start by typing the ISBLANK function which will give us “=ISBLANK(“.

3. We want to determine the blank cells in the Sales column so we will select a cell in this column. This will give us the formula “=ISBLANK(D2)”.

4. We will press the Enter key to return the results.

5. Drag down the Fill Handle tool to copy the formula.

6. Now we will combine the ISBLANK formula with the IF function to input values in the blank cells. To do this, we will start with the IF function and then add the ISBLANK function. This will give us the formula “=IF(ISBLANK(‘.

7. Then, we will add the conditions. If the cell is blank, we will input N/A. If the cell is not blank, we will input Available. The complete formula would be “=IF(ISBLANK(D2),”N/A”,”Available”)”.

8. We will press the Enter key to return the results.

9. We will drag down the Fill Handle tool to copy the formula.

10. Lastly, let’s try an alternative to the ISBLANK function using the LEN function. We can simply type in the formula “=LEN(D2)=0”.

11. We will press the Enter key to return the results. Afterward, we will drag down the formula to apply to the other cells.

And tada! We have successfully used ISBLANK to identify blanks in a range in Excel.

You can apply this guide whenever you need to determine the empty cells in a range or array of data in Excel. You can now use the ISBLANK function and the various other Microsoft Excel formulas available to create great worksheets that work for you. 

That’s about it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.