[Fixed!] Null Error in Excel (3 Possible Solutions)

In this article, we will discuss reasons for #Null! Error in Excel and their possible solutions. #Null! The error occurs when we forget to use a comma or colon in between cell references in a formula. It can also happen if we try to use two non-intersecting ranges in a formula separated by spaces.

[Fixed!] Null Error in Excel : 3 Possible Reasons with Solutions

In this article, we will talk about 3 possible reasons for #Null! Error in Excel and their solutions. Firstly, we will look into the error where a formula is missing a colon in between the cell ranges. Secondly, we will resort to the problem of the absence of commas in between the cell references in a formula. Finally, we will solve the problem of non-intersecting data ranges in a formula.

Reason 1: Missing Colon in Formulas

In this section, we will deal with the problem of the absence of a colon in a formula. This will result in a #Null! Error in Excel. Here, we have the dataset of salaries of different employees in a company. We will calculate the sum of the salaries of the employees.

At the start, click on the C11 cell and enter,

=SUM(C5 C10)

Then, hit Enter.
As a result, we will encounter a #Null! Error.

This is because of the absence of a colon in between the cell range of the function.

Solution: Using Colon in Between Ranges

Now, we will resolve the issue by inserting a colon in between the cells C5 and C10 inside the SUM function.

Now, click on the C11 cell and type,

=SUM(C5:C10)

Next, press Enter.
Consequently, we will get the sum of the values without any #Null! Error.

Reason 2: Skipping Comma in Formulas

In this instance, we will fix the #Null! Error resulting from the absence of commas in a formula. In this instance, we will sum up the salaries of the employees of a company. We will first look at reason for the #Null! Error. Then, we will go into the solution.

At the beginning, select the C11 cell and enter the following formula,

=SUM(C5,C6,C7,C8,C9 C10)

Then, hit Enter.
As a result, we will face a #Null! Error.

This is because of the absence of a comma in between the cell references inside the formula.

Solution: Applying Comma in Between Cell References

Here, we will deal with the #Null! Error by inserting a comma after the C9 cell in the formula.

Next, choose the C11 cell and enter,

=SUM(C5,C6,C7,C8,C9,C10)

Then, press Enter.
As a result, we will see no #Null! Error in the sheet.

Reason 3: Using Space Character Between Non-Intersecting Ranges

Here, we will deal with the #Null! Error that results from the inaccurate use of the Intersect Operator. In this instance, we have the sales data for the four quarters of different products of a company. We will first look into the cause behind the #Null! Error and then apply a solution to it.

To begin with, select the C11 cell and enter the following formula,

=SUM(C5:F6 C8:F10)

Then, press the Enter button.
As a result, we will encounter a #Null! Error from the formula.

This is because the C5:F6 and C8:F10 ranges do not have any common cells in between them. That means, they do not intersect. That is why the Intersect Operator or the space will return a #Null! Error.

Solution: Inserting Proper Cell Range

In this case, we will set the ranges inside the formula such that the two ranges intersect or have a common set of cells in between them.

Firstly, choose the C11 cell and insert,

=SUM(C5:F8 C8:F10)

Then, hit the Enter button.
Consequently, the #Null! Error will have vanished.

It happens because the C5:F8 and the C8:F10 ranges have a common set of cells in between them that are C7:F7. The SUM function returns the sum of the values inside those common cells.

Download Practice Workbook

You can download the practice workbook here.

Null Error.xlsx

Conclusion

In this article, we have talked about the reasons for #Null! Error in Excel and their possible solutions. These solutions will allow users to deal with the #Null! Error properly. If you have any questions regarding this essay, feel free to let us know in the comments.