Excel errors can happen even to advanced and experienced users. Errors can arise from misplaced or incomplete data and other reasons. Excel errors can be time-consuming to fix, for you need to check what caused the error.
If you use Excel daily, knowing how to fix common Excel errors is an advantage. If you want to know what are the 7 common errors in Excel, this article is for you. This article will also discuss the functions you can use to check errors.
7 Common Excel Errors
An error occurs for different reasons, and it is important to know the different reasons for the error message so you can fix the problem. Below are the seven common Excel errors that you may encounter when working on an Excel file.
1. #SPILL! Error
The #SPILL! error is common in Microsoft 365. This error occurs when the formula cannot override the data, for a data cell is blocking it. You can read more about the Excel spill error and how to fix it. The error can be fixed by applying the formula in free or unpopulated cells or removing the data blocking the cell.
2. #DIV/0 Error
This error is also called the divide by zero error. This error occurs when the dividend is divided by zero or an empty cell. Excel displays this error to inform you that the formula needs to be corrected. This formula error can be fixed by changing your formula so it won’t be divided by zero (use values other than zero). You can alternatively use the IF function to solve the issue.
3. #N/A Error
The #N/A error is also called the “no valuable available or not available” error. This error message is displayed when the program cannot find the value the formula you are using is referencing.
This happens when the reference data is incomplete, missing, or misspelled. This error message can easily be fixed by checking your data entries that will be used as referenced cells for your formula.
4. #NAME? Error
You will see the name error message when the program cannot read the formula, mainly because of a misspelled function name. This error is simple to fix. You just need to correct your spelling.
5. #NULL! Error
The null error is displayed when there are incorrect spaces in your formula or the cell reference is not correctly specified. The incorrect spaces or the space character is called the “intersect operator.” This error can be fixed by checking your formula for erroneous spaces.
6. #NUM! Error
This error is displayed when the formula’s function argument has an invalid numerical value. Error values may include finding the square root of a negative number or placing a dollar sign before the number inside the data cell. This error can quickly be solved by checking your formula and replacing or removing unnecessary values.
7. #REF! Error
Another common error in Excel is the Ref error or Reference error. This error is displayed when two scenarios happen. First, when the formula references a deleted cell, and second, the referenced cell is transferred to a different location in the spreadsheet.
To solve this issue, you just need to undo the deletion of the cell referenced or check the referenced cell to see if the data encoded is correct.
Other Error Messages and Error Functions in Excel Spreadsheets You Don’t Want to See
Aside from the seven common errors in Excel indicated above, below are other function errors that are also worth discussing. Also, aside from function errors, you may notice an error message appears on your spreadsheet. These are listed below:
1. #VALUE! Error
The value error appears when you use the wrong data type for a formula or function. For instance, your formula should compute numerical values from two cells. However, the other cell contains text (not values), while the other contains numerical values.
You cannot add a text and a number together. Hence the value error appears. You can correct this error by using reference cell ranges of numerical values.
2. Circular Reference Error
This error appears when the formula contains a reference cell where you type the formula itself. For instance, A1-A5 contains numerical values that need to be added together. You encoded an additional formula in cell A5 (A5 is part of the equation, a reference data).
This will lead to wrong calculations. When you type the formula in a different cell, for instance, A6, then the formula will work, and the error disappears.
3. ##### Error
The ##### error is displayed when the column width is not long enough to display the encoded value in the cell. The correct characters will not show. Instead, numbers signs will take their place. You can solve the problem by increasing the column’s width.
4. (Filename)’. xlsx Error
You can see this error message when you open a file format that is either corrupted, damaged, or not compatible with your existing version of Excel. If you use the new version of Microsoft Excel, you can change the file type and save it as a new one.
5. Problem Sending a Command to Program
This error message appears when a process within the program prohibits Excel from closing. You can solve this issue by unchecking the Dynamic Data Exchange (DDE) of the Excel Options tab.
6. The Document Caused a Serious Error the Last Time It Was Opened
A file can have serious errors because it is included in the disabled files list. This error can be resolved by unchecking the add-ins in the list COM add-ins dialog box and restarting the program to completely fix the error.
Functions to Check Error in Microsoft Excel
Two functions can help check errors in your Excel spreadsheet – the ISERROR function and the Aggregate function.
- ISERROR Function
The ISERROR function checks possible errors in the function you applied. It helps determine if certain conditions are met or not.
- Aggregate Function
The aggregate function ignores error values. It ignores error values and hidden rows.
Insert Function Tab
Aside from the two functions mentioned above, online help can also be beneficial. Click the Insert function tab and choose Help on this Function to access online help.
Final Thoughts
Learning about common Excel errors and how to fix them is an excellent skill that Excel users need to have. You can be a more efficient Excel user if you can easily resolve any errors you see in your spreadsheet.
Error messages can be resolved by understanding what caused them and constantly practicing to solve them.
0 Comments