Friday, July 1, 2011

Microsoft Excel: Highlight all cells referenced by a formula

When helping a client, I often look at worksheets I am totally unfamilar with. This makes it hard for me to know what’s going on to easily find the problem. To help me understand how cells and formulas are interrealted, I will highlight all the cells referenced by a formula.

To do this, select a cell with a formula and press Ctrl+[ (open-square-bracket). Excel highlights all the cells referenced by the formula. Use Enter and/or Tab (Shift+Enter and/or Shift+Tab to shift into reverse) to move among the selected cells.

But what if you want to do the reverse, and see the formulas that reference the a cell? You guessed it, select the cell, and press Ctrl+] (close-square-bracket).