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.
- 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 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.
Read More: Errors in Excel and Their Meaning (15 Different Errors)
Similar Readings
- [Fixed] Excel Print Error Not Enough Memory
- Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)
- How to Remove Value Error in Excel (4 Quick Methods)
- REF Error in Excel (9 Suitable Examples)
- Reasons and Corrections of NAME Error in Excel (10 Examples)
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.
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
- How to Calculate Tracking Error in Excel (with Detailed Steps)
- [Fixed!] Error Messages in Excel (10 Practical Solutions)
- How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)
- [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel (8 Reasons)
- Find Reference Errors in Excel (3 Easy Methods)
- How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)
- Excel VBA: Turn Off the “On Error Resume Next”