Cell Modes. What are they? How do we use them?
Believe it or not, you have been using cell modes your entire Excel life, you just may not have realized it fully.
In a nutshell, cell modes dictate how your cursor movement commands are interpreted while in various data entry states within Excel.
Being in the incorrect cell mode can be EXTREMELY frustrating when updating formulas while using certain tools, like Data Validation, Conditional Formatting, or the Name Manager.
To eliminate this frustration, let’s examine the different cell modes, their purposes, and how to control them like an Excel Ninja
Inputting Text or Numbers
The moment we begin typing text or a value into a cell, Excel automatically switches to Enter Mode.
Because we did not begin our input with an equals sign, Excel understands that we are not writing a formula.
If we press any of the arrow keys (up, down, left, right), we will leave the cell, commit the data to the cell and return to Ready Mode.
If we begin our data entry with an equals sign (or a plus or minus sign for you old-school Lotus users -wink-), we are placed in Enter Mode.
Pressing any of the arrow keys does not commit the data to the cell and remove it from Enter Mode. Instead, it switches from Enter Mode to Point Mode.
After navigating to a cell (Point Mode), if we enter an operator, like “plus”, we are placed back into Enter Mode.
Pressing an arrow key switches us back to Point Mode.
If we return to a cell that contains data, and press F2 (or click in the Formula Bar), we are placed in Edit Mode.
What makes this mode different is that the arrow keys act as navigation through the cell contents. We can “walk” through the cell’s contents the same way you would “walk” through a sentence/paragraph in a Word document.
Edit Mode Quirk
While in Edit Mode, if we wanted to add a new cell reference to a formula, pressing the arrow keys does not navigate/select adjacent cells.
We need to return to Point Mode.
Pressing the F2 key will switch us from Edit Mode back to Point Mode.
After navigating to the desired cell, you can switch back to Edit Mode by pressing the F2 key.
You can quickly switch back and forth between EDIT and POINT modes with repeated pressings of the F2 key.
Where User’s Run into Trouble
The main way user’s become frustrated with cell modes is when using Excel features like…
- Conditional Formatting
- Data Validation
- Name Manager
- Chart Ranges
A common issue arises when a user wants to edit the relative/absolute state of a cell reference.
While in a feature that references a cell, the user presses the left/right arrow keys to “walk” through a cell reference. Unfortunately, additional cell references appear in the field.
(Admit it; you’re already getting angry just thinking about when this happens to you.)
This is because our reference begins with an equals sign and we are in Enter Mode.
As we saw before, this causes Excel to automatically switch to Point Mode and create additional cell references.
The trick is to press F2 and switch to Edit Mode.
Now, we can “walk” through the formula making any necessary adjustments.
If we need to switch back to Enter Mode, we can press F2 again to revert.
If Cell Mode Status is Not Visible
It is unlikely but not out of the question, that you may not have Cell Mode status visible on the far-left of the Status Bar.
Status Bar items can be turned on and off to help customize the user’s workflow.
If you are unable to see the cell modes, right-click on the Status Bar and activate Cell Mode.
Key Point of Cell Modes
The key is to monitor the mode status in the lower-left corner of Excel and use the F2 key to switch to the desired mode.
This will reduce much of the frustration you encounter when updating formulas and references within Excel features.
Excel ESSENTIALS for the REAL World (The Complete Excel Course)
From Excel Beginner to Professional
Learn Excel from Scratch
OR Improve Your Excel Skills to Become More Confident
Check out our best-selling course