How To Identify Precedent Cells In Microsoft Excel
A precedent cell is a cell that is referred to within a formula in another cell. The cell containing the formula is referred to as the dependent cell.
There are two types of precedent cells, direct precedents and indirect precedents:
Direct precedent cells are referenced in the formula.
Indirect precedent cells are not referenced in the formula but are referenced in one of the cells referenced in the formula.
If you're working on a spreadsheet which contains precedent cells, it's useful to know how to identify these cells, as they can help you to better understand the spreadsheet.
In this article, we share 5 ways to identify precedent cells in Excel:
1. Pressing the F2 Key (Windows) or Ctrl + U (Mac)
The F2 key's main function is to edit the active cell, however if the active cell contains precedent cells on the same worksheet, the F2 key can be used to identify them.
In the spreadsheet pictured below, the formula in cell D2 is the product of cell B2 multiplied by cell C2. Pressing F2 highlights the precedent cell references in the formula and their precedent cells the same colour.
2. Using the Trace Precedents Command under the Formula Tab
To identify a cell's precedent(s) using the Trace Precedents Command, Click on Formulas in the Ribbon.
Then Click the Trace Precedents button
When the precedent cells are on the same worksheet a blue tracer arrow appears and points to the dependent cell.
When the precedent cells are on a different worksheet to the dependent cell, a black dotted tracer arrow with a worksheet icon at the top appears and points to the precedent cell. To get the cell reference of the precedent cells double click on the black dotted arrow (not the worksheet icon).
This generates the Go To dialog box which shows the cell references of the precedent cells. To jump to the precedent cells double click on the cell reference inside the dialog box.
N.B. If a red tracer arrow appears, this means one of the precedent cells is causing an error in the active cell.
To remove the tracer arrows, click on Formulas in the Ribbon Tab.
Then click Remove Arrows.
3. Using the Go To Dialog Box
If the precedent cells are on the same sheet as the dependent cell, you can use the Go To Dialog box to identify them. Press F5 or Ctrl + G (also Ctrl + G on Mac) to generate the Go To Dialog Box.
Click on the 'Special' button on the bottom left of the dialog box.
Then click on Precedents.
Excel will select the precedent cells.
4. Using the Ctrl + [ or Ctrl + G Keys
This is one of the best, yet least known excel keyboard shortcuts EVER!!! It's particularly useful if you work with complex reports and dashboards that contain cells which reference other cells on different tabs.
If the active cell contains precedent cells on the same sheet, Ctrl + [ (also Ctrl + [ on a Mac) will select direct precedent cells on the active sheet.
If multiple precedent cells are highlighted, like in the example below, you can rotate among them by pressing the enter key or the tab key.
If the active cell contains precedent cells on a different worksheet, Ctrl + [ will jump to the first referenced cell in the formula.
To return back to the original cell press the F5 key or Ctrl + G keys, which will generate the Go To Dialog Box. The box will automatically populate the cell reference of the original cell, so just press enter to jump back to the original cell.
It's important to note that if the precedent cells are on different worksheets to the dependent cell, Ctrl + [ will only take you to the first reference in the formula. If you need to trace multiple precedent cells on different worksheets, then it's best to use the trace precedents command which we discussed earlier on.
5. Using the Ctrl + Shift + [ keys
If the active cell contains precedent cells on the same sheet, Ctrl + Shift + [ will highlight all direct and indirect precedent cells on the active sheet.
If the precedent cells are on a different worksheet, Ctrl + shift + [ will jump to the first referenced cell in the formula.
Bonus Tip – Quickly view Precedents value without going to the cell
If you want to quickly view the value of the precedent cell without having to go to the cell, you can highlight the cell reference in the formula and press F9 (Cmd + = on Mac) to view the value of the cell without having to jump to it.
