NULL in Excel (Table of Contents)
What is Null Error in Excel?
Null is an error value in a cell when Excel cannot properly evaluate a worksheet formula or function. This error is shown when you use an incorrect range operator in a formula or when you use an intersection operator. The error value itself plus the Error Option Button, displayed in the error formula, helps identify the problem by showing a small green triangle containing error values. The small green triangle indicates one of the cell contents is violating one of Excel’s errors checking rules.
So here below, we will show you how it looks like.
As shown in the above screenshot, the green triangle and a yellow diamond-shaped button which is next to that cell marked with Red Arrows and when you click that Yellow Diamond shaped icon in the cell will show you the Excel’s error options button, which contains the options in the drop-down list to correct the perceived error.
Examples of NULL in Excel
Excel display some of the #Null! Errors which are very common. We will show you the list of those common error values along with some examples.
Example #1
As shown below, we have the employees name in Column A and Productive Hours in Column B, so if we want to get the Total Productive hours of the below employees, we will Sum the hours from Column B2 to B4 but we have to input the Formula (=B2+B3 B4).
We have given space instead of the plus sign(+), so we got the #NULL! Error in Column B5.
4.9 (8,717 ratings)
View Course
Now to rectify the error, we can click the Yellow Diamond Icon, and we will show that we need to input the correct formula in the formula bar, which is (=B2+B3+B4), which will give us the Total Productive Hours and rectify the #NULL! Error. Refer to the below screenshot.
After applying the formula, the result is shown below.
Example #2
Suppose we need to take the Total Gross Profit of the resources in the below example for 12 resources with their gross profits in column B3 to B14.
You can see that in column B13, we are getting the #NULL! Error. This is because, in the Formula bar, we have input the formula (=SUM (B2 B13)).
Suppose you want to rectify the #NULL! error you can add “Colon (:)” instead of “Space” in the formula bar (=SUM (B2:B13)) as shown in the below screenshot.
After applying the formula, the result is shown below.
Example #3
Another example shows where you are adding up Multiple ranges, and instead of adding “Comma”, we give space for those individual ranges, which as a result will give us the NULL Error and rectify that we will use Comma as the Union operator.
We need the Total Gross Profit of resources for both January and February month, so we insert the Sum formula for the Total Gross Profit (=SUM (B1:B12 C1:C12)) in the formula bar, but it will give us the #NULL! Error.
To eradicate the Null Error, input “Comma (,)” between both ranges.
After using the above formula, the output is shown below.
Example #4
In the below example, we have taken two students marks in 5 subjects in 2 different rows, which do not intersect. This is when we get the Null error in Excel.
After using the formula, the output is shown below.
To eradicate the Null Error, input “Comma (,)” between both ranges.
After using the formula, the output is shown below.
Example #5
Intersect operator is basically used when you need the values which come from the intersection of one or more columns or rows. “Intersect Operator” allows performing the calculation on the ranges that intersect.
Let us try to use the intersection operator in the below example where you need to calculate Peter’s sales in February Month. We would input the Formula in the formula bar =(B3:E3 C2:C5) in cell B7.
Once the formula is applied, we will get the sales value for Peter in February month which is 277.
If you want to calculate the sales of Peter for January and April also, you could use=SUM (Peter Jan, Peter Apr), which means we need to input the formula in the formula bar “=SUM (B2:B5 B3:E3, E2:E5 B3:E3)”.
In cell B6, to get the sales value of Peter which is 1276 for both the months. The space between the two named ranges is the Intersect Operator.
Things to Remember About NULL in Excel
- If error checking is turned on in Excel, then you can easily fix your #NULL! error by clicking the Yellow Diamond icon next to the cell, which shows the error. Click on Help or Show Calculation Steps if it’s available and pick the resolution that works for your data.
- Excel #NULL! error will appear in case of two reasons. Whether you have used an incorrect range operator or trying to use the intersect operator where there is no intersection of ranges. #Null! Error gets returned when Excel can’t figure out the range specified in the cell.
- Use of Colon, Comma, Space, and mathematical operator (plus sign (+) needs be to input correctly in the formula to avoid the #NULL! Error in Excel.
- Excel display some of the #Null! Errors which are very common. We will show you the list of those common error values along with some common causes and solutions which will be helpful to correct them. #Null! Error Values occur when incorrectly or unintentionally, a “SPACE” character is used as the intersect operator between two or more cells. This error is shown when you use an incorrect range operator in a formula. The error occurs if the Multiple cell references in a formula are separated by a space instead of a mathematical operator such as a plus sign (+).
- #NULL! error occurs when the intersect operator (the space character) is used intentionally, but specified ranges do not intersect.
Recommended Articles
This is a guide to NULL in Excel. Here we discuss How to use NULL in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
