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

Get FREE Advanced Excel Exercises with 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.

null error in excel

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

null error in excel from missing colon in formulas

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.

null error in excel from missing colon in formulas


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.

null error in excel

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

null error in excel from missing skipping commas in formulas

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.


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.


Related Articles


<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo