How can the Cell Modes affect your work?

When you try to edit a Conditional Formatting formula, and you click the left or right arrows to move across formula’s text, Excel inserts unwanted cell references. Same thing happens when you try to create or edit a Defined Name formula, or a Chart series…

If you are not aware of the active Cell Mode, then working with formulas, defined names formulas or conditional formatting formulas, editing chart series can become very annoying.

What are these Cell Modes?

The Cell Mode is displayed in the bottom left corner of the Excel Application Window, also known as the Status Bar.

If you don’t see the Cell Mode displayed in Status Bar, you have to right click the Status Bar and click on Cell Mode:

Customize Status Bar

The normal state of worksheet cells is the Ready Mode. The Status Bar will display Ready when :

• no cell is edited, or:
• no Defined Name is edited, or:
• no Conditional Formatting formula is edited, or:
• no chart series is edited.

When you edit an Excel worksheet cell, a defined name formula, a conditional formatting formula, or a chart series, the Excel application can operate in 3 Input Modes:

• Enter Mode
• Edit Mode
• Point Mode

In Ready Mode, as you already know, nothing happens. The action starts when you start editing anything, Excel will start operating in one of the 3 Input Modes, depending on your actions.

Sometimes, Excel works in mysterious ways:

In some cases, Excel will start operating directly in Point Mode, even if you click inside the formula, with the intention to manually edit  the formula. If you’re not paying attention to the Status bar, and which Mode you are in, you could get unintended results in your formula. For example if the Point Mode is shown in the Status bar and you use the arrow keys or you click on a cell, Excel will insert that reference where your cursor pointer is, no matter if it’s in the middle of the worksheet name, for example.

The cases where Excel will start operating directly in Point Mode are: (may be more cases, if you have experienced such unusual behavior in situations not listed here, please let us know)

• When creating or editing a Defined Name, while working inside Refers To field,
• When creating or editing a Conditional Formatting formula,
• When creating or editing a chart series.

Just like many others, I asked myself:

Why does Excel starts operating directly in Point Mode in these cases?

My opinion is, because there is no way to manually switch to Point Mode (pressing F2 more than once will switch only between Enter Mode and Edit Mode), and in most scenarios you will refer to a worksheet range, Excel is simply trying to help…

If you are aware that Excel starts working directly in Point Mode in the 3 cases described above, and you intend to manually edit the formula or the chart series, all you have to do is press F2 once, and Excel will listen to you like a “well trained puppy”…

Switching between Input Modes:

• From Point Mode, you can switch to Edit Mode by Pressing the F2 key once;
• From Point Mode, you can switch to Enter Mode by Pressing the F2 key twice;
• From Edit Mode, you can switch to Enter Mode by Pressing the F2 key once.

Note:

If you want Excel to continue autocompleting cell references after you edit the formula ( Excel is in the Edit Mode in this case), then press the F2 key once to return to the Enter Mode. This way you will allow Excel to switch to the Point Mode, which is the mode where Excel will insert references in formulas. Excel will operate in Point Mode only from Enter Mode, NOT from Edit Mode!

Available Ribbon commands when the Enter Mode, Edit Mode or Point Mode are active:

When Excel operates in Enter Mode, most of the Ribbon commands are greyed out, except the Clipboard and Font tabs commands (not all of them though). Find and Replace commands will be available, but Excel will exit from cell editing, returning to the Ready Mode.

When Excel operates in the Edit Mode, most of the Ribbon commands are greyed out (unavailable), except the Clipboard and Font tabs commands (not all of them though). Find and Replace commands will be available, but Excel will exit from cell editing, returning to Ready Mode.

When Excel operates in the Point Mode, most of the Ribbon commands are greyed out (unavailable), except the Cut-Copy-Paste commands. Find, Replace commands will be available, but Excel will exit from cell editing, returning to Ready Mode.

The Go To command (Ctrl+G shortcut) is available in Point Mode, but only for typing a reference, the Go To Special menu is not available.

The Function Library Group from Formulas Tab in Ribbon is fully available in Point Mode.

From the Calculation Tab, only Calculate Now (F9 shortcut) and Calculate Sheet commands are available.

Important!

Note that if you press the Calculate Now command (F9 key shortcut) from Point Mode, Excel will replace the entire formula from the current cell with its calculated value. You have to use the Undo command (Ctrl+Z) to return to your formula!

You will read more about the F9 command in Chapter 2 – Excel Functions, it really is very useful if you know how to use it.

Default behavior of Input Modes:

There are 3 characters that will act like a trigger for formulas for Excel. For Excel to see the input as a formula, the first characters must be: “=“, “+“, “–“. If the first character you type into a cell is one of these 3 characters, Excel will “think”  you are entering a formula.

Enter Mode:

When you select a cell and start typing text except for the special characters denoting a formula (“=”, “+”, “-“) , Excel will operate in Enter Mode.

Enter Mode Behavior:

From this Mode, there are 3 ways to exit and return to the Ready Mode:

• using the arow keys from your keyboard,
• clicking on another cell, or
• pressing the Enter key from keyboard, Excel will move the selection to the new reference, keeping the text you typed in the original cell.

Edit Mode:

From Ready Mode, Excel will operate in the Edit Mode in one of these 3 cases:

• double clicking a cell,
• clicking anywhere inside the formula bar, or
• pressing the F2 key.

Excel will operate in these cases in Edit Mode, the Enter Mode Behavior previously described will not be available.

You can return anytime to the Enter Mode by pressing the F2 key!

The F2 key will return Excel to the Enter Mode, with the cell still open.

From the Enter Mode, there are only 2 ways to exit:

• clicking on another cell, or
• pressing the Enter key from keyboard, Excel will move the selection to the new reference, keeping the text you typed in the original cell.

Point Mode:

If you start typing one of these formulas associated characters: “=”, “+”, “-“, Excel will “think” that you are entering a formula. After typing one of these 3 characters and you:

• use the arrow keys from keyboard, or
• you click to select a cell or a range,

Excel will then switch to Point Mode, to help you select ranges more easily.

Point Mode Behavior:

When Excel operates in Point Mode, you have 2 options, if you don’t want to exit cell editing:

• Switch to Edit Mode by Pressing the F2 key once , or click inside formula bar;
• Switch to Enter Mode by Pressing the F2 key twice.

If you want to exit cell editing, simply press the Enter key, Excel will return to the Ready Mode.

Remember:

Excel will switch to Point Mode only from Enter Mode, NOT from Edit Mode! There is no command to manually switch to Point Mode!

This means that, if you want to operate in Point Mode after editing a formula, with Excel in Edit Mode, you have to press F2 to switch to Enter Mode, then you will be able to use the arrow keys or mouse to select ranges for your formula.

You do have the option to Enable or disable Edit Mode!

For Excel 2010 and up, click on File>Options> Advanced.

For Excel 2007, click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
Under Editing options, do one of the following:

To enable Edit mode, select the Allow editing directly in cells check box.
To disable Edit mode, clear the Allow editing directly in cells check box.

If you disable Edit Mode, Excel will surprise you again. Try double clicking any cell, Excel will not open the cell in Edit Mode and this is the only disabled action… If you click inside formula bar, you will be able to switch between Enter Mode and Edit Mode with F2 key as before disabling the Edit Mode.

Yes… You will see the Edit Mode displayed in Status Bar, because that Excel option will not actually disable the Edit Mode, it will disble only one of the 3 ways to edit a cell! (click here to read again the 3 methods to start working in Edit Mode)

I know it’s not the end of the world if you are not perfectly aware of these Input Modes, but if you understand them, you can avoid frustrations, you and Excel are understanding each other now!

If you enjoyed this article and perhaps learned a few things and you would like to continue Your Free Excel Learning Journey, please subscribe for new posts. This way you will be notified when a new tutorial is published. Use the Subscription form below to “sign up.” Remember it’s FREE!

Also, if you experienced such frustrations while working with Excel, or you have other related informations to share with us, please leave a comment.

Cheers,

Catalin