Null In Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is 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.

Table of contents
  • Null error is an error that occurs when Excel formulas have incorrect cell references or their positioning is wrong. This error also occurs when using a space between two cell references. There are two reasons for the null error: incorrect range reference or the use of the intersect operator (space character).
  • Instead of using ISBLANK, we can also test NULL cells with double quotes (“ ”) since even space is considered a character and treated as a non-empty cell.
  • If a cell appears blank but has a non-null formula, use the LEN function to test the number of characters.

ISBLANK To Find 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.

ISBLANK Formula

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.

Shortcut To Find NULL Cells In Excel

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 (=).

null value Example 2

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

null value Example 2-1

Now open one more equal sign after the cell reference.

null value Example 2-2

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

null value Example 2-3

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.

null value Example 2-4

Drag the formula to the remaining cells.

NULL Example 2-5

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

null value Example 2-6

How To Fix NULL In Excel? (With Steps)

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.

Examples

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.
NULL Example 1

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


    NULL Example 1-1

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


    NULL Example 1-2

  3. We got the result as given below:


    NULL Example 1-3

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


    NULL Example 1-4

  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.


    NULL Example 1-5

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


    NULL Example 1-6

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


    NULL Example 1-7

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


    NULL Example 1-8

  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.


    NULL Example 1-9

  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.


    NULL Example 1-10

Example #2

Consider the below data for an example.

Example 3

Step 1: Open the IF condition first.

Example 3-1

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

Example 3-2

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

Example 3-3

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

Example 3-4

We got the result as the same cell value.

Example 3-5

Step 5: Drag the formula to the remaining cells.

Example 3-5

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

Example 3-6

Important Things To Note

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

Frequently Asked Questions

1. How to remove rows with NULL values in Excel?

Click on any cell in your table. Go to the Tools tab - Transform group. Click Delete Blanks - Empty Rows. Click OK to confirm that you really want to remove empty rows.

FAQ 1 - data

2. How do I remove null from a column?

For example, first, begin by connecting your data set and carefully examine it for any measures or dimensions that contain NULL values. Once you have identified these instances, right-click on the NULL values and select "Exclude" to remove them from your data set. Next, drop the column on the filter shelf to refine your data even further. You can then deselect "Null" from the options to ensure that any remaining null values are not included in your analysis.

3. How do I show 0 as NULL in Excel?

To show cells with a value of zero (0), select the "Show a zero in cells that have zero value" checkbox. To hide zero (0) values and display blank cells instead, uncheck the exact checkbox.