Microsoft Excel is the most popular application of Microsoft Office suite. This is used in both personal as well as professional life. We can easily manage; organize data, carry out the complex calculations, and lots more easily with it.
Undoubtedly this is very important for performing different functions without any hassle. This is the reason Microsoft has provided various functions and features to make the task easy for the users.
However, despite its popularity and advancement, this is not free from errors.
Sometimes while using Excel formulas and functions, result in error values and returns inadvertent results.
So today in this article, I am describing How to handle/ignore different Excel errors.
How to Handle/Ignore Excel Errors
While using the Excel spreadsheet if you entered an incorrect Excel formula or by mistakenly enter any wrong value, a small green triangle appears in the upper left corner of the cell, near the wrong formula.
But if you get a cryptic three or four letter before the sign (#) in the cell, then this means you are facing any unusual error in Excel.
So, if you encounter any sign among the two, then it is clear you are getting Excel error.
Well, if you want to fix the Excel formulas error, then Relax! As there are certain ways that make your Excel workbook error-free.
Correct Common Errors In Excel Formulas
You need to execute certain rules to check for Excel formulas error. Checking these rules will help you recognize the issue. But there is no guarantee that your worksheet is error-free.
You can individually turn On or Off any of the rules. The errors can be fixed in two ways:
- One particular error at a time (spell checker)
- Or directly as they appear on a worksheet when you type or enter data.
Well, in this way you can fix the error by utilizing the options that Excel displays or else you can even ignore the error by un-checking Ignore Error.
In a specific cell, if you ignore an error, the error in that cell does not appear in additional error checks.
Note: The previously ignored errors can be reset to appear again.
You May Also Read:
- 8 Common Excel Error Messages that Irritates You The Most
- Fix 3 Excel Errors That Occur Due To Corruption & Recover Lost Data
- 9 Best Fixes & Solutions For Excel Formulas Not Working Error
1. Error Checking Options
The Error Checking Options button appears when the formula in Excel worksheet cell causes an error. Additionally, as I said above the cell itself displays a small green triangle in the upper left corner.
And as you click the button the error type is exhibited, with the list of error checking options.
Well, if you want to ignore the errors completely then simply uncheck the Error Checking option.
Steps to Turn Error Checking Option On/Off
- Click File > Options > Formulas.
- Then under Error Checking, check Enable background error checking. (Doing this mark the error with a triangle in the top-left corner of the cell.)
- Also, you can change the colour of the triangle marked as an error in the > Indicate errors using this colour box > choose the desired colour type.
- Now under Excel checking rules > you can check or uncheck the rules boxes that you want.
- Cells containing formulas that result in an error
- Inconsistent calculated column formula in tables:
- Cells containing years represented as 2 digits
- Numbers formatted as text or preceded by an apostrophe
- Formulas inconsistent with other formulas in the region
- Formulas which omit cells in a region
- Unlocked cells containing formulas
- Formulas referring to empty cells
- Data entered in a table is invalid
Some Other Excel Errors
1. Fix an Inconsistent Formula
While entering the formula in the Excel workbook if get an inconsistent formula then this means the formula in the cell does not match the formulas pattern.
So here follow the steps to fix it:
- Click Formulas > Show Formulas.
- You will see the formulas in all cells > instead of the calculated results.
- Now compare inconsistent formula with the one before and after > correct any unintentional inconsistencies.
- As finished > click Formulas > Show Formulas. This will now show the calculated results of all cells
- If it won’t work for you > select a nearby cell nearby that won’t have any issue.
- And click Formulas > Trace Precedents > choose the cell that won’t have any issue.
- Click Formulas > Trace Precedents > compare blue arrows > correct issues with the inconsistent formula.
- Click Formulas > Remove Arrows.
Doing this will help you fix the inconsistent formula error.
2. VBA Runtime Error 1004
This error is faced by the users while trying to copy/paste the filtered data programmatically in Microsoft Office Excel and in this case you will receive one of the below-given error messages:
- Runtime error 1004: Paste method of worksheet class failed.
- Runtime error 1004: Copy method of Range Class Failed.
One of these error messages appears when the data is pasted into the workbook.
To follow the complete fixes visit: How to Repair Runtime Error 1004 in Excel
3. Excel VLOOKUP Function Not Working
While using the Excel VLOOKUP function getting the #N/A, the error is common. This is faced by the users while trying to match a lookup value within an array. The error commonly shows that the function has failed to locate the lookup value in the lookup array.
To know more about it read: 5 Reasons Behind “Excel VLOOKUP Not Working” Error Occurrence
Automatic Solution: MS Excel Repair Tool
Make use of the professional recommended MS Excel Repair Tool to repair corrupt, damaged as well as errors in Excel file. This tool allows to easily restore all corrupt excel file including the charts, worksheet properties cell comments, and other important data. With the help of this, you can fix all sort of issues, corruption, errors in Excel workbooks. This is a unique tool to repair multiple excel files at one repair cycle and recovers the entire data in a preferred location. It is easy to use and compatible with both Windows as well as Mac operating system. This supports the entire Excel versions and the demo version is free.
Steps to Utilize MS Excel Repair Tool:
Well, this is all about how to ignore or handle Excel errors.
There are ways that help you recognize the Excel formulas and functions errors in the workbook.
Read the complete article to know more about the errors and ways to handle and ignore the Excel errors
I tried my best to put together the complete information about the common Excel problems and ways to handle them easily, so implement them and make your Excel workbook error-free.
Additionally, Excel is an essential application and used in daily life, so it is recommended to handle the Excel file properly and follow best preventive steps to protect your Excel files from getting corrupted.
Despite it, always create a valid backup of your crucial Excel data and as well scan your system with good antivirus program for virus and malware infection.
If, in case you have any additional questions concerning the ones presented, do tell us in the comments section below or you can also visit our Repair MS Excel Ask Question