NULL value in Excel

Null value in Excel is an uncommon error that occurs when there is something fundamentally wrong with the formula. If it occurs in Excel, it can be confusing to some people, but it really is very simple to correct.

Key Takeaways

• Excel Doesn’t Have a True NULL: Unlike databases, Excel does not have a built-in NULL value, but blank cells or errors like #NULL! can represent missing data.
• Using Functions to Handle NULL: Functions like IF(ISBLANK(A1), "No Data", A1) help manage blank values and prevent errors in calculations.
• #NULL! Error Meaning: The #NULL! error usually appears when an incorrect range intersection operator (SPACE) is used in formulas.
• Replacing NULL or Blanks: Use IFERROR(), IFNA(), or IF(ISBLANK()) to replace blanks or errors with meaningful values.
• Impact on Calculations: Blank cells in numerical formulas may be treated as 0 or ignored, depending on the function used (e.g., SUM ignores blanks, while A1+B1 treats them as 0).

What is NULL value in Excel?

NULL in Excel occurs simply when the range provided in the formula is not valid and there is a typo in the formula. This can be mainly due to two reasons :

• Wrong input instead of a colon (:)
• Wrong input instead of a comma (,)

In most cases, this wrong input is that users insert a space rather than the appropriate character. Simply correcting these typos can remove the error. Let’s look into this in more detail.

Follow this detailed tutorial on null Excel and download this Excel workbook to practice along understand better:

 

How to fix NULL value in Excel?

Example 1 – Wrong input instead of a colon (:)

Sometimes in a formula, where there is supposed to be “:”(colon), you might have entered space. This can be the result of the error that we are getting as shown below.

In the formula bar, we can see that instead of =SUM(A7:A13), it is entered as =SUM(A7 A13), which is the cause of the error.

So to correct this, we have to replace that space with a colon(:).

Like so! As the typo has been corrected, the error has been removed and the formula is returning the correct value.

 

Example 2 – Wrong input instead of a comma (,)

Sometimes in a formula, where there is supposed to be “,”(comma), you might have entered space.

In this example, we are trying to calculate the average speed in cell B11 based on the 3 entries mentioned in rows 7, 8, and 9. The formula used is:

=AVERAGE(C7,C8 C9)

This can lead to an error that we are getting as shown below:

Here, instead of =AVERAGE(C7,C8 C9) you should enter the formula as =AVERAGE(C7,C8,C9).

There should have been a comma between C8 and C9 but instead, there has been a typing error and instead of the comma, space was inserted.

Simply removing the space and adding the comma rectifies this error.

Frequently Asked Questions

What does a NULL value mean in Excel?

Excel does not have a true NULL value. A blank cell, an empty string (""), or the #NULL! error may act as NULL representations.

How do I check if a cell is NULL or blank?

Use ISBLANK(A1) to check if a cell is empty. For formulas returning empty strings, use A1="" to detect them.

How can I replace NULL or blank values with a default value?

Use IF(ISBLANK(A1), "Default Value", A1) to replace blanks with a custom value.

Why am I getting a #NULL! error?

This error occurs when a space is used instead of a proper range operator (like a comma or colon). Example: =A1 A2 instead of =A1,A2.

Do NULL values affect calculations?

Yes, blank cells may be ignored in some functions (e.g., AVERAGE), while in direct arithmetic operations, they are often treated as zero.

Conclusion

So now it should be clear that the NULL value is a simple but uncommon error that occurs due to a typing error, making the inputs of a formula invalid or if the separators of different arguments in a formula are entered incorrectly.

It doesn’t necessarily have to be a space, but can be any incorrect character. Now that you have understood the fundamentals of Excel null value, you too can easily overcome it!