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 seven ways to trace precedents of a cell, and they are all slightly different. 

• Read more: Group cells in Excel

1 – 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.  To do this simply select the cell you want to check and press F2 on your keyboard – you should end up with something similar to the image below: 

2 – GO TO SPECIAL

To bring up the ‘Go to’ dialog press the function key F5 or CTRL+G. 

Next, choose the ‘Special…’ button and an additional set of options are offered from which you can select the ‘Precedents’ or ‘Dependents’ options – for this purpose we will select ‘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. 

3 – CTRL+[  

The Ctrl+[ key combination works very like the ‘Go to Special’ method above.  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 is to a cell or range on another worksheet, Excel will make that the selection, and ignore all the other precedents in that formula. In the example below, Excel would select the ‘Calc of Inp’!$H$104 cell only. 

If there are no precedents found, Ctrl+[ will not select any cells. 

4 – 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 ” Allow editing directly in cells ” feature.  This option is reached by selecting the ‘File’ menu followed by ‘Options’, Advanced tab and finally by unchecking the option ‘Allow editing directly in cells’. 

It is worth mentioning that not all keyboard layouts have the ‘[‘ character or it might be not accessible only by pressing simultaneously with CTRL – in this case the ‘Double click’ method is a valid alternative. 

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.) 

5 – FORMULAS | 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. 

6 – FORMULAS | 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. 

7 – 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 (or CTRL+G), is well known (see method 2).  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. 

Start editing the cell and select any cell reference – in our example below the cell reference selected is $H$149: 

Press F5 (or CTRL+G) and Excel will show the Go To dialog with the selected cell reference on the ‘Reference’ field – press ‘OK’ 

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 article offered finding cell precedents as the most relevant example of why Excel’s ability to select ranges that are of arbitrary shapes is interesting. Nevertheless, modern spreadsheets do have very complex formulas with multiple references in different worksheets making the trace of precedents a very onerous task both in time and money and where we believe that the OAK Formula Walker can give a significant reduction to the time needed to trace formula precedents. 

• Read more on Excel: How to delete named ranges