Formula Auditing in Microsoft Excel

Microsoft Excel has a very effective formula auditing tool, which is useful to track the relationship between cells and formulas. The formula auditing tool is located in the formulas tab under the Formula Auditing section where h Trace Precedents, Trace Dependents, Remove Arrows, Show Formula, Error Checking, & Evaluate Formulas are included.

Trace Precedents:Trace precedents is used to show an arrow to indicate the cells or ranges which affect the active cells value.

img1

 

Trace Dependents:Trace Dependents is used to showan arrow to indicate the cells or ranges that are affected by the active cell. Remove Arrows:This is used to remove the arrows of “Trace precedents” and “Trace Dependents”.

img231

 

Show Formula:This is used to show formula in cell instead of returningthe value.

img3

 

Error Checking:Error checking is used to check if there is any error in the formula.

img4

 

Evaluate Formula:This is used to check the formula step by step.

img5

 

Let’s take an example to understand how you can move betweenprecedents and dependent cells.

Example 1: We have data in range A2:B11, in which column A contains “Agent List” and column B “Reporting Value”. Cell B8 containsthe total reporting value. Cell B10 containsthe No. of Agents. Cell B11 contains the Average Value of reporting values.

img6

 

To use Trace Precedents

  • Select the Cell B11.
  • Go to the “Formulas” tab in the ribbon.
  • Select “Trace Precedents” in the Formula Auditing” group.
  • Double-click the blue arrow between the cells to move between the precedent cell and the dependent cell.

img7

 

To use Trace Dependents

  • Select the Cell B11
  • Go to the “Formulas” tab in the ribbon
  • Select “Trace Dependents” in the “Formula Auditing” group.
  • Double-click the dotted-line arrow to open the Go To dialog box, select the address, and then click on Ok.

img8

 

To remove the arrows

  • Go to the “Formulas” tab in the ribbon
  • Select “Remove Arrows” in the “Formula Auditing” group.

img9

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

 

Users are saying about us...

  1. but how do you trace which worksheet the precedent or dependent is on ? It is easy when they are on the same sheet, but it just shows a "sheet" icon if it is on another sheet - not which sheet it is on.

    • Click on the arrow next to the sheet icon, it will open a Go To dialog box, with the dependent cell locations, click on them to go to the dependent cell location in other sheets

  2. With the trace precedents function in MS Excel 2013, is there a way to go from a dependent cell in another worksheet back to the original cell in the worksheet where you elected to trace precedents?

Leave a Reply to Abhinav Cancel reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube