EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • 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
  • Log in
  • Sign Up
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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
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

Download NULL Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download NULL Excel Template

EDUCBA

डाउनलोड NULL Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW