The real power of Microsoft Excel lies in its formulae. However, as a Microsoft Excel user would know well, making mistakes with formulae are common since they are complicated. You can fix this by tracing these errors and checking suggestions for improvements. This is called Background error checking. We will show you the procedure to enable or disable the same.
What is Background error checking in Excel?
Microsoft Excel is not only about arranging data in sheets. The real purpose of the software is to make calculations. Microsoft Excel is used to create information out of random data. Formulae are used for this purpose. Since formulae in Microsoft Excel can get very complicated, it is important to check them for errors. When Microsoft Excel does this process automatically in the background, then it is called Background error checking.
How to enable or disable Error checking rules in Microsoft Excel
Background error checking is enabled on Microsoft Excel by default. However, if a third-party extension disabled it or another user disabled the option, it can be re-enabled again as follows:
- Launch Excel
- Click on File.
- From the menu, select Options.
- Go to the Formulas tab.
- In the Error checking section, check the box associated with Enable background error checking.
- If you wish to disable Background error checking, then simply uncheck the box.
- Click on OK to save the settings.
If the enabling or disabling or Background error checking is caused due to a third-party extension, you will have to disable the same.
How to change the color for Background error checking in Excel?
The color for errors marked through the Background error checking process are all green. This is because green is the default color for these marked errors. You can change this color as well. The procedure is as follows:
- Go to the Formulas window as explained earlier.
- In the Error checking group, you will find an option for Indicate error using this color.
- Change the color from the drop-down menu.
How to reset Ignored errors in Microsoft Excel?
The Background error-checking process marks errors as per a set procedure. Many errors marked by the process could be genuine formulae or instructions. In this case, you can ignore them or whitelist them. Should you be willing to reset this list of Ignored errors, then the procedure is as follows:
- Go to the Formulas window as explained earlier.
- Scroll down to the Error checking section.
- You will notice a button for Reset Ignored Errors.
- Click on it to reset the Ignored errors.
How to modify Error checking rules in Microsoft Excel?
The Background error checking is dependent on a set of rules which can be modified. Basically, these rules indicate which figure or formula will be marked as an error. The procedure to change these error-checking rules is as follows:
- Go to the Formulas window as explained earlier.
- You will notice options with checkboxes.
- Check a box to enable the associated option and uncheck the box to disable the associated option.
- Click on OK to save the settings.
How to change error checking rulesin Microsoft Excel?
The meanings of the error checking rules are as follows:
1] Cells containing formulas that result in an error
Whenever the error is with the syntax of the formula itself, then this rule comes into play. It is marked with #VALUE! or #DIV/0! .
2] Inconsistent calculated column formula in tables
This rule marks cells in which the syntax of the formula may be correct, but the formula might be inconsistent with the column. Eg. If you mark a column which doesn’t fit in the formula, then you will get an error.
3] Cells containing years represented as 2 digits
Years should be represented as 4 digits. Some people prefer them as 2 digits. This rule will mark the cell if the year is marked as 2 digits. If you did this intentionally, then you can consider unchecking the checkbox associated with this rule.
4] Numbers formatted as text or preceded by apostrophe
Writing ten and mentioning 10 is read differently by Microsoft Excel. Similarly, writing 10 and “10” is read differently by Excel. Anything other than numerical representation of numbers is not read by formulas.
5} Formulas inconsistent with other formulas in the region
When you use lots of formulas in Microsoft Excel, eventually, formulas become dependent on each other. In many cases, the value obtained by one formula is used by another one. In this case, the mentioned rule will mark an error if a formula you created in not in conjunction with other formulas of the region in the Microsoft Excel sheet.
6] Cells containing data types that couldn’t refresh
A lot of data in Microsoft Excel is picked from other sources. As an example, you can add stock market data to Microsoft Excel. Since this data keeps changing, Microsoft Excel keeps refreshing the data and uses that data for other calculations. However, imagine a case where the internet is not working, or the server of the stock market is down. In this case, the data will not refresh. This rule will mark an error to indicate the same.
7] Formulas which omit cells in a region
Formulas may or may not influence all cells in a region. However, if they do not impact each cell, the mentioned rule will come to play. Should this be done intentionally by you, the rule can be left unchecked.
8] Formulas referring to empty cells
An empty cell carries a default value of 0 but that might not always be the intention for keeping the cell empty. If a cell included in a formula is empty, it will be marked if this rule is active.
9] Data entered in a table is invalid
In case formulas are used either for assessing tables or using the data mentioned in them, the data could be inconsistent with the formula/s used. In such a situation, this rule will mark the entry in color.
10] Misleading number formats
When you read dates, time, etc, they use a specific format. This is called number format. The Misleading number format rule can help with marking such number formats.
Why does Background error checking in Excel keep disabling on its own?
A lot of functions in Microsoft Excel are managed by its extensions. These extensions can also manage the Background error checking and disable the option. Merely re-enabling it will not solve the problem for long-term. You can disable the problematic extension as follows.
- Open Microsoft Excel.
- Go to File > Options.
- In the left pane, go to the Add-ins tab.
- Corresponding to the drop-down menu associated with Manage, select COM Add-ins.
- Check an Add-in and click on Remove to delete it.
Try this method till your problem gets fixed. You will be able to figure the problematic extension using the hit and trial method.
How do I enable error checking in Excel?
To enable error checking in Excel, you need to follow the steps as mentioned above. Open the Excel Options panel first and switch to the Formulas tab. Then, find the Enable background error checking option and enable it to turn the feature on. You can also follow the article for a detailed guide.
How do you enable the Formulas referring to empty cells in the error checking rules?
As of now, there is no option to enable or disable this functionality in Microsoft Excel. However, if you want to turn on or off error checking in the same app, you can go through the above-mentioned steps. For your information, you can set various conditions to enable or disable the error checking.
