Formula auditing tools

Formula auditing tools are so powerful such that it can be used to trace the source of your errors in excel.

They can breakdown your calculations and pinpoint where something might be wrong in your formula.

Let’s take a look at the three most powerful formula auditing tools.

Formula auditing tool

Trace Precedents: what trace precedents does it that, it identifies cells which affect the value of the one selected.

Trace precedents

What this simple means is that, if you were to change the value of the mortgage costs, property tax, utilities or insurance cells, the value in the Monthly Expenses cells which was selected would change.

Trace Dependents: in the other direction, trace dependents identify cells which are affected by the value of the one selected.

Trace dependents

In this scenario, you are looking at an upstream cell (Cash to Close) and understanding which downstream cell (Down payment % and Loan Amount) are impacted.

Show Formulas: this simply display all formulas in a worksheet as text string.

It’s pretty easy to get a workbook from your boss with hard coded formula, just use the show formula option to demystify it.

Show formulas-formula auditing tools

Learn to Freeze rows and columns in Excel

HIGHLIGHT CELLS CONTAINING FORMULAS IN A WORKSHEET

Press,

  • Control + G: Go to special

Select, Special… Formulas, then click OK.

The trick here is to show all cells, containing formulas in a worksheet.

Here is a visual explanation giving more insight about the formula auditing tools in Excel.

Excel file: Formula Auditing tools

Thanks, sharing is caring.