10 Common Errors in Excel and How to Fix Them

Excel is an ideal tool for businesses, individuals and organizations. Because in this free Windows program, you can organize, store and analyze data. However, like any other software, Excel can give you some errors. When errors occur in Excel, fixing them can be frustrating if you do not know the cause or the solution. Here we will see most common errors in Excel and mention general solutions on how to fix them.

Types of Errors in Excel: how to find errors in excel

So when we look at generally, we can categorize Excel errors into syntax errors, calculation errors and data errors.

Syntax Errors in Excel

Syntax errors occur when the formula or function entered with a incorrect syntax. This means the formula or function contains an error in its syntax. Such as a missing bracket, quotation mark or comma. Excel will display a #NAME? error if it cannot recognize the formula or function.

Calculation Errors in Excel 

So, these ones happen if the formula entered into a cell with an error in the calculation logic. For example, if a cell has a formula that divides a number by zero, Excel will display a #DIV/0! error.

Data Errors in Excel 

These ones pop up if your data itself has an error. So, if a cell has a date format but the data is not a date, Excel will display a #VALUE! error.

Common Errors in Excel: What Are Errors In Excel?

Most Common Excel Errors

#NAME? Error

This error happens if Excel cannot recognize the formula or function in the cell. But you can fix it by checking the syntax of the formula and correcting any errors. If the formula or function is correct, you will check to if add-in containing the function is installed as well as enabled.

#DIV/0! Error

The #DIV/0! error pops up when a formula attempts to divide a number by zero or an empty cell. If you wanna fix it, you can do it by checking the formula. Then you need to ensure if divisor is not zero or an empty cell.

#VALUE! Error

This  #VALUE! error happens when the formula is not valid for the cell’s data type. In this case, if a cell is should have a format date but your number is not date then, Excel will display a #VALUE! error. You can fix it by correcting the data to match the cell’s data type.

#REF! Error

If your formula or function refers to a cell that does not exist or a deleted one, you  will see this ref error. But it is possible to solve it by correcting the formula to refer to a valid cell.

Circular Reference Error: How to fix Excel errors

The circular reference means when a formula refers to itself. This is actually meaning it is  creating an infinite loop. You can solve it if remove the circular reference or by modifying the formula to avoid the circular reference.

#NUM! Error

So #NUM! error is about when a formula or function contains an invalid numeric value. It is possible to solve it by checking the formula and ensuring all numeric values are valid.

#N/A

Basically #N/A error pops up when formula returns a value that is not available or not applicable. If you modify it to return a valid value or by changing the input data, you can solve this problem.

#NULL!

The #NULL! error happens when a formula or function refers to two ranges that don’t intersect. In case you wanna eliminate it, you need to correct the range reference in the formula or function.

#GETTING_DATA

Excel returns you this when it cannot retrieve data from an external data source. If you wish to solve this error, you should check the connection to the data source and make it available.

#SPILL!

If excel cannot calculate a spilled array formula, you will see this error. It is because of a conflict with existing data on the worksheet.  In order to fix this problem, you can clear the conflicting data. Or as an alternative, you can modify the formula by hand.

FAQs on Excel formula errors

1. How can I prevent errors in Excel? For this general solution, you should first check the accuracy of data and double-check the syntax. Also, it is important to control logic of your formulas before just entering them into cells.
2. How to fix a #DIV/0! error in Excel? In case you need to eliminate a #DIV/0! error, you should check the formula and look if divisor is not an empty cell or zero.
3. Why am I getting a #N/A error in Excel? You might see this error if your formula returns a not applicable or available value. But you can fix this error if you change your input.
4. What to do to retrieve data from an external data source in Excel? Here, you should check the connection to the data source and ensure it is available.

Conclusion

In case you wanna solve excel errors without panicking around, you can regularly check and verify data and using Excel‘s tools. Learning excel formulas is a milestone for every level of user. But while you are using and learning formulas themselves, it is good to know about error cases.

And if you think you cannot manage it by yourself, seeking expert assistance can be smart solution. Or you can check online resources first to solve issues. Using these methods, you can minimize the negative impact of Excel errors.