If a formula contains mistakes, the result in the cell will be an error value. The first character of an error in MS Excel is the symbol #, followed by text. The text of an error value may end with an exclamation point or a question mark. However, not all errors can be identified this way.
To make troubleshooting easier, you can switch the worksheet to display formulas in cells instead of their calculated results.
Go to the File tab → Options → in Excel Options select Advanced → in Display options for this worksheet, check Show formulas in cells instead of their calculated results.
You can also use Formulas tab → Formula Auditing group → Show Formulas.
To trace dependencies—that is, to graphically display the relationships between precedent and dependent cells—use the commands in Formulas tab → Formula Auditing group . A dependent cell contains a formula that refers to the active cell. A precedent cell is one that the formula in the active cell refers to.
If a cell shows an error value, you can try to determine the likely cause using Trace Error, which you can select from the Error Checking dropdown in the Formula Auditing group: arrows will point to the cells involved in the error.
Finding errors can take time. A helpful tool is Home tab → Editing group → Find & Select → Go To Special…. In the Go To Special dialog, you can select parts of the worksheet that meet specific criteria.
To move the active cell within a previously selected range while preserving the selection, use Tab (forward) or Shift+Tab (backward).
TIP
To find errors in formulas:
- Select the cell that returns an incorrect result or an error value.
- In the formula bar, select the part of the formula you suspect.
- Press F9 to evaluate the selected part: if FALSE appears, there is an error.
- In the same way, evaluate other parts of the formula until you find the error.
- To revert the formula to its original state (without the literal FALSE or the evaluated part), press Esc or click Cancel in the formula bar.
- Correct the erroneous part of the formula.