Null in Excel

Null is a type of error which occurs in excel when the two or more cell references provided in a formulas are incorrect or the position they have been placed is incorrect, if we use space in formulas between two cell references we will encounter null error, there are two reasons to encounter this error one is if we used incorrect range reference and another when we use the intersect operator which is the space character.

Null in Excel

NULL is nothing but nothing or blank in excel. Usually, when we are 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.

We have several ways of finding the NULL cells in excel. In today’s article, we will take a tour of dealing with NULL values in excel.

How do you find which cell is actually blank or null? Yes, of course, we just need to look at the particular cell and make our decision. Let’s discover many methods of finding the null cells in excel.

ISBLANK Function to Find NULL Value in Excel

In excel, we have a built-in function called ISBLANK function, which can find the blank cells in the worksheet. Let’s look at the syntax of the ISBLANK functionISBLANK FunctionISBLANK is a logical and referencing worksheet function for referring to a cell and determining whether or not it contains blank values. It just accepts one argument, the cell reference, and returns TRUE if the cell is blank and FALSE if it is not.read more.

ISBLANK Formula

The syntax is simple and straight forward. Value is nothing but the cell reference; we are testing whether it is blank or not.

Since ISBLANK is a logical excel functionLogical Excel FunctionA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more, it will either return TRUE or FALSE as a result. If the cell is NULL, then it will return TRUE, or else it will return FALSE.

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

#1 – How to Find NULL Cells in Excel?

You can download this Null Value Excel Template here – Null Value Excel Template

Assume you have the below values in the excel file, and you want to test all the null cells in the range.

NULL Example 1

  1. Let’s 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. Drag-drop the formula to other remaining cells.


    NULL Example 1-4

  5. We got the results but look at the cell B7, even though there is no value in the cell A7 still formula returned the result as a False, i.e., non-null cell.


    NULL Example 1-5

  6. Let’s apply the LEN function in excelLEN Function In ExcelThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more to find the no. 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. 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’s edit the cell now. So, we found the space character here; let’s remove the space character to make the formula to show accurate results.


    NULL Example 1-9

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


    NULL Example 1-10

#2 – Shortcut Way of Finding 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’s open the formula with an equal sign (=).

null value Example 2

After the equal sing selects the 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 signs double quotes (“”) says the selected cell is NULL or not. If the selected cell is NULL, then we will get TRUE, or 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”. It means it is a null cell.

null value Example 2-6

#3 – How to Fill Our Own Values to NULL Cells in Excel?

We have seen how to find the NULL cells in the excel sheet. In our formula, we could only get TRUE or FALSE as a result. But we can also get our own values for the NULL cells.

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, i.e., 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 (FALSE means 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 own value of No Values Found for all the NULL cells.

Example 3-6

Things to Remember

  • Even space will be considered as character and treats 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 by using the LEN function.

Recommended Articles

This has been a guide to Null in Excel. Here we discuss the top methods to Find Null Values in Excel using ISBLANK and Shortcuts to replace those null cells along with practical examples and a downloadable template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>