# Display the relationships between formulas and cells

Sometimes, checking formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) for accuracy or finding the source of an error can be difficult when the formula uses precedent or dependent cells:

**Precedent cells**are cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.**Dependent cells**contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

To assist you in checking your formulas, you can use the **Trace Precedents** and **Trace Dependents** commands to graphically display, or trace the relationships between these cells and formulas with tracer arrows (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.).

- Click the
**File**tab, click**Options**, and then click the**Advanced**category. - In the
**Display options for this workbook**section, select the workbook you want, and then make sure that**All**is selected under**For objects, show**. - If formulas reference cells in another workbook, open that workbook. Excel cannot go to a cell in a workbook that is not open.
- Do one of the following.

Trace cells that provide data to a formula (precedents)

- Select the cell that contains the formula for which you want to find precedent cells.
- To display a tracer arrow to each cell that directly provides data to the active cell, on the
**Formulas**tab, in the**Formula Auditing**group, click**Trace Precedents**. - Blue arrows show cells with no errors. Red arrows show cells that cause errors. If the selected cell is referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell to a worksheet icon . The other workbook must be open before Excel can trace these dependencies. If the other workbook is not open, Excel may prompt you to locate and open it.
- To identify the next level of cells that provide data to the active cell, click
**Trace Precedents**again. - To remove tracer arrows one level at a time, starting with the precedent cell farthest away from the active cell, on the
**Formulas**tab, in the**Formula Auditing**group, click the arrow next to**Remove Arrows**, and then click**Remove Precedent Arrows**. To remove another level of tracer arrows, click the button again.

Trace formulas that reference a particular cell (dependents)

- Select the cell for which you want to identify the dependent cells.
- To display a tracer arrow (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.) to each cell that is dependent on the active cell, on the
**Formulas**tab, in the**Formula Auditing**group, click**Trace Dependents**.

Blue arrows show cells with no errors. Red arrows show cells that cause errors. If the selected cell is referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell to a worksheet icon . The other workbook must be open before Excel can trace these dependencies. If the other workbook is not open, Excel may prompt you to locate and open it.

- To identify the next level of cells that depend on the active cell, click
**Trace Dependents**again. - To remove tracer arrows one level at a time, starting with the dependent cell farthest away from the active cell, on the
**Formulas**tab, in the**Formula Auditing**group, click the arrow next to**Remove Arrows**, and then click**Remove Dependent Arrows**. To remove another level of tracer arrows, click the button again.

See all the relationships on a worksheet

- In an empty cell, type = (equal sign).
- Click the
**Select All**button.

- Select the cell, and on the
**Formulas**tab, in the**Formula Auditing**group, click**Trace Precedents**twice.

Issue: Excel beeps when I click the **Trace Dependents** or **Trace Precedents** command.

If Excel beeps when you click **Trace Dependents** or **Trace Precedents** , either Excel has traced all levels of the formula, or you are attempting to trace an item that cannot be traced. The following items on worksheets that can be referenced by formulas cannot be traced by using the auditing tools:

- References to text boxes, embedded charts (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), or pictures on worksheets
- PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.)
- References to named constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.)
- Formulas located in another workbook that refer to the active cell if the other workbook is closed

- To remove all tracer arrows (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.) on the worksheet, on the
**Formulas**tab, in the**Formula Auditing**group, click**Remove Arrows**.

** Notes **

- To see the color-coded precedents for the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in a formula, select a cell and press F2.
- To select the cell at the other end of an arrow, double-click the arrow. If the cell is in another worksheet or workbook, double-click the black arrow to display the
**Go To**dialog box, and then double-click the reference you want in the**Go to**list. - All tracer arrows disappear if you change the formula the arrows point to, insert or delete columns or rows, or delete or move cells. To restore the tracer arrows after making any of these changes, you must use auditing commands on the worksheet again. To keep track of the original tracer arrows, print the worksheet with the tracer arrows displayed before you make the changes.