See all Microsoft Excel tips
Strategy: There are a few options.
Option 1: Select the cell. Hit F2 to edit the cell, as shown in Fig. 490.
As shown in Fig. 490, all of the references in the formula bar will light up with different colors. If the precedent cell is in the visible portion of the window, the cell will be surrounded by a box of the same color as the formula. You can’t tell from this black and white book but, in the formula bar, cell E30 is a bright blue. The box around cell E30 is a matching blue. Cell C4 is a dark green. You cannot see C4 in the visible worksheet, so there is no matching dark
green cell. Cell D14 is a light green in the formula bar. The box around D14
is a matching green. The final term of the formula points to an off-sheet reference. This term appears black in the formula bar.
Additional Information: If you need something more permanent
than this, you can use Formula Auditing to draw the blue arrows to all
of the precedent cells. Select cell D32. From the menu, choose Tools – Formula
Auditing – Trace Precedents. Blue arrows draw to all the cells that are referenced in the D32 formula. As shown near the bottom left of Fig. 491, the arrow to the sheet icon indicates that at least one reference is on another worksheet.
If you immediately Trace Precedents enough times, Excel will trace the precedents of all the arrowed cells. After a few iterations of the command, you will see all of the cells that factor in to the calculation, as shown in Fig. 492.
To remove all of the arrows, choose Tools – Formula Auditing – Remove All Arrows.
Summary: Tracing Precedents gives you a quick visual view of all the cells that are used to calculate a formula.
Commands Discussed: Tools – Auditing – Trace Precedents; Tools – Auditing – Remove All Arrows
See all Microsoft Excel tips
