formula audit not seen

Question

How to enable formula auditing (trace dependents / precedents) in pc excel?

Answer 1

Hi, I'm John! I will help you with this.

Can you confirm which version of Excel you are using (Microsoft 365, Excel 2021, Excel 2024, etc.)?

In desktop Excel, Formula Auditing is normally available under Formulas > Formula Auditing, where you should see Trace Precedents and Trace Dependents.

If you do not see those options, could you share a screenshot of:

• The Formulas tab in Excel
• The Excel version (File > Account > About Excel)

Also, is this happening in all workbooks or only one specific workbook?

Answer 2

In Excel for Windows, formula auditing (Trace Precedents / Trace Dependents) is available on the Formulas tab. If it is not visible or seems not to work, check and use it as follows:

1. Make sure objects are shown in the workbook
   1. Select File > Options > Advanced.
   2. In the Display options for this workbook section, select the correct workbook.
   3. Under For objects, show, make sure All is selected. If objects are hidden, tracer arrows will not appear.
2. Use Trace Precedents (cells that feed into a formula)
   1. Select the cell that contains the formula.
   2. Go to the Formulas tab, in the Formula Auditing group.
   3. Click Trace Precedents.
      • Blue arrows show cells with no errors.
      • Red arrows show cells that cause errors.
      • A black arrow to a worksheet icon indicates the precedent is on another sheet or workbook (the other workbook must be open).
   4. Click Trace Precedents again to see the next level of precedent cells.
   5. To remove arrows one level at a time, in the Formula Auditing group choose the drop-down on Remove Arrows and select Remove Precedent Arrows.
3. Use Trace Dependents (formulas that use a cell)
   1. Select the cell whose dependents are needed.
   2. Go to Formulas > Formula Auditing > Trace Dependents.
      • Blue arrows show dependents with no errors.
      • Red arrows show dependents that cause errors.
      • A black arrow to a worksheet icon indicates a dependent on another sheet or workbook (the other workbook must be open).
   3. Click Trace Dependents again to see the next level of dependent cells.
   4. To remove arrows one level at a time, in the Formula Auditing group choose the drop-down on Remove Arrows and select Remove Dependent Arrows.
4. See all relationships on a worksheet
   1. In an empty cell, type =.
   2. Click the Select All button (top-left corner of the sheet).
   3. With the cell still active, go to Formulas > Formula Auditing > Trace Precedents and click it twice.
   4. To clear all arrows, select Formulas > Formula Auditing > Remove Arrows.

If clicking Trace Precedents or Trace Dependents only makes Excel beep, either all levels have already been traced or the item is untraceable (for example, references to text boxes, embedded charts, PivotTables, named constants, or formulas in closed workbooks).

---

References:

• Display the relationships between formulas and cells
• Detect formula errors in Excel
• How to avoid broken formulas in Excel
• Calculate a running total in Excel
• Stock data type is working but the Insert data feature is broken. - Microsoft Q&A
• Excel files now not opening. - Microsoft Q&A
• solver in EXCEL doesn't work anymore, - Microsoft Q&A
• Promlem with ran out resources when canculation in excel - Microsoft Q&A
• SOLVED - Excel 21 stating "Microsoft has blocked macros from running because the source of this file is untrusted." for a network file - Microsoft Q&A