Understanding which cells a formula refers to, and coming to a view on whether they are the correct cells in the circumstances, is a central activity in checking a spreadsheet. Excel offers about seven ways of determining the precedents of a cell, and they are all slightly different.
Inspection
The most obvious method for checking a formula's precedents is to examine it and to navigate to any cells that it mentions, one after the other.
Go To Special
One of the options of Excel's Edit | Go To | Special is Precedents.
Activating this will
| • | either, select a multi-area range consisting of those cells that are mentioned in any formula in the selection, so long as they are on the same worksheet |
| • | or, report that no such precedents exist. |
This command dates back to the very earliest versions of Excel, which were too primitive to have any notion of workbooks that might contain several worksheets. If a formula mentions a cell on another worksheet, with a formula such as =Sheet2!A1, Excel will ignore the fact. A selection of one or more formulas that don't mention any cells on the same worksheet will be reported as having no precedents even though they do refer to numerous cells on other worksheets.
Ctrl+[ (left square bracket)
The Ctrl+[ key combination works very like Go To | Special | Precedents. In fact, the actions are identical in many circumstances. But there are two situations in which Ctrl+[ will act differently.
| • | If the first reference in the formula of the active cell is to a cell or range on another worksheet, Excel will make that the selection, and ignore all the other precedents in that active cell or any other selected cells. |
| • | If there are no precedents found, Ctrl+[ won't complain. It will do nothing. |
Double click
Older versions of Excel allowed the precedents of a cell to be selected if the cell was double clicked. More recent versions use double clicking on the cell to mean you want to edit its contents. But the old behaviour can be restored by turning off the "Edit directly in cell" feature. This option is reached with Tools | Option | Edit | Edit directly in cell.
The action is identical to Ctrl+[, except that it applies to the cell double clicked on only (Ctrl-[ will show the precedents of all cells selected if there is more than one.)
Tools | Formula auditing | Trace Precedents
The Trace Precedents command draws
| • | blue arrows to indicate a linkage between the active cell and any other cells it refers to on the active workbook. Navigation is possible between the cells by clicking on the arrows. |
| • | a black arrow, leading to an icon representing other worksheets, which can be double clicked to see what references are present to other worksheets or workbooks and to navigate to them. |
Tools | Formula auditing | Evaluate formula
The Evaluate Formula command brings up a dialog through which a formula can be probed interactively.
| • | Good points: It can step through the precedent hierarchy of a calculation, and understands row and column matching. |
| • | Bad points: It evaluates lookup formulae to values rather than references, and insists on visiting terms in a prescribed order, left to right through the expression text, with no opportunity offered for backtracking. |
Go To while editing
Using Excel's Go To command is such a basic action that this section of the help starts by recapping it. The shortcut for it, F5, is well known. But what isn't so well known is that you can press F5 while editing a formula. If the portion of the formula being edited is a reference, then F5 will navigate to the reference and adjust the formula.
For example,
| • | Put the number 7 into cell A1 of a blank worksheet. |
| • | Put =6*INDEX(A:A,1) into another cell. The answer will be 42, because INDEX(A:A,1) evaluates to the first cell in row A, in other words, A1. |
| • | Start editing the cell and select the last final characters, the ones that read "INDEX(A:A,1)". |
| • | Now press F5. Excel will navigate to cell A1, scrolling the worksheet if necessary until A1 is brought into view, and indicate the cell with a border of animated dashes. |
| • | The text of the formula will change. Rather than =6*INDEX(A:A,1), it will read =6*A1 |
| • | There are circumstances when debugging a formula that you may like the change Excel has made in your formula, since it is so much easier to read. In such a case you would press Enter to keep the revised formula. But most times, you would press Ctrl+Z (Undo), to go back to the original formula, or Esc to abandon the formula edit altogether. |
You won't be able to see what's in A1, since Excel is busy displaying the formula that you are editing. But you will see where it is, and what labels surround it
Why this is useful
Understanding which cells a formula refers to, and coming to a view on whether they are the correct cells in the circumstances, is a central activity in checking a spreadsheet. That's why this help offered finding cell precedents as the most relevant example of why Excel's ability to select ranges that are of arbitrary shapes is interesting.