Updated August 22, 2023
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 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 that one of the cell contents violates one of Excel’s error checking rules.
So here below, we will show you what 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 displays some of the #Null! Errors are very common. We will show you the list of those common error values and some examples.
Example #1
As shown below, we have the employee’s 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.
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 columns 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! The 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 Multiple ranges. Instead of adding “Comma”, we give space for those individual ranges, which 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 used when you need the values from the intersection of one or more columns or rows. “Intersect Operator” allows performing the calculation on the ranges that intersect.
Let us use the intersection operator in the example below, 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 Peter’s sales value in February, which is 277.
If you want to calculate 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 Peter’s sales value, which is 1276 for both 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, you can easily fix your #NULL! An 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! The 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! The error gets returned when Excel can’t determine the range specified in the cell.
- Use of Colon, Comma, Space, and mathematical operator (plus sign (+) needs to be input correctly in the formula to avoid the #NULL! Error in Excel.
- Excel displays some of the #Null! Errors are very common. We will show you the list of those common error values along with some common causes and solutions which will help 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! An 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
