Tuesday, February 22, 2005

Edit Directly in Cell in Excel

Under the Tools | Options menu on the Edit tab there is a check box next to an item called Edit Directly in Cell.
By default this is turned on.
However turning it off allows access to a couple of cool features.

The first is editing in the function bar and not in the cell (as the name implies). This may not sound very cool, but when you have a long formula it pays off big time.
If you also have a formula that links to a lot of other cells or ranges on that same sheet, then these are all colour coded and easy to see when you first press F2 to edit the formula.

The second feature is not obvious at all. However turning off the option gives you the ability to double click a cell and be magically transported to the origin of that cell. Now this gets a little tricky with ranges or multiple selections, but is sure is a handy way to trace links back. Try it. On a sheet make a cell = to a cell on another sheet. Eg on Sheet1 put this formula in a cell. =+Sheet2!B29.
Now double click that cell. You will be taken to Sheet2, cell B29. This even works across open workbooks.