EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Excel Tips NULL in Excel

NULL in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 22, 2023

NULL in Excel

NULL in Excel (Table of Contents)
  • What is Null Error in Excel?
  • Examples of NULL in Excel

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.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

So here below, we will show you what it looks like.

null excel 1

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.

You can download this NULL Excel Template here – NULL Excel Template

Example #1

NULL in Excel 1-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).

NULL in Excel 1-2

We have given space instead of the plus sign(+), so we got the #NULL! Error in Column B5.

NULL in Excel 1-3

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.

NULL in Excel 1-4

After applying the formula, the result is shown below.

NULL in Excel 1-5

Note: #Null! The error can occur when the cell’s start and endpoints are separated by a space instead of using the range operator, the “Colon (:)”.

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.

NULL in Excel 2-1

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

NULL in Excel 2-2

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.

NULL in Excel 2-3

After applying the formula, the result is shown below.

NULL in Excel 2-4

Note: #NULL! Errors can occur when the Individual cell references in a formula are separated by space instead of the Union operator, the “Comma (,)”.

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.

Adding up Multiple Ranges 3-1

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.

null gross profit

To eradicate the Null Error, input “Comma (,)” between both ranges.

Adding up Multiple Ranges 3-3

After using the above formula, the output is shown below.

Adding up Multiple Ranges 3-4

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.

intersect 4-1

After using the formula, the output is shown below.

intersect 4-2

To eradicate the Null Error, input “Comma (,)” between both ranges.

intersect 4-3

After using the formula, the output is shown below.

intersect 4-4

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.

intersect 5-1

Once the formula is applied, we will get Peter’s sales value in February, which is 277.

intersect 5-2

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)”.

intersect 5-3

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.

intersect 5-4

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 –

  1. Excel Spell Check
  2. SUM Cells in Excel
  3. VBA ISNULL
  4. SUMIF Formula in Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download NULL Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download NULL Excel Template

EDUCBA

डाउनलोड NULL Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW