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


Download Practice Workbook

You can download the practice workbook here.


3 Possible Reasons with Solutions If Null Error Occurs in Excel

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 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 colon in between the cell range of the function.

Read More: [Fixed!] Error 509 in Excel (4 Possible Solutions)


Solution: Using Colon in Between Ranges

Now, we will resolve the issue by inserting a colon in between the cell 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

Read More: Errors in Excel and Their Meaning (15 Different Errors)


Similar Readings


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.

Read More: How to Fix #REF! Error in Excel (6 Solutions)


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.

Read More: VALUE Error in Excel: 7 Reasons with Solutions


Reason 3: Using Space Character Between Non-Intersecting Ranges

Here, we will deal with the Null Error that results from the inaccurate use of 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 be 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.

Read More: [Fixed!] NUM Error in Excel (4 Reasons with Solutions)


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. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo