What Are Excel Errors and How To Fix Them?

What Are Excel Errors and How To Fix Them?

6 mins read3.8K Views 1 Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Jun 18, 2024 17:40 IST

MS Excel is a powerful tool for working with data, but it can also be frustrating when errors occur. While some errors are easy to fix, others can be more difficult to troubleshoot. By understanding the different types of Excel errors, you can learn how to avoid them and quickly fix them when they occur. This will help you save time and ensure that your Excel workbooks are accurate and error-free. This article will discuss the different types of Excel errors you might encounter and how to fix them.

2022_09_Types-of-Errors-in-Excel-1.jpg

When we work with formulas in Excel, there are chances that we get errors at some point. These Excel errors are usually presented with strange letters and numerals or exclamation marks containing little information about what is happening in the formula we are executing.

You have likely seen Excel errors like #N/A, #VALUE!, #REF!, etc. These errors are commonly caused by the incorrect use of different formulas.

Here, we will walk you through the most common errors in Excel, show you what each one means, and, most importantly, how you can troubleshoot or fix Excel errors.

TYPE OF ERROR

TYPE OF ERROR MEANING DETAIL
#N/A Not available Can’t find the value you’re looking for
#VALUE! Invalid value Wrong arguments in the formula
#REF! No references We remove references that are part of the Excel formula
#NAME? Can’t find the name Excel formula typing error
#DIV/O! Divided by zero The denominator is zero.
######### Can’t display value Can’t display cell content
#NULL! Empty value Can’t determine a range of formula
#NUM! Invalid number Invalid numeric values

Error #N/A

Meaning: Not available. 

It usually occurs when the Excel lookup functions like VLOOKUP, HLOOKUP, MATCH, and XLOOKUP cannot find the value you are looking for within the formula in the specified range.

How to Fix Excel Error #N/A

Check the formula to ensure no inconsistencies, primarily if it refers to other sheets to validate whether the range is correct or if the information has been deleted.

When performing the search and verifying that the Excel formula is correct and the information is not there, we will get the #N/A error. To avoid this error, we can use an error handling function like IFERROR, which will help us put a text like “Value not found” instead of Error #N/A.

Must Read – What is MS Excel?

What is Filter in Excel?
What is Filter in Excel?
Filter in Excel is a valuable tool to analyze data quickly. In this article, you will learn how to use filters in Excel.
How to Use Excel Filter Shortcut?
How to Use Excel Filter Shortcut?
Excel Filter shortcuts are keyboard combinations that you can use to apply filters to your data quickly and easily. The article will explain different ways to use Excel Filter shortcuts,...read more
4 Ways to TRANSPOSE in Excel – Shiksha Online
4 Ways to TRANSPOSE in Excel – Shiksha Online
Learn how to transpose data in Excel and quickly change the orientation of columns and rows in a spreadsheet.

Error: #VALUE!

Meaning: Invalid value.

This is one of the most frequent Excel errors, particularly when we enter erroneous data or arguments in our formulas or functions, such as spaces, characters, texts, or formulas requiring numbers.

How to Fix Error #VALUE!

We must ensure that the Excel function or formula parameters are correct. For example, if you perform a mathematical operation such as multiplication, you should check that all the parameters are numeric. If you still see an error, check that no special characters or blank spaces generate it.

Do you want to learn Excel? Become an expert with our MS Excel Courses.

Error #REF!

Meaning: There is no reference.

This type of error in Excel is also one of the most frequent. It generally occurs when we accidentally delete or replace information about the values ​​that make up an already established function or formula in Excel.

How to fix the #REF!

To solve this error, it is convenient that you undo all the actions to recover the information that has been deleted or accidentally changed. Otherwise, we would have to formulate again.

How to Calculate the Difference Between Two Dates in Excel?
How to Calculate the Difference Between Two Dates in Excel?
Whether you need to determine the number of days between two events, measure the duration of a project, or calculate an individual’s age, Excel offers a range of functions and...read more
150+ Keyboard Shortcuts In Excel
150+ Keyboard Shortcuts In Excel
Excel users who enter a lot of data with the keyboard can boost their productivity by learning Excel shortcut keys, which allow them to access the program’s commands much faster....read more

Error: #NAME?

Meaning: Can’t find the name.

A typo in the formula commonly causes this error in Excel or because one or more values ​​entered as arguments in the function cannot be computed.

For example, the error can occur when we mistype any formula, “sm” instead of “SUM” to execute the formula in Excel.

Error: #NAME?

This error suggests that you need to check and correct the syntax. 

Error: #NAME? 2

Now, you can see that Excel makes the correct calculation.

Another case is if you have written the formula correctly, but you need to enter the arguments correctly, such as specifying the range of the sheet in the VLOOKUP function.

How to fix the #NAME error?

  • If there is any error in the formula, we should resolve it. Avoid using any error-handling functions such as IFERROR to cover up.
  • We must verify that the formula is well written to solve this Excel error. If the error continues verifying the Excel function or formula, use the formulas tab and search for the function to insert and follow the steps to enter the arguments correctly.

Must Explore – Free MS Excel Courses

Mail Merge in Excel – Shiksha Online
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel is widely used to generate large numbers of documents simultaneously while sticking to an agreed-upon or required format. The unique information for each card or label...read more
How to Split Cells in Excel?
How to Split Cells in Excel?
Splitting cells in Excel involves dividing the contents of a single cell into multiple cells. This can be incredibly useful for organizing and formatting data in a way that makes...read more
What is Conditional Formatting in Excel?
What is Conditional Formatting in Excel?
Conditional Formatting is a popular tool in Excel that allows adding a different format to the cells per their conditions. This way, we can easily see if the value of...read more

Error #DIV/O!

Meaning: Divided by zero.

This error in Excel commonly occurs when the denominator is zero.

Error #DIV/O!

How to fix the #DIV/O!

To handle this type of error, use the IF function, for example, =IF(A3,A2/A3,0), to return 0.

Error #DIV/O! 2

We can also use =IF(A3,A2/A3,””) to return an empty string. In the below example, we have used =IF(A3,A2/A3,”No Input”) to get the result No Input.

Error #DIV/O! 3

Useful Read – Most Useful Excel Formulas

Error: #####

Meaning: Cannot display value.

It usually occurs when the cell size is not large or wide enough to display the content.

How to fix error ######

Solving this is very simple. It is only to enlarge the cell and display the content correctly.

Difference Between Formula and Function in Excel
Difference Between Formula and Function in Excel
Formula and Function in Excel sound similar and are often confusing. A formula is a user-created expression that combines operators, cell references, and constants to perform customized calculations or data...read more
Flash Fill in Excel – Shiksha Online
Flash Fill in Excel – Shiksha Online
Flash Fill is a powerful and time-saving feature in Microsoft Excel that can transform how you work with data. It allows you to extract, combine, or reformat information quickly and...read more
ROW and COLUMN in Excel
ROW and COLUMN in Excel
Excel, a powerful spreadsheet software, utilizes this grid-like structure to organize and analyze data effectively. Rows, represented by horizontal divisions, traverse the spreadsheet from top to bottom, while columns, represented...read more

Error #NULL!

Meaning: Empty value.

This error occurs when the wrong reference operator is used or not found. Usually, when Excel cannot determine or find the specified range, we get the Excel errors like #NULL!

For example, we have wrongly selected the cell references in the below data set, which makes the formula incorrect.

Error #NULL!

Instead of =SUM(G5 G23), we need to write =SUM(G5:G23)

Error #NULL! 2

How to fix the #NULL error!

Ensure that the formulas are written correctly, and the operators are used correctly. 

Validate if these operators are being used correctly:

  • Range Operator: Refers to the colon (:) and determines the range of the cells 
  • Union Operator: The semicolon (;) is called a union operator because it is generally used to add references to an operation. For example =SUM(G5:G23). In the example, we add the ranges of columns G5 to G23 to get the correct sequence.

Error: #NUM!

Meaning: Invalid number.

Excel error occurs when Excel usually cannot display the result of a mathematical operation. This type of error can occur for two reasons:

For example, a formula or function contains numeric values that aren’t valid. Calculating the square root of a negative number =SQRT(-8). This is because, in Excel, imaginary numbers are not considered.

Error: #NUM!

It can also be because the result of an operation is too large or small for Excel to display. For example, Calculating the power of 1000 raised to 300 =POWER(1000,300) will give us an Excel error type #NUM!

Error: #NUM!2

How to fix the #NUM!

To fix this error in Excel, change the number of times Excel iterates the formula –

  1. Go to File < Options.
  2. The dialogue box will open. Go to Formulas and then Calculation options. Check the Enable iterative calculation box.
  3. You will see two options – Maximum Iterations and Maximum Change. In the Maximum Iterations box, mention the number of times you want Excel to recalculate. The number of iterations is directly proportional to the time Excel takes to calculate a worksheet.
  4. Now, type the amount of change you will accept between calculation results in the Maximum Change box. The smaller the number, the less time Excel calculates a worksheet.
Error: #NUM! 3

Tell us in the comments what Excel errors you commonly encounter when using Excel. And how do you correct these types of errors in Excel?

———————————————————————————-

Read More in Excel

MIS Reports in Excel | Excel Interview Questions | Basic Excel Formulas | Difference Between Formula and Function in Excel | Average Function in Excel | Introduction to MS Excel | HLOOKUP in Excel | ROW and COLUMN in Excel | Financial Modelling in Excel | Percentage In Excel | Remove Duplicates In Excel | Merge Cells in Excel | MIN and MAX Functions in Excel | Combine Text Strings in Excel | Import Data from PDF to Excel | Pivot Table In Excel | How To Enable Macros In Excel | How to Import Text Files to Excel | TRIM Function in Excel

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments

(1)

There is a small mistake. The meanings of the errors "#NAME? , #DIV/0" are jumbled.

Reply to Anish Vikram Varma

R

Rashmi KaranManager - Content

Thanks Anish, for pointing out the mistake. It is corrected now.