What Are Excel Errors and How To Fix Them?
MS Excel is a powerful tool for working with and handling data. However, it can also get 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 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.
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. The incorrect use of different formulas commonly causes these errors.
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 | Solution |
|---|---|---|---|
| #N/A | Not available | The value sought cannot be found | Double-check your formulas and look closely at which sheets or rows may have been deleted or referenced incorrectly. |
| #VALUE! | Invalid value | Wrong arguments in the formula | An easy fix is checking your formula to ensure you only used numbers. If you still see an error, look for blank cells, missing formulas that link to cells, or any special characters you may be using. |
| #REF! | There is no reference | References that are part of the Excel formula are removed | Before pasting over a set of cells, make sure no formulas refer (hence, "REF") to the cells you are deleting. Also, double-check which formulas refer to those cells when deleting cells. |
| #Name? | Can't find the name | Excel formula typing error | If the formula is written correctly and your spreadsheet still displays an error, Excel is likely getting confused with one of your inputs within the formula. |
| #DIV/O! | Divided by zero | The denominator is of zero value. | Change the cell value to a value that is not equal to 0, or add a value if your cell is blank. |
| ######## | Unable to display value | Cell contents cannot be displayed. | Click the right edge of the column header and increase the width of the column. |
| #NULL! | Empty value | The range of the formula cannot be determined. | Use a colon to separate the first cell from the last cell when referring to a continuous range of cells in a formula. On the other hand, you must use a comma when referring to two cells that do not intersect. |
| #NUM! | Invalid number | Invalid numeric values | Check if you've entered any formatted currencies, dates, or special symbols. Then, remove those characters from the formula, keeping only the numbers. |
Let us check these out in detail -
Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
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?
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.
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.
This error suggests that you need to check and correct the syntax.
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
Error #DIV/O!
Meaning: Divided by zero.
This error in Excel commonly occurs when the denominator is zero.
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.
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.
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.
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.
Instead of =SUM(G5 G23), we need to write =SUM(G5:G23)
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.
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!
How to fix the #NUM!
To fix this error in Excel, change the number of times Excel iterates the formula –
- Go to File < Options.
- The dialogue box will open. Go to Formulas and then Calculation options. Check the Enable iterative calculation box.
- 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.
- 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.
Tips for Quick Error Fixes
- Organizing your data effectively with features like Tables, Filters, and Sort can save you time and reduce errors in Excel.
- Before applying a formula to a large dataset, try it on a small set to ensure it works as expected.
- Use Data Validation to control what can be entered in cells (e.g., numbers only), reducing the chances of errors.
- Visualizing data with Tables, Charts, PivotTables, and Insights makes it easy to spot trends and analyze large data sets.
- Advanced functions like VLOOKUP, XLOOKUP, and IF help you easily manage and manipulate complex data.
- Using Error Checking, Change Tracking, and Auto Save ensures data accuracy and recoverability, protecting your work from unexpected errors and problems.
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
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
Top Picks & New Arrivals
Comments
(1)
A
a year ago
Report Abuse
Reply to Anish Vikram Varma
R
Rashmi KaranManager - Content
Report Abuse