What are Excel Errors and How to Fix them

This page is an in-depth guide on different types of Excel errors and ways to fix them. An Excel error is simply a mistake. The Excel errors happen when something goes wrong in a formula, calculation, or data input, leading to incorrect or unexpected results. The Microsoft Excel displays error messages to warn us when any error happens so that we can fix them.

What are the Types of Excel Errors and how to Fix them?

Let us learn all these errors in detail with solution:

1. #CALC! Error

• The #CALC! error occurs when Excel cannot process a calculation.
• The #CALC! error in Excel is caused by invalid formulas, incorrect array usage, or unsupported operations.
• This Excel error happens when some cells have missing data or when formulas that depend on previous results do not calculate correctly.

How to Fix #CALC! Error in Excel:

• Double check the formula to ensure there are no mistakes or incorrect references.
• Verify that all required values and arguments are properly entered in the function.
• If using an array formula, make sure it follows the correct structure and is applied properly.
• Use the IFERROR function to display a custom message if error occurs.

2. #DIV/0! Error

The #DIV/0! error in Excel occurs when you try to divide a number by zero or an empty cell.

How to Fix #DIV/0! Error in Excel:

• Make sure the denominator (bottom number) is not zero or empty cell.
• Ensure it only includes numbers, not text or symbols.

3. #N/A Error

• The #N/A error in Excel represents “Not Available.”
• The #N/A error occurs when lookup functions (e.g., VLOOKUP, HLOOKUP, MATCH, XLOOKUP) cannot find the specified value in the given range.
• This Excel error is the most common error and found frequently in day-to-day tasks.

How to Fix #N/A Error in Excel:

• Make Sure the Value Exists:
  • If the value you are searching for is not in the data, the Excel will return #N/A error.
  • So, you should check your table or range to confirm the value is present.
• Check for Spelling Mistakes or Extra Spaces:
  • Make sure the lookup value is spelled correctly and has no extra spaces.
  • You can use the TRIM function to remove unwanted spaces.
  • You can also use the wildcard feature for advanced lookup.
• Fix Number and Text Mismatches:
  • Sometimes, numbers are stored as text, which causes lookup failures.
  • You can convert the text to numbers using VALUE function.
• Use Correct Column or Row:
  • You should use the correct column/row number or range in the formula.
  • The column/row number you are looking for should be within the range you selected.
• Check Exact vs. Approximate Match:
  • You should use FALSE or 0 for an exact match in the lookup formula.
  • The TRUE or 1 for approximate match and it may not give the exact result.
• Use Better Function:
  • Always try to use the new and advanced function instead of old functions.
  • For example, you should use XLOOKUP function instead of VLOOKUP or HLOOKUP function.
• Use IFERROR or IFNA to Hide Errors:
  • You can replace #N/A with a message like “Not Found” or blank (“”) using IFERROR function.
  • Alternatively, you can use the IFNA function to do the same since its specifically designed to handle the #N/A error in Excel.

4. #NAME? Error

• The #NAME? error in Excel occurs when a formula contains unrecognized text or references.
• This Excel error usually happens due to misspelled function names, missing quotation marks around text values, usage of functions not supported in the current Excel version, or missing add-ins required for custom functions.
• Identifying the exact cause of the #NAME? error helps in troubleshooting and ensuring the formula calculates correctly.

How to Fix #NAME? Error in Excel:

• Misspelled Function Name:
  • A typing error in the function name can lead to the #NAME? error in Excel.
  • Ensure function names are spelled correctly (e.g., =SUM(A1:A10), not =SUME(A1:A10)).
• Formula Entered as Text:
  • Forgetting the equal sign (=) at the beginning causes Excel to treat the entry as plain text.
  • Ensure formulas start with = (e.g., =SUM(A1:A10)) so Excel recognizes them correctly.
• Missing Quotation Marks in Text:
  • Text not enclosed in quotes is misinterpreted as a named range or function.
  • Always enclose text values in double quotes (e.g., =IF(A1=”Yes”, “OK”, “No”)).
• Disabled or Missing Add-ins:
  • If an add-in is disabled, any function from it will not work.
  • Go to File > Options > Add-ins and enable the required add-ins.
• Excel Version Compatibility Issues:
  • Some functions (e.g., XLOOKUP) are only available in newer Excel versions.
  • Check if your Excel version supports the function or use an alternative function.

5. #NULL! Error

• The #NULL! error in Excel occurs when an incorrect range operator, such as a space instead of a comma or colon, is used in a formula.
• This Excel error appears when a formula tries to use ranges that do not intersect or refers to cells that have been deleted or are invalid.

How to Fix #NULL! Error in Excel:

• Use the Correct Symbols for Ranges: The most common reason for the #NULL! error in Excel is using the wrong symbol to define a range or list of cells. The Microsoft Excel requires specific operators for different types of cell references, such as:
  • Colon (:) is used to define a range of cells (from one cell to another).
    • ❌ Problem: =SUM(A1 A10) (#NULL! error because space is used instead of colon)
    • ✅ Fix: =SUM(A1:A10) (Adds all values from A1 to A10)
  • Comma (,) is used to add multiple separate cells to a formula.
    • ❌ Problem: =SUM(A1 C1 E1) (#NULL! error because spaces act as an intersection operator)
    • ✅ Fix: =SUM(A1, C1, E1) (Adds only A1, C1, and E1)
  • Space ( ) is treated as an intersection operator (which looks for overlapping cells).
    • ❌ Problem: =SUM(A1A2) or =SUM(A1 A2) (#NULL! error if A1 and A2 do not overlap).
    • ✅ Fix: =SUM(A1,A2) or =SUM(A1:A2) (Use comma or colon in between the cell references).
• Use the Correct Named Ranges: A formula using a named range will return a #NULL! error if the named range does not exist, is misspelled, or has been deleted.
  • ❌ Problem: =SUM(ProfitTable) (If “ProfitTable” has not been defined as a named range, Excel cannot recognize it, resulting in a #NULL! error).
  • ✅ Fix: =SUM(Profits_Table) (Go to Formulas > Name Manager, verify and update the correct name in the formula)
• Verify Your Cell References: If a formula references a cell that does not exist (due to a typing mistake or an incorrect range), Excel may return the #NULL! error. So, make sure you use the correct cell references in your formula to avoid the #NULL! error.

6. #NUM! Error

• The #NUM! error in Excel occurs when Excel cannot process a calculation.
• This Excel error is caused by invalid formulas, incorrect array usage, or unsupported operations.
• It also happens when some cells have missing data or when formulas that depend on previous results do not calculate correctly.

How to Fix #NUM! Error in Excel:

• Handling Invalid Numeric Input:
  • Some functions require specific types of numeric values. If an invalid number is used in the formula, Excel returns #NUM! error.
  • For example – If you will use a negative number inside the SQRT or LOG function, it will return #NUM! error because it accepts only positive values.
  • So, make sure the entered data follows the required rules.
  • You can use the ABS function to convert negative numbers to positive before using the functions which requires only positive values.
  • You can also use any logical function like IF or IFS with conditions to prevent errors.
• Fixing Overflow Errors (Extremely Large or Small Numbers):
  • If a formula results in a number exceeding the numeric limit of Excel, it will return #NUM! error.
  • So, you should reduce the size of numbers in calculations to avoid exceeding Excel’s limits.
  • You can also break down calculations into smaller steps to prevent overflow.
• Solving Iteration Limit Errors in Functions:
  • Some functions, such as IRR and NPV, require multiple calculations. If Excel cannot determine a valid result within its iteration limit, it returns #NUM! error.
  • So, you should provide a better guess for Excel to find a solution more easily.
  • You can also increase Excel’s iteration limit. To do that go to File > Options > Formulas and check Enable iterative calculations under the Calculation options. Increase the Maximum Iterations (e.g., set it to 1000) and increase the Maximum Change value for more accurate results.

7. #REF! Error

• The #REF! error in Excel occurs when a formula tries to use a cell that no longer exists.
• This Excel error usually occurs if you delete a cell, remove a worksheet that the formula refers to, or use an incorrect cell range.

How to Fix #REF! Error in Excel:

• Identify the Error:
  • Click on the cell displaying #REF! error.
  • Look at the formula bar to find the part of the formula causing the error.
• Undo the Last Action:
  • If you have accidentally deleted a cell reference, press undo button to fix it.
  • If Excel is closed and reopened, undo may not work, and you need to fix the formula.
• Manually Correct Broken References:
  • Click the formula and replace #REF! with the correct reference.
  • Example: If you see =A1 + #REF!, update it to reference a valid cell, such as =A1 + B1.
• Use Error Checking Tool:
  • Go to Formulas > Error Checking in the Formula Auditing group.
  • Excel will analyse the sheet and highlight formulas with errors.
  • You can follow the suggested fixes or manually edit the formula.
• Use Alternative Functions:
  • Instead of INDIRECT function, you can use INDEX or OFFSET function, which are more flexible when dealing with dynamic data.
  • When dealing with external references, use Power Query to create a more stable link.

8. #VALUE! Error

• The #VALUE! error in Excel occurs when a formula encounters an incorrect data type, unexpected text values, or improper function arguments.
• Instead of performing calculations, Excel displays #VALUE! error, indicating an issue with the input data.

How to Fix #VALUE! Error in Excel:

• Ensure Data Types Are Correct:
  • ❌ Problem: The formula expects a numerical value but encounters text, which prevents calculations.
  • ✅ Fix: To fix the #VALUE! error in Excel, use ISNUMBER function to check the data type, VALUE function to convert text to numbers, and ensure that arithmetic operations do not involve text values.
• Remove Extra Spaces or Hidden Characters:
  • ❌ Problem: Cells contain extra spaces or hidden characters that make numbers unreadable by formulas in Excel.
  • ✅ Fix: Use TRIM function to remove extra spaces and CLEAN function to eliminate non-printable characters from the data.
• Convert Numbers Stored as Text:
  • ❌ Problem: Numbers are stored as text, preventing Excel from performing calculations correctly.
  • ✅ Fix: Convert the text-based numbers using VALUE function or use the Text to Columns feature under the Data tab to convert them into numeric values.
• Use Proper Function Arguments:
  • ❌ Problem: The function is receiving the wrong data type, such as text instead of numbers, leading to an error.
  • ✅ Fix: Ensure that numbers are correctly formatted using TEXT function, and that functions like DATE are supplied with numeric values rather than text-based numbers.
• Convert Boolean Values Before Calculations:
  • ❌ Problem: Logical values like TRUE and FALSE are used in calculations without proper conversion, leading to errors.
  • ✅ Fix: Convert Boolean values using =IF(A1=TRUE, 1, 0) + B1 to ensure they work correctly in mathematical operations.
• Handle Errors from Incorrect Cell:
  • ❌ Problem: The formula refers to a blank cell or a cell containing another error, causing the calculation to fail.
  • ✅ Fix: Use IFERROR function to replace errors with a specified value and prevent formula failure.

9. #### Error

• The #### error in Excel occurs when a cell’s content is too large to fit within its column.
• This Excel error is not an actual error but a visual indicator that the data is not displaying correctly.

How to Fix #### Error in Excel:

• Fix Column Width:
  • ❌ Problem: The column is too narrow to display the full content of the cell, causing Excel to show #### error instead of the actual value.
  • ✅ Fix: To automatically adjust the column width, double-click the right edge of the column header. If needed, manually increase the column size by clicking and dragging the border. For long text entries, enable Wrap Text from the Home tab to ensure all content is visible.
• Fix Negative Date or Time Values:
  • ❌ Problem: Excel does not support negative date or time values, so when a formula returns a negative result, it displays #### error instead.
  • ✅ Fix: To fix this Excel error, check the formula that is producing the negative date or time and adjust the calculations. Enable the 1904 Date System, which allows negative dates to be displayed. To do this, go to File > Options > Advanced, then check “Use 1904 date system” under “When calculating this workbook”. If negative values must be shown, convert them into text format using the formula =TEXT(A1, “dd-mm-yyyy”).
• Fix the wrong Date Format for Large Numbers:
  • ❌ Problem: When a large number is mistakenly formatted as a date, Excel may not be able to display it within the supported date range, resulting in the #### error.
    ✅ Fix: Select the cell and press Ctrl + 1 to open the Format Cells window. Then double check the entered data to ensure that numbers are not mistakenly being treated as dates.
• Fix the Formatting:
  • ❌ Problem: The cell is using a custom number format that forces Excel to display #### error instead of showing the actual value.
  • ✅ Fix: Open the Format Cells menu by pressing Ctrl + 1 and change the format to General or Number to display the value properly.

10. Circular Reference Error

• The circular reference error in Excel occurs when a formula refers to its own cell, either directly or indirectly, creating an endless loop.
• This Excel error can lead to incorrect calculations or slow performance.

How to Fix Circular Reference Error in Excel:

• Modify the Formula to Remove Self-Reference:
  • ❌ Problem: The formula is placed in the same cell it is trying to calculate, causing an infinite loop.
  • ✅ Fix: Go to Formulas > Error Checking > Circular References to find the affected cell. Change the formula so it doesn’t reference itself.
• Break the Loop Between Linked Formulas:
  • ❌ Problem: Two or more formulas depend on each other, causing an indirect circular reference. Example: If A1 = B1 + 10 and B1 = A1 – 5, they create a loop.
  • ✅ Fix: Use Trace Dependents from the Formulas tab to see which cells are linked. Adjust the formulas to remove the circular dependency.
• Correct the Logic in Conditional Formulas:
  • ❌ Problem: Some logical formulas unintentionally create a circular reference when the logic isn’t properly set. Example: =IF(A1=0, A2, A1+1) can create a loop if A1 depends on another looping formula.
  • ✅ Fix: Review the formula logic carefully. Make sure the condition prevents an endless loop.
• Enable Iterative Calculations (If Necessary):
  • ❌ Problem: Some formulas, like financial or forecasting calculations, require multiple iterations, and Excel blocks them by default.
  • ✅ Fix: To enable iterative calculations, go to File > Options > Formulas and check Enable iterative calculation and set a limit for the loops.
• Use a Helper Cell to Avoid Circular References:
  • ❌ Problem: Formulas depend on each other in a way that creates a loop.
  • ✅ Fix: Use a helper cell to store an intermediate result and break the loop.

Tips to Handle all Excel Errors

The errors in Excel can be annoying, but you can fix them easily with the right steps. Here are some simple tips to handle errors in your spreadsheets.

1. Handle Errors with Functions

• Use the IFERROR function to catch any error and return an alternative value.
• If you want to handle only #N/A errors, then you can use the IFNA function.

2. Verify Data Types

• Ensure numbers are not stored as text.
• Use the ISTEXT function to verify if it is a text.
• Use the ISNUMBER function to verify if it is a number.
• Use the ISBLANK function to verify if it is an empty cell.

3. Turn On Excel’s Error Checking

• You can turn on the error checking feature in Excel to automatically detect and highlight errors.
• To do that navigate to File > Options > Formulas > Error Checking.

Conclusion

To keep your Excel data accurate and workflows smooth, it’s important to understand and fix Excel errors. The errors like #DIV/0!, #VALUE!, #REF!, and #NAME? etc. can often be avoided by using the right formulas, applying data validation, and referencing cells correctly. Using error handling functions like IFERROR, IFNA, and ISERROR etc. can help to handle errors in Excel.

Learn More