Excel errors
When working with an Excel sheet, you might face several errors that occur for different reasons. The Excel users must be aware with those errors and know the reasons why they occurred. So, whenever they get any error, they can resolve it.
In this chapter, we will discuss the Excel errors and the methods to resolve them. This will help you to handle the errors in an Excel sheet.
What is an error in Excel?
An error is an unexpected exception thrown by the Excel application that may occur when two or more cell references are separated incorrectly. Sometimes, these errors are tricky to solve but not impossible.
You have seen too many errors in Excel, such as – #N/A, #DIV/0!, #VALUE!, #NULL!, ######, and many more. We will discuss the most annoying errors that generate in Excel. These errors can be avoided by following some common best practices.
Why does an error occur?
When working with Excel worksheets, we get some annoying errors. It can be generated due to small mistakes. So, you should be aware with Excel errors and why they occur. So, we will discuss the errors that usually occur in Excel.
Different types of errors occur due to different reasons, like a number divided by 0, due to circular cell reference, adding a numeric value with text using arithmetic plus operator (+), using an incorrect formula, etc.
Similar to this, there are various other errors in Excel.
Most common errors in Excel
There is a list of some most common errors that the users usually face most of the time while working with Excel sheets.
- ###### Error
- #DIV/0! Error
- #N/A Error
- #Name? Error
- #VALUE! Error
- #NULL! Error
- #NUM! Error
- #REF! Error
Best practices to avoid errors in Excel
Following tips will help you out to avoid these errors in Excel:
- Use equal sign (=) every time you put the formula in an Excel cell.
- For the multiplication operation, use the * (asterisk) sign rather than X.
- All opening and closing parenthesis match. It means every opening brace should have closing braces.
- Use quotation marks (“”) around the text or string data in the formula.
- Cell size should be good enough to hold the data.
###### Error
This one is the most common error that an Excel user usually sees in an Excel sheet. It is easy to tackle this error. This error may occur due to the size of the cell. It means the cell width is not enough to hold the value. Hence, the value of the cell is visible as ###### (hash marks). Basically, it is a general error, not a formula-based error.
Error
For example, we have a number 100000000 in a cell here and the number is in comma-formatted. But you see that the cell width is not enough to show this value. So, it converts to ###### (hash marks).
Solution
Increase the cell width having ###### has mark error and see the value is showing now properly.
To remove negative time error
Besides this, ###### Error occurs because of one more reason, i.e., when a negative date (-7:12) is stored in a cell. It happens when you subtract one time from another and returns a negative time value. To display the negative time in a cell, you have to use the 1904 date system.
For this, Go to the File > More > Options > Advanced.
Mark the Use 1904 date system checkbox here for the current workbook.
You see – ###### hash mark error has been removed and the negative time value is displaying now.
#Div/0! Error
As the name implies, this error is easy to understand. It refers to the divide by 0 exceptions that is thrown when a number is divided by 0. Let’s understand with an example.
A cell A2 has a number 34 and another cell B2 has 0 in it. We write a formula in C2 cell in which we divide the A2 cell data by B2 cell. i.e., (=A2/B2).
Error
See that we get the #Div/0! Error as result. This error always occurs when a number is divided by 0.
Solution
To avoid this error, avoid dividing a number by 0 that returns this error.
#N/A! Error
The #N/A! error occurs when the Excel formula does not find the value in the Excel sheet. Basically, this error refers to no value available. It means when you apply a function to a cell, but there is no value in the cell, #N/A error appears. This can occur when something is missing or misspelled.
#N/A! error refers to the not applicable.
Solution
To fix this error, check that cell have the value that you are using in your Excel formula. There should be no cell without value used in the formula.
#Name? Error
This error occurs when the function name is used wrong and Excel is not able to recognize the formula. It returns #Name? error.
Error
For example, we want to sum the cell B2 to B5 data. So, we used the SUM() function for it but mistakenly typed the incorrect function name. Hence, it returned #Name? Error.
Solution
We have corrected the function and now you see that the result is calculated successfully without any error.
Whenever you get the error named #Name?, check the function name whether it is used correctly or not and avoid this error.
#Value! Error
The Value! Error is a very interesting error that a user may face when you a large amount of error.
The Excel users usually face when they are trying to perform any operation using arithmetic operation (+, -, *, /) on data, but one of the cells contains text string in it. Thus, it generates #Value! Error.
Error
For example, column B contains some numeric value along with a text string in it. We have tried to sum the numeric value but mistakenly included text data cells in the formula.
=B2+B3+B4+B5
Here, you will see the #Value! Error has been returned. We have just shown an example on a small set of data.
Solution
Whenever you face the #Value! Error, check that all values on which you are performing the operation are numeric.
We have removed the reference of B5 cell holding text data and see that we get the correct result without error.
#Null! Error
The #Null! Error rarely occurs in Excel and a typo error. Basically, it occurs when you use space instead of a comma or semicolon in a formula. For example, a sum formula SUM(A2:A5) but you made a typo mistake and the formula becomes like SUM(A2 A5). Thus, it returns #NULL! Error.
Let us see an example for it.
Error
We have applied a sum formula on the data stored in column B. For this, we have used the SUM() function and provided the reference of the cells.
But we made a typo mistake and it returned #NULL! Error.
Solution
Recheck the formula and correct the typo mistake. The #Null! Error has been removed and now the result is returned correctly without any error.
#Num! Error
The #Num! Error occurs when the number is too large or too small to perform an operation. One more reason is for this error, i.e., when the calculation is impossible to perform.
For example, if you try to find the square root of a negative number in an Excel sheet, you can get this error. It is an impossible type of operation. That is why this error has occurred. So, avoid these types of impossible calculations in Excel worksheets.
One more reason for this such as – when you swap the parameters in a function. Like if you reverse the start date and end date to each other inside the DATEDIF() function. It will generate #NUM! error.
Error
You see the #NUM! error in the below screenshot where we tried to calculate the square root of -5 that is a negative number. So, it returned error.
See the below screenshot, it has returned #NUM! error.
Solution
To resolve this error, avoid this type of calculation in the Excel sheet.
#REF! Error
You might face #REF! error while working with Excel sheet most of the time, as it is a very common error of Excel. This error generates when a reference of the cell becomes invalid. It can be due to invalid references. It means you might be trying to reference a cell, sheet, row, or column that has been removed. So, the used formula generates the #REF! error.
These errors are usually seen when your Excel sheet has some calculations used. But you mistakenly delete the cell, row, or column used in that calculation, then this error occurs.
These are the errors that can be handled as they are because of typo mistakes. Some errors in Excel that you will surely hate.
Another Excel errors
Besides the errors discussed above, some more errors in Excel. These errors are different than the above ones and not easy to solve. So, you may hate these errors.
Error 1: Excel cannot open this file
You have definitely faced this error in Excel at least once while working with an Excel sheet. If not – no issue, you should still be aware with this error. This error appears when you try to open a file that is not compatible with the current version of Excel you are using. It can either be corrupted or damaged. It can also be due to an invalid extension used with an Excel file.
Solution
To handle this error, follow the steps:
Step 1: Start opening the Excel file and be on the Excel backstage screen (using File option in menu bar).
Step 2: On the left sidebar, click the Export option and choose the Change the File Type.
Step 3: From here, you can change the file format and choose a valid file format to resolve this error.
Error 2: The file is corrupted and cannot be opened
This error is mostly seen when you upgrade the Excel software. You have to make the changes in Excel to avoid this error. After that, you can perfectly work with Excel.
Solution
To handle this error, follow the steps:
Step 1: Be on the Excel backscreen using File option in the Excel menu bar.
Step 2: Here, click More and then choose Options.
Step 3: On the Options panel, go to the Trust Center in the left sidebar. Click the Trust Center Settings.
Step 4: A new window for protected view on the same window will open where unmark all the checkbox options and click OK.
All Set. Now you can work with Excel without any error.
Error 3: The document caused a serious error the last time it was opened
This error occurs on a file you worked on. This error can cause a serious problem when you opened the Excel file last time. This error can occur due to the file being included in the list of disabled files. Don’t worry; this is not so big a problem.
Solution
Fix the error by following the given steps:
Step 1: On the Excel backscreen, click More and then Options.
Step 2: Now, move to the Add-Ins on the Options dialogue panel and choose the Com Add-Ins from the Manage dropdown list.
Step 3: After choosing the Com Add-Ins for Manage list and click Go.
Step 4: At the end, when the error is fixed, restart the Excel application. No error you will see now.
Error 4: There was a problem sending the command to the program
Sometimes, too many processes are running in Excel simultaneously, which does not let the users to close the Excel. Then, this error occurs and this error message shows to the Excel users. This can be because of the heavy Excel file.
Following step will show you the way to resolve this error:
Step 1: On the Excel backscreen (using File tab), click More and then Options.
Step 2: On this panel, go to the Advanced tab in left sidebar and then scroll down the General section. Here, unmark the Ignore other applications that use Dynamic Data Exchange (DDE) checkbox.
Step 3: Click OK to close it.
