Most of us know that Excel allows you to evaluate a formula by tracing back to the formula’s precedents. That is to say, Excel will draw a line back to each cell that plays a part in the formula.
.
If you’ve never tried it, here’s the deal.
Click on the cell that contains your formula, then click the Trace Precedents command found on the Formula tab.
.
Excel will draw helpful lines back to each precedent cell.
.
.
Sometimes the lines lead to cells that are way off to the side of your spreadsheet (off the screen).
Instead of squinting your eyes while following the line as you scroll your way to the precedent cell, just jump there. Simply double-click on any of the lines to jump to the source cell.
.
.
If the precedent cell is off the current spreadsheet, Excel will give you an icon with a dotted line. Again, double-click on the dotted line (NOT the icon). This will call up the Go To dialog box where you can select which precedent cell you want to jump to.
.
If the precedent cell is in a completely different workbook, one of two things will happen when you double-click on the line:
- If the other workbook is open, Excel will call up the Go To dialog box, where you can jump to the precedent cell in the other workbook.
- If the other workbook is closed, you will an error stating that your reference is not valid.
Hi Mike. Here’s a couple of shorter shortcuts:
CTRL + [ Selects DIRECT dependents
CTRL + ] Selects ALL dependents
CTRL + SHIFT + [ Selects DIRECT precedents
CTRL + SHIFT + ] Selects ALL precendents
Hi Mike,
Nice little gem this article. The formula auditing tools are underused if you ask me.
Pity that goto box for off-sheet references is not sizable. You can’t see the entire formula when the workbook and/or worksheet name is long.
I have a tool which helps a lot here:
http://www.jkp-ads.com/reftreeanalyser.asp
Just wanted to plug JKPs RefTreeAnalyser tool – I don’t have to use it often, but it never fails to impress me when I do – everything for formula tracing that I can think of.
One word: explode.xla. Or is that two words?
Hi Mike,
great trick.
thank you for sharing your knowledge.
just another trick
if you double click twice you will be back to cell where is the formula.
so we ca way that double click toggle between the source cell and the cell where the formula is written.
Regards.