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.