Our today’s topic will give you the best idea on How To Ignore All Errors In Excel. So that any Excel user can easily handle commonly rendered errors of Excel.
Microsoft Excel is the most popular application of the Microsoft Office suite. This is used in both personal as well as professional life. We can easily manage; organize data, carry out 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 all errors in Excel.
How To Trace Error In Excel?
While using the Excel spreadsheet if you entered an incorrect Excel formula or by mistake 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 to ignore all errors in Excel.
How To Ignore All Errors In Excel?
You need to execute certain rules to check for Excel errors. 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.
So, check out the following methods to ignore all errors in Excel.
Method 1# Error Checking Options
The Error Checking Options button appears when the formula in the 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 color of the triangle marked as an error in the > Indicate errors using this color box > choose the desired color 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 that omit cells in a region
- Unlocked cells containing formulas
- Formulas referring to empty cells
- Data entered in a table is invalid
Method 2# 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 formula’s 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 the 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.
Method 3# Hide error values and error indicators
If your Excel formula is having such errors that you don’t need to correct then it’s the best idea to hide it. So, that it won’t appear in your result. Well, this task is possible in Excel by hiding the error indicator and values in cells.
Hide Error Indicators In Cells
If your cell is having a formula that breaks the rule which Excel usually uses for checking up the issues. Then you will see a triangle at the top left corner of the cell. Well, you have the option to hide this indicator from being displayed.
Here is the figure which shows how the Cell appears with the error indicator:
- Go to the Excel menu and tap the Preferences option.
- Within the Formulas and Lists, tap to the Error Checking. After then remove the checkmark from the Enable background error checking
Some Other Commonly Encountered Excel Errors Along With The Fixes:
1. 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
2. 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 files 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 systems. This supports the entire Excel version and the demo version is free.
Steps to Utilize MS Excel Repair Tool:
Well, this is all about how to ignore all errors in Excel.
There are ways that help you recognize the Excel formulas and function errors in the workbook.
Read the complete article to know more about the errors and ways to handle and ignore errors in Excel.
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 the 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 a 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 social accounts.