Null in Excel

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.

We have several ways of finding the NULL cells in Excel. Today’s article will take a tour of dealing with NULL values in Excel.

How do you find which cell is blank or null? Yes, we must look at the particular cell and decide. Let us discover many methods of finding the null cells in Excel.

ISBLANK Function to Find NULL Value 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 functionISBLANK FunctionISBLANK in Excel is a logical function that checks if a target cell is blank or not. It returns the output “true” if the cell is empty (blank) or “false” if the cell is not empty. It is also known as referencing worksheet function and is grouped under the information function of Excel read more.

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

#1 – How to Find NULL Cells in Excel?

#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 us open the ISBLANK formula in cell B2 cell. Select cell A2 as the argument. Since there is only one argument, close the bracket. We got the result as given below: Then, drag and drop the formula to other remaining cells. 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. Let us 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 number of characters in the cell. It counts the no. of characters and gives the result. The LEN function returned the no. of character in the A7 cell as 1. So, there should be a character in it. 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. 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.

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.

#3 – How to Fill Our 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. But we can also receive our values for the NULL cells.

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.

Things to Remember

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

This article is a guide to Null in Excel. 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: –

  • Excel Count RowsExcel Count RowsThere are numerous ways to count rows in Excel using the appropriate formula, whether they are data rows, empty rows, or rows containing numerical/text values. Depending on the circumstance, you can use the COUNTA, COUNT, COUNTBLANK, or COUNTIF functions.read moreVBA RangeVBA RangeRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read moreCountif not Blank in ExcelRemove Blank Rows in ExcelRemove Blank Rows In ExcelThere are several methods for deleting blank rows from Excel: 1) Manually deleting blank rows if there are few blank rows  2)  Use the formula delete 3) Use the filter to find and delete blank rows.read more