WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Errors in Excel

Errors in Excel

List of Various Excel Errors

MS Excel is popular for only its most useful feature of automatic calculation, which we achieve by applying various functions and formulas. But while applying formulas in excel cell, we get various types of errors.

The error can be of

  1. #DIV/0
  2. #N/A
  3. #NAME?
  4. #NULL!
  5. #NUM!
  6. #REF!
  7. #VALUE!
  8. #####
  9. Circular Reference

Errors-in-Excel

We have various functions to deal with these errors, which are –

  1. IFERROR Function
  2. ISERROR Function
  3. AGGREGATE Function

Types of Errors in Excel with Examples

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

1 – #DIV/0 Error

#DIV/0 error comes when we work with a spreadsheet formula, which divides two values in a formula and the divisor (the number being divided by) is zero. It stands for divide by zero error.

#DIV/0 Error

Here, in the above image, we can see that number 90 is being divided by 0, that is why we get #DIV/0 error.

How to Resolve this Error?

The first and foremost solution is to divide only with cells that have a value that is not equal to zero. But there are situations when we also have empty cells in a spreadsheet. In that case, we can use the IF function as below.

Example – IF Function to Avoid #DIV/0 Error

Suppose we are getting #DIV/0 error as follows,

#DIV/0 Example 1

To avoid this error, we will use the IF function as follows,

#DIV/0 Example 1.1

2 – #N/A Error

This error means “no value available” or “not available.” It indicates that the formula is not able to find the value which we suppose the formula will return. While using VLOOKUP, HLOOKUP, MATCH, and LOOKUP function in excel, if we don’t find referenced value in the source data supplied by us as an argument, then we will get this error.

  • When the source data and the lookup value are not of the same data type.

#N/A Error

In the above example, we have entered Roll No. of Student as a number, but in the source data, roll numbers of students are stored as text; that is why #N/A error appears.

To resolve this error, either we can enter the roll number as text-only, or we can use the TEXT formula in excel in the VLOOKUP Function.

Solution 1: To enter Roll number as text

#N/A - Solution 1

Solution 2: Use the TEXT Function

Use the TEXT function in the VLOOKUP function for the lookup_value argument to convert entered numbers to TEXT.

#N/A - Solution 2

We could also use the IFERROR function in excel to display the message if VLOOKUP can’t find the referenced value in the source data.

#N/A - Solution 2.1

3 – #NAME? Error

This error is displayed when we usually misspell the function name.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

#NAME? Error

We can see in the above image that VLOOKUP is not spelled correctly; that is why #NAME? error is being displayed.

To resolve the error, we just need to correct the spellings.

#NAME? Solution

4 – #NULL! Error

This error is usually displayed when cell references are not specified correctly.

We get this error when we do not use the space character appropriately. The space character is called Intersect Operator, as this is used to specify the range which intersects each other at any cell.

In the below image, we have used the space character, but the ranges A2:A12 and B2:B12 is not intersecting; that is why this error is being displayed.

#NULL! Error

In the below image, we can see that the sum of range B2:B12 is being displayed in cell D2 as while specifying a range for SUM function, we have picked up two references (with space character), which overlaps each other for range B2:B12. This is the reason why the sum of B2:B12 range is being displayed.

#NULL! Solution

#NULL! Error can also be displayed when we use Intersect Operator (Space Character) instead of

  • Mathematical Operator (Plus Sign) to sum.
  • Range Operator (Colon Sign) to specify the start and end cell for a range.
  • Union Operator (Comma Sign) to separate individual cell references.

5 – #NUM! Error

This error is usually displayed when a number for any argument of a function is found invalid.

Example 1

To find out the square root in excel of a negative number, which is not possible as the square of a number always has to be positive.

#NUM Error

To solve the error, we just need to make the number positive.

#NUM Solution

Example 2

MS Excel has a range of numbers that we can use. The number smaller than the shortest number or number greater than the longest number as a result of the function can return an error.

#NUM Error Example

Here we can see that we have written formula as 2^8000, which yields results greater than the longest number; that is why #NUM! error is being displayed.

6 – #REF! Error

This error stands for reference error. This error usually comes when

  1. We have deleted the cell accidentally, which we have referenced in the formula.
  2. We cut and paste the referenced cell in different locations.

Formula Error - Example 7

As we deleted cell B7, then the cell C7 shifted left to take the place of B7, and we got a reference error in the formula as we deleted one of the referenced cells of the formula.

7 – #VALUE! Error

This error comes when we use the wrong data type for a function or formula. For example, only numbers can be added. But if we use any other data type like text, then this error will be displayed.

Formula Error - Example 8

8 – ###### Error

This error is displayed when the column width in excel is not enough to display the stored value in the cell.

Example

In the below image, there are dates and times written in the cells. But as column width is not enough, that is why ##### is being displayed.

Formula Error - Example 9

To resolve the error, we just need to increase the column width as per requirement using the ‘Column Width’ command available in the ‘Format’ menu in the ‘Cells’ group under the ‘Home’ tab, or we can just double click on the right border of the column.

Circuluar References

9 – Circular Reference Error

This type of error comes when we reference the same cell in which we are writing the function or formula.

Formula Error - Example 10

In the above image, we can see that we have got a sum as 0 as we have referenced B4 in the B4 cell itself for calculation.

Whenever we create this type of circular reference in excel, Excel alerts us about the same too.

Error MSg

To resolve the error, we just need to remove the reference for the B4 cell.

Formula Error - Example 10.1

Function to Deal with Excel Errors

1 – ISERROR Function

This function is used to check whether there would be an error after applying the function or not.

Formula Error - Example 11

2 – AGGREGATE Function

This function ignores error values. When we know that there can be an error in the source data, then we need to use this function instead of SUM, COUNT function, etc.

Example

We can see that the AGGREGATE function avoids error values.

Formula Error - Example 12

Things to Remember

  • To resolve any error in the formula, we can take online help also. We just need to click on the ‘Insert Function’ button under the Formula tab and choose “Help on this function.”

Formula Error - Things to remember

  • To avoid #NAME error, we can choose the desired function from the drop-down list opened when we start typing any function in the cell, followed by the ‘=’ sign. To choose a function, we just need to press the ‘Tab’ button on the keyboard.

Recommended Articles

This has been a guide to Errors in Excel. Here we discuss the top types of errors in excel and functions to deal with them with the help of examples. You can learn more about excel from the following articles –

  • VLOOKUP Errors in Excel
  • Calculate Standard Error
  • Shortcut to Edit Excel Cells
  • Payslip Template in Excel
7 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download Errors Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More