#NULL error in Excel

A handful of errors can arise in your Excel worksheet if the applied formula or function is not written properly. Errors are a part of your Excel life but it becomes frustrating if you can’t catch those mistakes. Luckily, Microsoft Excel lets users know about Errors with an error message. Though it doesn’t correct the problem, knowing about the cause of the errors helps the user correct the problem. One such error type is the #NULL Error.

The Null error value is raised in a cell when Excel cannot correctly read a worksheet formula or function. This error is thrown whenever the user uses an inaccurate range operator or an intersection operator in their formula.

Unlike other Excel errors (like #VALUE! or #NUM! Errors), the #NULL! Error is less common. In most cases, a #NULL! Error is shown in your Excel worksheet if the user has incorrectly inserted a space, comma, or semicolon in a formula. But what is the definition of #NULL! Error?

What is #NULL error in Excel?

The Excel #NULL error value is shown whenever the user commits a typing error, such as incorrectly typing the range name or referring to a range name already deleted or failing to put quotation marks for a string data in a formula. This error typically occurs mostly because of syntax errors. For example, if the user forgets to put a comma, semicolon or space in a formula.

The #NULL! error signifies that you’ve committed a typing error while inserting your formula. This error also occurs if in a cell the user specifies an intersection of two ranges that don’t intersect or an inaccurate range operator is used mistakenly in the formula. The #NULL errors are often simple to fix if you know where to look for the error. However, the #NULL errors are not very common in Excel. They are generally the output of typo mistakes or the use of incorrect ranges in a formula.

For instance, As you can see in the below table, we have applied the SUM formula to calculate the total marks of the student in a subject. But instead of getting the result, Excel has thrown #NULL error as an output. In the SUM formula, we have omitted the colon between two ranges, B3 and B10 and instead have put the space character in between (B3 and B10).

Scenarios of Getting in Excel

Though the primary reason the #NULL error is thrown in an Excel worksheet is due to a typo error. But that’s always not the case. There are many other situations we will get this error. Let’s look at them in detail now.

Let’s go through a few scenarios when you Excel throws the #NULL! Error:

1. Skipping a Colon in an Excel Formula

While working with a formula, the user often forgets to specify the colon in between the ranges. Let’s suppose the range entered in the SUM function formula has no colon but instead has a space character in between the two cell references. As shown below in the image, the #NULL error will be thrown.

To fix the error, all you need to do is to enter a colon between the first and second cell reference, like so:

Refer to the image given below:

As soon as you put the colon, the #NULL! error will be fixed and the formula return the sum as an output.

2. Skipping a Comma in your Excel formula

Another common instance you can likely encounter #NULL error is when you skip a comma and put a space character instead. For example, let’s suppose you’re adding two cell ranges, A4:A8 and B4:B8, but mistakenly instead of a comma, you put the space character in the formula.

In the above formula, the cell ranges inside the SUM function are not separated by a comma. Instead, we have put a space character between them. Excel won’t recognise the specified range, and it will throw a #NULL! error.

Refer to the image given below:

To fix the error, all you need to do is to enter a comma between the first range of cells and second range of cells, like so:

Refer to the image given below:

As soon as you put the comma, the #NULL! error will be fixed and the formula return the sum of 4036 (summation of both the cell ranges) as an output.

3. Space Characters Between Non-Intersecting Ranges

In an Excel formula, space character is interpreted an intersect operator. But what does an intersect operator mean?

Let’s suppose you have numeric values in three different excel columns, and you want to sum only the value that intersects between column A, column B, and row 4. It may sound simple, or it could be easily fetched for small data sets because you can just add the fourth row for columns A and B. But when you have larger data sets, it can’t be calculated, and you will require a strong, scalable formula.

So, let’s figure out how to create a formula to achieve the above result with minimal manual effort.

The solution for above problem is the use of intersect operator. Here’s the formula you could use for this summation:

However, to your surprise the above formula would also return a #NULL! error.

The above formula will throw an error because none of the cell ranges are intersecting. Here, we have not made any typing error instead we have supplied those data ranges where the intersection of data is not possible.

The correct formula will be as follows:

NOTE: In most cases, there are always two reasons that trigger a #NULL! error in your Excel worksheet: incorrectly using a space character instead of another operator (say comma or colon), or deploying the intersect operator for ranges that don’t intersect.

How to Fix the Null Error?

Errors are part and parcel of our day-to-day Excel life. But fortunately, Microsoft Excel gives several tools to tackle errors when they occur. In the above section of this tutorial, we have covered the different situations we can get the #NULL! in our Excel worksheet. Now let’s look at the various tricks to fix this error in excel:

1. Turn on the error checking feature

Remember to turn on the error checking feature in Excel worksheet. It helps you to easily fix the #NULL! Error. You only need to click on the Yellow Diamond icon next to the error cell. A window will be prompted, click on Help or Show Calculation Steps option (if available) and choose the solution that fits well for your data.

2. Using ‘Replace All’ Feature

Sometimes you repeat the same typing error in every formula. In such cases, it’s possible to “replace” the error with the correct operator. You can use this tool to fix the #NULL error, but make sure not to use the “Replace All” tool since you never know which problem has led to your error.

3. Fix the Error using Find and Replace

The best option to fix the typing error is by using the “Find and Replace” operation. As it allows you to use the “Find Next” operation, which further helps you to check whether your formula is missing a colon, comma, or some other problem is there with the specified ranges.

Things to Remember About #NULL Error in Excel

• Make sure to turn on the error checking feature in the Excel worksheet. It helps you to easily fix the #NULL! Error. You only need to click on the Yellow Diamond icon next to the error cell. A window will be prompted, click on the Help or Show Calculation Steps option (if available) and choose the solution that fits well for your data.
• Your worksheet shows the #NULL! error when Microsoft can’t figure out the formula or range specified in the cell. It typically occurs in one of the cases. 1.) If the user has used a range operator that doesn’t exist or is invalid, 2) If the user tries to access the intersect operator where there is no intersection of ranges.
• While writing a formula, always make sure to put a colon, Comma, Space, and mathematical signs (plus operator (+)) accurately to dodge the #NULL! Error in your worksheet.
• Another reason the #NULL! error occurs is when the intersect operator (the space character) is used, but specified ranges do not intersect with reach other.