Using tracer arrows will allow you to quickly identify the following information:
1) All the cells that are used in a particular formula (these cells are called precedents).
2) All the formulas that refer to a particular cell (these cells are called dependents as the value of the formula depends on the value in this cell).
3) All the cells that contain errors (e.g. #DIV/0), #VALUE! etc).
This information will be displayed on the worksheet as blue coloured
arrows. The arrows always point in the direction of the data flow.
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").
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. |
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).
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.
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.
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.
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 ??
Important
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.
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext