Null In Excel

Null in Excel is an error that occurs when the two or more cell references provided in a formula are incorrect, or the position they have been placed in is erroneous.

NULL is nothing but nothing or blank in Excel. Usually, when working in Excel, we encounter many NULL or blank cells. We can use the formula and find out whether the particular cell is blank (NULL) or not.

For example, suppose we use space in formulas between two cell references; we will encounter a null error. In that case, there are two reasons to meet this error, one if we used an incorrect range reference and another when we use the intersect operator, which is the space character.

How do you find which cell is blank or null? Yes, we must look at the particular cell and decide. We have several ways of finding the NULL cells in Excel. In this article, let us learn how to deal with NULL values in Excel.

Let us discover many methods of finding the null cells in Excel.

In Excel, we have an ISBLANK function, which can find the blank cells in the worksheet. First, let us look at the syntax of the ISBLANK function.

The syntax is straightforward. Value is nothing but the cell reference; we are testing whether it is blank or not.

Since ISBLANK is a logical excel function, it will either return TRUE or FALSE. If the cell is NULL, it will return TRUE, or else it will return FALSE.

Note: ISBLANK will treat the single space as one character; if the cell has only space value, it will be recognized as a non-blank or non-null cell.

We have seen the traditional formula way to find the null cells. Without using the ISBLANK function, we can find the null cells.

Let us open the formula with an equal sign (=).

After the equal sign, select cell A2 as the reference.

Now open one more equal sign after the cell reference.

Now mention open double-quotes and close double-quotes. (“”)

The sign double quotes (“”) says the selected cell is NULL or not. If the selected cell is NULL, we will get TRUE. Else, we will get FALSE.

Drag the formula to the remaining cells.

We can see that in cell B7, we got the result as “TRUE.” So it means it is a null cell.

We have seen how to use the shortcut to find the NULL cells in the Excel sheet. In our formula, we could only get TRUE or FALSE. But we can also receive our values for the NULL cells.

The steps are:

Step 1: To start with, insert the IF condition in the cell where we want to find the result.

Step 2:  Remember, we need to do a logical test. We need to test whether the cell is NULL or not.

Step 3: If the logical test is TRUE (TRUE means cell is NULL).

Step 4: If the logical test is FALSE (which means the cell contains values).

We will get the result in the desired cell.

Step 5: Next, we can drag the formula to the remaining cells.

Example #1

#1 - How to Find NULL Cells in Excel?

Assume we have the values below in the Excel file and want to test all the null cells in the range.

1.  Let us open the ISBLANK formula in cell B2 cell.

   
   

2. Select cell A2 as the argument. Since there is only one argument, close the bracket.

   
   

3. We got the result as given below:

   
   

4. Then, drag and drop the formula to other remaining cells.

   
   

5. We got the results. But look at cell B7. Even though there is still no value in cell A7, the formula returned the result as a FALSE, non-null cell.

   
   

6. Let us apply the LEN function in excel to find the number of characters in the cell.

   
   

7. It counts the no. of characters and gives the result.

   
   

8. The LEN function returned the no. of character in the A7 cell as 1. So, there should be a character in it.

   
   

9. Let us edit the cell now. So, we found the space character here. Let us remove the space character to make the formula show accurate results.

   
   

10. We have removed the space character, and the ISBLANK formula returned the result as TRUE. That is because even the LEN function says there are zero characters in cell A7.

    
    

Example #2

Consider the below data for an example.

Step 1: Open the IF condition first.

Step 2: Here, we need to do a logical test. We need to test whether the cell is NULL or not. So apply A2=”.”

Step 3: If the logical test is TRUE (TRUE means cell is NULL), we need the result as “No Values Found.”

Step 4: If the logical test is FALSE (which means the cell contains values), then we need the same cell value.

We got the result as the same cell value.

Step 5: Drag the formula to the remaining cells.

So, we have got our value of No Values Found for all the NULL cells.

• Even space will be considered a character and treated as a non-empty cell.
• Instead of ISBLANK, we can also use double quotes (“ ”) to test the NULL cells.
• If the cell seems blank and the formula shows it as a non-null cell, then you need to test the number of characters using the LEN function.