Excel Errors: Auditing Your Excel 2019 Formulas

Your Excel spreadsheet provides results that are only as good as the data you give it and the formulas you create. Feed an Excel spreadsheet the wrong data, and it will (obviously) calculate the wrong result. More troublesome is when you feed a spreadsheet the right data but your formula is incorrect, which produces a misleading and incorrect result.

Even if Excel appears to be calculating your formulas correctly, recheck your calculations just to make sure. Some common errors that can mess up your formulas include

• Missing data: The formula isn’t using all the data necessary to calculate the proper result.
• Incorrect data: The formula is getting data from the wrong cell (or wrong data from the right cell).
• Incorrect calculation: Your formula is incorrectly calculating a result.

If a formula is calculating data incorrectly, you probably didn’t type the formula correctly. For example, you may want a formula to add two numbers, but you accidentally typed the formula to multiply two numbers. To check whether a formula is calculating data incorrectly, give it data for which you already know what the result should be. For example, if you type the numbers 4 and 7 in a formula that should add two numbers, but it returns 28 instead of 11, you know that it’s not calculating correctly.

If your formula is correct but it’s still not calculating the right result, chances are good that it’s not getting the data it needs from the correct cells. To help you trace whether a formula is receiving all the data it needs, Excel offers auditing features that visually show you which cells supply data to which formulas. By using Excel’s auditing features, you can

• Make sure that your formulas are using data from the correct cells.
• Find out instantly whether a formula can go haywire if you change a cell reference.

Finding where an Excel formula gets its data

If an Excel formula is retrieving data from the wrong cells, it’s never going to calculate the right result. By tracing a formula, you can see all the cells that a formula uses to retrieve data.

Any cell that supplies data to a formula is a precedent.

To trace a formula, follow these steps:

1. Click in a cell that contains the formula you want to check.
2. Click the Formulas tab.
3. In the Formula Auditing group, click the Trace Precedents icon.
   Excel draws arrows that show you all the cells that feed data into the formula you chose in Step 1.
   Excel draws arrows that trace the precedent cells that feed data into a formula.
4. In the Formula Auditing group, click the Remove Arrows icon to make the auditing arrow go away.

Finding which Excel formula(s) a cell can change

Sometimes you may be curious about how a particular cell may affect a formula stored in your worksheet. Although you can just type a new value in that cell and look for any changes, it’s easier (and more accurate) to identify all formulas that are dependent on a particular cell.

Any formula that receives data is a dependent.

To find one or more formulas that a single cell may affect, follow these steps:

1. Click in any cell that contains data (not a formula).
2. Click the Formulas tab.
3. In the Formula Auditing group, click Trace Dependents.
   Excel draws an arrow that points to a cell that contains a formula. This tells you that if you change the data in the cell you chose in Step 1, it will change the calculated result in the cell containing a formula.
   Excel can identify which formulas a particular cell can change.
4. In the Formula Auditing group, click the Remove Arrows icon to make the arrow go away.

Checking for Excel errors

If you create large worksheets with data and formulas filling rows and columns, it can be hard to check to make sure that there aren’t any problems with your spreadsheet, such as a formula dividing a number with a nonexistent value in another cell.

Fortunately, you can get Excel to catch many types of errors by following these steps:

1. Click the Formulas tab.
2. In the Formula Auditing group, click the Error Checking icon.
   Excel displays a dialog box and highlights any errors.
   Excel can identify many common errors in a spreadsheet.
3. Click Previous or Next to see any additional errors.
4. When you're finished, click the Close (X) icon in the dialog box to make it go away.
5. Click in a cell that contains an error.
6. Click the downward-pointing arrow that appears to the right of the Error Checking icon.

   A menu appears.

7. Click Trace Error.
   Excel displays arrows to show you the cells that are causing the problem for the error you chose in Step 5.
   Excel can show you where errors are occurring in your spreadsheet.
8. In the Formula Auditing group, click the Remove Arrows icon to make the arrow go away.