Leading the way in Microsoft Office Development
 Home|

Excel

|Word|PowerPoint|Consultancy|Feedback|Contact 
 Excel > Auditing > Tracing Cell References< Previous | Next > 

 

Step 1 - What are Tracer Arrows ?

 
 

Tracer arrows are arrows that can help you to understand the flow of data on a worksheet and can help you to understand formulas that contain lots of cell references.

 
 

These can be used to help understand and visualise the relationships between cells.

 
 

Tracer arrows will disappear if you change the formula they point to or you insert or delete any rows or columns.

 
 

There are 6 buttons on the Formula Auditing toolbar that can be used to add and remove tracer arrows from your worksheet.

 
 

Adding tracer arrows lets you visually step through which formulas refer to which cells.

 
 

If a referenced cell contains a formula and that formula also contains an error, then a red line is drawn between the formula cells.

 
 

Tracer arrows are also known as "cell tracers" and are always in the direction of the data flow.

 
 

Double clicking on any of the arrows you will be moved to the cell at the end of the arrow.

 
 

The terms dependent and precedent refer to the relationships that cells containing references to other cells have.

 
 

The tracer arrows are considered to be objects so they will not be displayed if the following option is selected (Tools > Options)(View tab, "Hide all").

 
 

 Remove All Arrows - Removes all tracer arrows from the worksheet.

 

 

Step 2 - Tracing Dependent Cells

 
 

Cells which contain formulas that refer to other cells are called dependents.

 
 

These are cells that use the value in the selected cell.

 
 

A cell that has dependents can contain either a formula or a constant value.

 
 

You can use the shortcut (Ctrl + ] ) to select the cells that are dependents of the active cell.

 
 

Alternatively you can select (Tools > Formula Auditing > Trace Precedents).

 
 

 Trace Dependents - Draws a tracer arrow to the active cell from formulas that depend on the value in the active cell. To add additional levels of indirect dependents, click the Trace Dependents button again. If Excel beeps it means you have traced all the levels of the formula.

 
 

 Remove Dependent Arrows - Removes tracer arrows from one level of precedents on the active worksheet. To remove the next level of arrows, click the Remove Dependent Arrows button again.

 

 

The rate is in cell "C2". You can find out which cells refer to this value by selecting the cell and pressing the Trace Dependents button.

 
   
 

The arrows are pointing to all the cells that contain a formula that refers to cell "C2".

 
 

The dot in cell C2 indicates that it has dependents.

 
 

The tracer arrows indicate that cell C2 is directly referred to by the formulas in cells "D5", "D6", "D7", "D8", "D9", "D10" and "D11".

 

 

Pressing the Trace Dependents button again will display another set of arrows, indicating the next level of dependents (or indirect dependencies).

 
   
 

You can press the Remove Dependent Arrows button to remove one level of dependents.

 

 

Step 3 - Tracing Precedent Cells

 
 

This allows you to trace cells in the opposite direction meaning you can start from a cell that contains a formula and trace back to all the cells that are referenced by that formula.

 
 

Cells that are referred to by a formula in another cell are called precedents.

 
 

These are cells whose values are used by the formula in the selected cell.

 
 

A cell that has precedents always contains a formula.

 
 

You can use the shortcut (Ctrl + [ ) to select the cells that are precedents of the active cell.

 
 

 Trace Precedents - Draws tracer arrows from the cells that supply values directly to the formula in the active cell (precedents). To trace the cells that supply values indirectly to the formula in the active cell, click the Trace Precedents button again.

 
 

 Remove Precedent Arrows - Removes tracer arrows from one level of dependents on the active worksheet.

 

 

The overall total is displayed in cell "D13". You can find out which cells are referred to in this formula by selecting the cell and pressing the Trace Precedents button.

 
   
 

The arrows are pointing to all the cells that the formula directly refers to.

 
 

A blue border is only placed around cells that are referred to as a range.

 
 

There is only one dot in cell D5, since the formula in "D13" refers to a range of cells.

 
 

If the formula in cell "D13" was "=D5+D6+D7+D8+D9+D10+D11" then there would be dots in all these cells, since they are all referenced individually.

 

 

Pressing the Trace Precedents button again will display another set of arrows, indicating the next level of precedents.

 
   
 

You can press the Remove Precedents Arrows button to remove one level of precedents.

 
 

It is also useful to toggle between the value and the formula layers to quickly check formulas have not been over-written with constants. The R1C1 reference style makes formulas that are “just a bit out” can be easily spotted.

 

 

Step 4 - Tracing References to Other workbooks

 
 

Sometimes you can have links to cell references that can be on different worksheets or even different workbooks.

 
 

When the cell reference is not on the active worksheet an arrow and a small worksheet icon appears.

 
 

When a cell contains a reference to a different worksheet or to a workbook a dashed tracer arrow appears with a small icon attached to it.

 
 

If the formula refers to a cell on a different worksheet (or even in a different workbook) then the tracer arrow is a black dotted line and the icon resembles a small worksheet.

 

   
 

It is not possible to trace the precedents when the arrow refers to a different worksheet or workbook ???

 
 

You can open the referenced workbook and then start a new trace from the referenced cell.

 
 

You can double click this dashed arrow to display the (Edit > GoTo) dialog box.

 
 

Selecting a particular reference and pressing OK will open the corresponding workbook / worksheet ??

 

 

Step 5 - Things to Remember

 
 
  • The trace arrows always point in the direction of the data flow and can also be used to move around a worksheet.

     
     
  • A cell can be both a dependent and a precedent if the cell contains a formula and the cell is referenced by another formula in a different cell.

     
     
  • You can trace the cells that indirectly refer to the active cell by pressing the Trace Dependents button again.

     
     
  • Double clicking on any of the arrows will take you directly to the cell at the other end of the arrow.

     
     
  • You can trace the cells that supply values indirectly to the formula in the active cell by pressing the Trace Dependents button again.

     
     
  • (Ctrl + Shift + [ ) - Selects all the cells that are directly or indirectly referred to by the formula in the active cell.

     
     
  • (Ctrl + Shift + ] ) - Selects all the cells that directly or indirectly refer to the active cell.

     

     Copyright © 2012 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >