Excel is the most used spreadsheet software in the world. This means a lot of people like it and would prefer it over the others. However, even while using Excel you can face some errors. Some are easy to fix while others require complex solutions. Thus it’s most likely you have come across errors such as #VALUE, ####, and #REF among others
This tutorial will look at the 15 most common Excel errors and how to fix them with practical examples.
Types of Errors
| Error | Description | Solution | Example |
|---|---|---|---|
| #CALC! | Invalid calculation in formula | Invalid calculation in the formula | =SUM(A1:B10 (missing closing parenthesis) |
| #DIV/0 Error | Division by zero | Use IF statement to handle zero divisor | =A1/B1 (where B1 contains 0) |
| #N/A Error | Value not found in lookup | Use error-handling functions like IFERROR or ISNA() | =VLOOKUP("Apple", A1:B10, 2, FALSE) (if “Apple” not found) |
| #NAME? Error | Unrecognized function or reference | Review the formula for correct syntax and operators | =SUMM(A1:A10) (misspelled SUM function) |
| #NULL! Error | Space instead of a comma in function arguments | Correct syntax in the formula | =SUM(A1 A2) (missing comma) |
| #REF! Error | Invalid cell reference | Check the spelling of functions and references | =A1+B2 (after deleting column B) |
| #VALUE! Error | Inappropriate value in a function or formula | Verify data types or use error-handling functions | =SUM("A", B2, C3) (summing a text string) |
| ####### Error | Date or numeric value too wide for the column | Widen column or adjust cell format | Date wider than the column width |
| #SPILL Error | Dynamic array formula spills into non-empty cells | Clear adjacent cells or adjust formula spill range | =SEQUENCE(5) in a cell with non-empty adjacent cells |
| Circular Reference | Formula refers directly or indirectly to itself | Break the circular reference or enable iterative calculations | Circular reference between A1 and B1 |
| #NUM! Error | Numeric argument invalid or outside acceptable range | Check numeric arguments in the formula | =SQRT(-1) (square root of a negative number) |
| #GETTING_DATA Error | External data refresh in progress | Update formula with the correct references | Loading data from an external source |
| #UNIQUE! Error | Duplicate values in UNIQUE function | Wait for the data retrieval process to complete | =UNIQUE(A1:A10) (with duplicate values) |
| #MISSING! Error | Required input parameters missing | Provide missing parameters or check documentation | Custom function missing input parameters |
| #FILE_LINK Error | Ensure the range contains only unique values | Verify linked file location and update file path | External file linked but not available |
1. #CALC!
#CALC! error occurs when there is an issue with a calculation in a formula, often due to a missing or misplaced operator or function.
This error appears in the latest versions of Excel only which are Excel 365, Excel for the web, Excel for Android, Excel 2021, Excel for iPhone and Excel Mac.
#CALC! Error Solution
Review the formula for missing or misplaced operators or functions. Ensure all parentheses are closed correctly.
Example
=FILTER(C3:C5,D3:D5<100) is missing 0 which tells Excel to return 0 if no match is found
As you can see from the image below Excel returns 0 because I have indicated the formula =FILTER(C3:C5,D3:D5<100) (correctly) instead of #CALC error
2. #DIV/0 error
This error appears when you attempt to divide a number by zero. Division by zero is undefined in mathematics.
How to fix #DIV/0 error
Check for division by zero by examining the divisor. You may want to add an IF statement to handle cases where the divisor is zero.
Example
If you have a formula like =A1/B1 and B1 contains 0, you’ll get a #DIV/0! error because division by zero is undefined.
3. #N/A error
#N/A error indicates that a value is not available to a function or formula. It often occurs when using lookup functions, and the specified value is not found.
Solution
Use error handling functions like IFERROR or IF(ISNA()) to manage cases where a value is not found. Verify the lookup value and the range being searched.
Example
Let’s say you have a table with marks and student names. You can use VLOOKUP to pull the marks of any student. However, If you indicate the wrong column index containing marks names then you will face the N/A error. For example VLOOKUP in this case unable to look for values in the left.
As you can see the N/A Error has been cleared.
4. #NAME? Error
#NAME? error occurs when Excel doesn’t recognize a name used in a formula. It could be a misspelled function or a reference to a name that doesn’t exist.
Solution
Use error handling functions like IFERROR or IF(ISNA()) to manage cases where a value is not found. Verify the lookup value and the range being searched.
Example
As you can see from the image above the #NAME? error shows up because of misspelling the VLOOKUP function as VLOOKSUP. You can solve the error by writing the function correctly.
5. #NULL! Error
This error happens when you specify an intersection of two ranges that don’t intersect, or if you use the space character (” “) instead of a comma to separate function arguments.
How to fix #NULL Error
Replace the space character with a comma or correct the syntax issue in the formula.
Example
As you can see from the image above the formula =SUM(B2 B10) causes the #NULL error. This is because its missing (:) to indicate its a range. If you type it the correct way as =SUM(B2:B10) then the error is fixed.
6. #REF! Error
#REF! error arises when a cell reference is not valid. It could be due to a deleted cell, row, or column that a formula is referring to.
Solution
Examine and correct the cell references in the formula. If a referenced cell, row, or column has been deleted, update the formula accordingly.
Example
The #REF! error above appears because I have deleted the range where Excel used to reference. It solves automatically after restoring the deleted range.
7. #VALUE! Error
This error occurs when a function or formula receives an argument of the right data type but with an inappropriate value. For example, trying to perform a mathematical operation on a text string.
Solution
Verify the data types of the function arguments. If combining different data types, use functions like IF or ISTEXT to manage the mix.
8. ####### error
####### error typically appears in a cell when the column width is too narrow to display the cell content as a date. Widening the column should resolve this error.
Solution
Widen the column to display the entire content. Alternatively, reformat the cell or adjust the date format to fit within the current column width.
Example
The above cells contain dates but display #####. This is due to the smaller width of the cell. You can solve it by enlarging the cell width.
Read more: 5 ways to fix the ##### error in Excel.
9. #SPILL error
This error occurs when using dynamic array formulas and the result spills into adjacent cells that are not empty. It indicates that the formula cannot properly spill its results.
Solution
Ensure that the dynamic array formula spills into empty cells. Clear adjacent cells or adjust the formula to spill into the desired range.
Example
=SEQUENCE(5) in a cell with non-empty cells in the adjacent columns.
10. Circular reference errors
Circular reference errors occur when a formula directly or indirectly refers to its own cell. Excel cannot calculate such circular references, and it needs to be resolved by adjusting the formulas.
Solution
Identify and break the circular reference. Either change the formula or structure to remove the circular reference, or enable iterative calculations in Excel settings.
Example
Cell A1 has =B1 + 5, and cell B1 has =A1 * 2, creating a circular reference.
11. #NUM! Error
The #NUM! error occurs when a numeric argument in a formula is invalid or outside the acceptable range. It indicates a numerical calculation error.
Solution
Check the numeric arguments in the formula. Ensure they are within acceptable ranges for the specific function or operation.
Example
=SQRT(-1) (attempting to calculate the square root of a negative number).
12. #GETTING_DATA Error:
#GETTING_DATA error appears during the process of refreshing external data. It indicates that Excel is still in the process of retrieving data from an external source.
Solution
Wait for the external data retrieval process to complete, or check for issues with the external data source, such as connection problems.
Example
Loading data from an external database, and the refresh operation is not yet complete.
13. #UNIQUE! Error
#UNIQUE! error occurs when using the UNIQUE function with a range that contains more than one instance of the same value. The UNIQUE function requires unique values.
Solution
Ensure that the range provided to the UNIQUE function contains only unique values. Address duplicate values in the specified range.
Example
=UNIQUE(A1:A10) where there are duplicate values in column A.
14. #MISSING! Error:
The #MISSING! error can occur when working with certain add-ins or custom functions that expect specific input data, but some required input parameters are missing.
Solution
Check the documentation or source of the custom function or add-in for the required input parameters. Provide the missing parameters.
Example
A formula refers to data in an external workbook, but the workbook has been moved or deleted
15. #FILE_LINK Error:
#FILE_LINK error occurs when a linked file is not available or the link is broken. It indicates that Excel cannot update the linked data due to issues with the linked file.
Solution
Verify the location and accessibility of the linked file. Update the file path or restore the linked file to resolve the error.
Example
A formula refers to data in an external workbook, but the workbook has been moved or deleted
Final Thoughts
Excel errors can be frustrating especially when you don’t know what they mean and how to solve them. Luckily this guide addresses the most common Excel errors that you can face. Most are due to errors in a formula and can be solved easily. You can also utilize the Excelweez AI assistant to help you solve stubborn errors that you face in Excel
