When you use formulas in Excel, you may always encounter errors. In such a case, you may see the text #VALUE! in the cell instead of the value that it should be. Excel provides a feature to check the errors in your worksheet, it can also help you to debug and fix the error. This article will tell you how to use it to check for errors in Excel.
1. How To Turn On Excel Error Checking Option.
- Open Excel, and click the File tab on the top Excel ribbon area.
- Click the Options item on the left side to open the Excel Options dialog window.
- Click the Formulas item on the left side panel.
- Go to the Error Checking section on the dialog window’s right side.
- Make sure to check the Enable background error checking checkbox.
- You can customize the error color from the Indicate errors using this color drop-down list.
- Go to the Error checking rules section below the Error Checking section.
- Check the checkbox before the error-checking rules to enable that rule.
- Then the error-checking rule will be triggered when an error occurs on the worksheet.
2. How To Check, Debug, And Fix Errors In Excel.
2.1 Cells containing formulas that result in an error Rule Example.
- In this example, I will show you how to use error checking in Excel to check value errors.
- Input the text value 2022.1.1 in cell A1, then input the formula =DATAVALUE(A1) in cell B1.
- Click cell A1 and move the mouse pointer to the bottom right corner of the cell.
- When the pointer changes to a black cross icon, click it and drag the black cross pointer down to create more rows with the same text.
- Do the same in cell B1, and it will create a formula in each cell of the rows below cell B1.
- Below is the example data table with the data and formula.
A B 1 2022.1.1 #VALUE! 2 2022.1.2 #VALUE! 3 2022.1.3 #VALUE! 4 2022.1.4 #VALUE! 5 2022.1.5 #VALUE! 6 2022.1.6 #VALUE!
- When you see the #VALUE! text in the cell and there is a small green triangle on the top left corner of the cell, which means the formula has an error.
- To debug it, we can click the Formulas tab on the Excel top ribbon area, and then click the Error Checking item in the Formula Auditing group.
- Then it will pop up the Error Checking dialog window, and select the first error formula cell.
- You can see the cell info that has the error, in this example, it is Error in cell B1.
- It will show the formula that triggers the error below the above cell info. In this example, the error formula is =DATEVALUE(A1)
- You can find the error detailed information below the error formula, in this example, the error detailed message is Error in Value, A value used in the formula is of the wrong data type.
- Click the Show Calculation Steps button will open the Evaluate Formula dialog window, you can debug the formula error in this dialog window.
- Click the Evaluate button it will evaluate the formula that triggers the error.
- If your formula is complex, you can click the Step In, Step Out to debug the formula in more detail.
- Click the Close button will close the Evaluate Formula dialog window.
- Now it goes back to the Error Checking dialog window, and we can click the Next button to go to the next cell that triggers the error.
- In this example, the error-checking dialog tells us the error is because of the wrong data type.
- So we can change the date text from 2022.1.1 to 2022-1-1, then the error is fixed.
- This is because 2022.1.1 is not a regular Excel date format.
- If the cell formula has an error, when you click to select the cell, it will display a yellow diamond before the cell.
- Hover your mouse pointer on the yellow diamond, it will display a string to tell you the error reason.
- When you click the down arrow after the yellow diamond, it will list the same items that exist in the Error Checking dialog window.
- If you click the Error Checking Options… menu item in the menu list, it will open the Excel Options dialog and show the Formulas —> Error Checking section to you.
- This error is triggered by the rule Cells containing formulas that result in an error, you can go to the Excel Options dialog —> Formulas —> Error checking rules to enable or disable it.
- When you check it to enable it, it will trigger the error on the worksheet, when you uncheck it to disable it, it will not show the error signals on the worksheet.