‘Edit directly in cell’ – Quick Excel Tip

There is a setting in the Options -> Edit -> Setting section called ‘Edit directly in cell.

edit-cell-1

The ‘Edit directly in cell’ related to the way Excel shows formulas in the cell and in the toolbar when you are in Edit mode (which is what you get when you click F2).

Edit directly in cell –activated

When Edit directly in cell is activated the formula in the toolbar does not get colour-coded but the formula in the cell does.

edit-cell-2

Edit directly in cell – not activated

If you choose to uncheck the Edit directly in cell option then you will get the formula in the toolbar colour-coded but the formula in the cell is not.

edit-cell-3

Which setting is better?

I find it more efficient to work with the ‘Edit directly in cell’ option unchecked.
There are two main advantages:

  1. If you are editing a formula that is longer than the cell (which is almost always the case) then you can see the whole colour-coded formula in the standard Excel toolbar.
  2. It is easier for the eye to focus on the same area of the spreadsheet (the toolbar) every time you are editing a formula. When you are copy-pasting sections of a formula or working with the Insert Function dialogs this makes life a lot easier.

This is a functionality that only takes seconds to implement so try both options and find out for yourself which option you prefer.

Recent posts by Rickard Wärnelid

Tags:

Comments for “‘Edit directly in cell’ – Quick Excel Tip”

  1. Ferry Firmans says:

    I agree with Rickard, and prefers to work with the ‘Edit directly in cell’ unchecked. A note about this functionality which me made choose this option: double-clicking on the cell will shade (show) the ranges of precedent cells if they are in the same sheet. Otherwise if the precedent ranges are on another sheet the action will take (jump)you to there. The fault with it is when it refers to precedent in multiple sheets. Try it!

  2. Tim Heng says:

    I agree, but it’s not something that you can only get by unchecking the option box. The option box is only a ‘default edit’ setting. Even if the box is ticked, you can have the same effect by clicking on the formula bar when editing. Conversely, if the box is not ticked, you can have your colours in the in-cell formula by clicking on that instead.

  3. Lincoln says:

    Thanks for the article. I’d never considered turning that option on, and now I know what it does!!

    One thing, in the text of the article the sub headings showing the two different options read exactly the same. You need to change the second on to “Edit directly in cell – Not activated”.

  4. Lincoln,

    Thanks for picking up the typo – all sorted now.

Comment on this Article