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. The following 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 is a chance 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 to check 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 delete. 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. |
| #CALC! |
Calculation Error |
When Excel cannot perform a calculation as expected |
Check and rewrite your formula to ensure it adheres to the correct syntax and logic. |
Let us check these out in detail -
Error #N/A
Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
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 to help us put a text like “Value not found” instead of Error #N/A.
To boost your data analytical skills, start by learning MS Excel! We recommend selecting the most suitable MS Excel online courses according to your professional and personal goals. Learn how to use both basic and advanced Excel formulas and functions, dashboards, macros, pivot tables, and data analysis tools to improve business insights and reporting.
Read Later
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.
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 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 the formula contains an error, we should resolve it. Avoid using error-handling functions, such as IFERROR, to cover up.
- To solve this Excel error, we must verify that the formula is well written. If the error continues, verify the Excel function or formula, use the formulas tab to 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 too large or wide 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 an Excel error 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 that 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.
An Excel error occurs when Excel cannot display the result of a mathematical operation. This type of error can happen 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 Error!
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 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 the calculation results in the Maximum Change box. The smaller the number, the less time Excel takes to calculate a worksheet.
Error: #CALC!
Meaning: Calculation error. The #CALC! error occurs when Excel cannot perform a calculation as expected. This usually happens when a formula contains an operation that Excel cannot understand or compute.
Common Causes of #CALC! Error
- Using an invalid formula
- Incorrect use of array formulas
- Unsupported calculations
- Missing required arguments
- Issues with iterative calculations
How to Fix the #CALC! Error
- Check the formula for errors: Check the accuracy of the formula.
- Verify function arguments: Ensure all required inputs for a function are provided.
- Check for invalid array formulas: If using array formulas, make sure all elements are correctly structured.
- Use IFERROR to handle errors: Wrap the formula in IFERROR to display a custom message instead of #CALC!:
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.
Conclusion
Excel errors may seem puzzling at first, but most have straightforward reasons and quick solutions. When you know what each error is, such as #N/A for missing values or #NUM!, you will be able to fix incorrect numbers in no time and not repeat the same errors.
You can keep your Excel spreadsheets accurate and efficient by being mindful of formulas and double-checking your data inputs. Figuring out how to troubleshoot these errors is a desirable skill to have that will enable you to work more effectively with data and achieve the most from Excel.
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
Comments
(1)
Top Picks & New Arrivals
