Every now and then, I find myself in trying to decipher someone else’s spreadsheet. If the spreadsheet is complicated enough, the first thing I do is turn off the ‘Edit Directly in Cell’ Option.
Turning off the ‘Edit Directly in Cell’ option tells Excel to allow the editing of a formula in the formula bar only. Although this may seem like an odd thing to do, there are peripheral benefits that make this technique a useful auditing tool. Here’s what I mean:
.
The Default Behavior
By default, when you double click on a cell that contains a formula, Excel will color code the cells involved in the formula. This gives you a fairly useful visual indication of which cells make up your formua. However, if a cell on another sheet or workbook is involved in the formula, there is now way to see that (other than to look at the formula bar).
.
.
.Turning off the ‘Edit Directly in Cells’ option
In Excel 2007, Click the Office button and then click Excel Options.
Click Advanced at the left side of the dialog box.
In the Editing Options section, remove the check from the ‘Allow Editing Directly in Cells’ check box.
In Excel 2003 Select Options from the Tools menu.
On the Edit tab, remove the check from the ‘Edit Directly In Cell’ check box.
.
Benefits of Turning off the ‘Edit Directly in Cells’ option
Once you turn off the ‘Edit Directly in Cell’ option, double-clicking on a cell that contains a formula, will select all the cells involved in that formula. This is just as if you would press the Ctrl key and choose every cell yourself. With the cells selected, you can press tab to jump to every cell that makes up the formula.
If the formula is complex enough, you may want to highlight every cell involved. With the ‘Edit Directly in Cell’ option turned off, you can double-click the formula, and then change the cell color, effectively highlighting all the cells that make up the formula.
Here’s another nifty tip.
With the ‘Edit Directly in Cell’ option turned off, double-clicking a cell with a link to another workbook, will automatically open the workbook and take you to the cell that is linked.
RELATED STUFF
This is a nice little trick. I can see myself using this in front of my unsuspected collegues – highlighting all the related cells with just a couple of clicks. They will think I’m an Excel magician.
The keyboard shortcut Ctrl+[ will also select all direct precedents of the currently selected cell, regardless of the "Edit Directly in Cells" option setting. Ctrl+Shift+{ will select all direct and indirect precedents. Replace [ with ], or { with }, and get dependents instead.
Paul: I did not know about those hotkeys. Great tips!
Yeah…whenever I’m sitting at someone else’s computer trying to help them figure out a formula, I naturally do the double-click thing, but end up in edit mode in the cell…drives me nuts. I’ve never been a fan of that feature, but that’s just my opinion.
And great tip from Paul…I didn’t know that either.
Datapig: Cool tip!
Paul: Cooler tip!
For some reason when i do the ctrl+[ it only takes me to one precedent instead of to all of them. Or for example when I use your technique and double click on the cell it does not automatically open the file that has a precedent
How can I do this in excel 2010?
To do this in excel 2010:
File – Options – Advanced – “Allow editing directly in cells”