Visiting the Corner
The current cell mode is displayed at the far left of the Status Bar.
Although it’s always been there, it’s amazing how many users never notice it changing while they use Excel.
There are four cell modes in Excel: Ready, Enter, Point, and Edit.
Ready Mode
Ready Mode is the default mode for Excel when you aren’t doing anything besides navigating from cell to cell or switching between ribbons while looking for a program feature.
Enter Mode
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.
Point Mode
Inputting Formulas
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.
Edit 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 Users Run into Trouble
The main way users 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
