Precision is paramount. Yet, even the most beautifully crafted Excel sheet can encounter errors. One common error most Excel guru’s face is the #null error.
It’s a broad topic, so cutting to the chase, null errors are missing values within a dataset. Addressing the #null error in Excel is crucial for maintaining an error-free worksheet.
In this post, we will help you treat the error efficiently so that you can accurately analyze the workbook and serve as a solid foundation for your business decisions.
Identify #Null Errors in Excel
Excel refers to #Null errors as situations where a cell contains no value or invalid value that disrupts calculations or functions. You will see error codes such as #NULL!, #DIV/0!, or #VALUE! in the cell.
Think of null errors like a missing ingredient in a recipe. If a recipe calls for sugar, and you forget to add it, the dish might not turn out as expected.
Similarly, in Excel, if a cell that should contain a number is left empty, it can cause errors in calculations.
So, how to visually identify such errors?
Well, cells with null errors are highlighted with a small triangle in the top left corner. This triangle indicates that there’s an error associated with that cell. Or, you can use in-built tools like “Trace Precedents” and “Trace Dependents” which help you identify which cells are involved in a particular calculation. Other than that, you can use the Error checking function as well.
How to Fix the #NULL! Error
There are two ways to fix a #null! Error in Excel. 1. Use a colon in between the two intersecting ranges. 2. Use a comma (,) separator to eliminate the space characters between the non-intersecting ranges.
Look at the example below:
Here, we wanted to sum the data between the two ranges A3 to B7 and C3 to C7. But look at the formula closely. It doesn’t have any separators. That’s why when you press Enter over this formula, you will be left with the null error.
So, by the example mentioned above, you must have guessed the three root causes for such an error.
- Skipping of a colon sign
- Skipping of a comma
- Adding extra spaces in between non-intersecting ranges
So, how to fix the null error?
Considering the above example, we need to add a comma in between the two ranges. So, the accurate formula would be:
Make sure that there is always a colon symbol while typing the ranges. If you want to add the colon automatically, then select the ranges using the left-click of your mouse instead of typing A2:B4.
You might be confused what we meant by adding extra spaces in between non-intersecting ranges. For that, you have to understand what is intersecting and non-intersecting ranges in Excel.
Example 1: Intersecting Ranges
Suppose you have the following data in Excel:
Range A: A1:A5 contains numbers from 1 to 5.
Range B: B3:B7 contains numbers from 3 to 7.
Here, we’ll calculate the sum of intersecting values between Range A and Range B.
Formula for intersecting sum: =SUM(A1:A5 * (B3:B7>=A1:A5) * (B3:B7<=A1:A5))
Result: The formula calculates the sum of values in Range A that are also present in Range B (i.e., numbers 3, 4, and 5). The result is 12.
Now, we’ll calculate the sum of non-intersecting values between Range A and Range B.
Formula for non-intersecting sum in Range A: =SUMIFS(A1:A5, A1:A5, “<>”&B3:B7)
Result: The formula calculates the sum of values in Range A that do not exist in Range B. The result is 1 + 2 = 3 (numbers 1 and 2 in Range A).
Example 2: Non-Intersecting Ranges
Suppose you have the following data in Excel:
Range X: X1:X5 contains text values: “Apple,” “Banana,” “Cherry,” “Date,” “Fig.”
Range Y: Y1:Y4 contains text values: “Grapes,” “Kiwi,” “Lemon,” “Mango.”
In this case, there are no intersecting ranges since the ranges X and Y do not share any common values.
But for calculating the non-intersecting ranges, you can use a formula to list items that are unique to each range (i.e., items in Range X but not in Range Y and vice versa).
Formula for unique items in Range X: =IF(ISNUMBER(MATCH(X1:X5, Y1:Y4, 0)), “”, X1:X5)
Result: The formula returns the text values “Apple,” “Banana,” “Cherry,” “Date,” and “Fig” since they are unique to Range X and not found in Range Y.
Similarly, you can create a formula for unique items in Range Y by comparing it to Range X.
These examples illustrate the concepts of intersecting and non-intersecting ranges in Excel, both with numerical data and text data
Final Words
Excel can’t calculate the formula properly if there is an extra space between non-intersecting ranges. Additionally, if you forgot to add colon and comma in between the data range, then Excel will show you a null error.
