Fast formula editing
How much time do you spend editing formulas? Spend less with these essential formula editing tips.
These speed tips will help you edit cells lightning fast!
The Quick Tip saves you time when editing cell formulas or values, and shows you how to make changes faster.
The Bonus Tip helps you troubleshoot cell references quickly and neatly.
Extending this tip using the Expert Tip gives you a very efficient way to navigate and edit complex formulas and their dependencies.
We hope you find these tips useful.
Cell edit mode speed tips
Take charge of formula editing by mastering cell edit mode. Here’s how to edit formulas fast:
Get into cell edit mode
You can use these keyboard shortcuts to enter “cell edit mode”, editing the existing contents of the selected cell:
|Edit the selected cell||F2|
The flashing text cursor will be active at the end of the cell’s contents, ready to type or paste additional characters.
Double-clicking with the mouse on an existing cell will also enter edit mode, if the cell is not blank.
On a Mac, you can use Ctrl + U instead of F2.
When you are in cell edit mode, the status bar at the bottom of the screen or window shows “Edit” where it would otherwise show “Ready”.
If you start typing into a blank cell, you will be in “Enter” mode, not “Edit” mode. To get to “Edit” mode, you need to double-click in an existing cell, or press F2.
Moving around within a cell
If the cell already contains a formula or value, you might want to edit or delete a specific part of the existing content.
Use these shortcut keys to navigate within a cell being edited:
|To move the cursor to:||Shortcut|
|Start of cell contents||Ctrl + Home|
|Start of current line in cell||Home|
|Start of current word in cell / previous word||Ctrl + ←|
|Previous character in cell||←|
|Next character in cell||→|
|End of current word in cell / next word||Ctrl + →|
|End of current line in cell||End|
|End of cell contents||Ctrl + End|
These shortcuts can be easier to remember if you think of them like this:
|Navigation item/distance||Shortcut for start/earlier||Shortcut for end/later|
|Word||Ctrl + ←||Ctrl + →|
|Limit of cell contents||Ctrl + Home||Ctrl + End|
You need to be in cell edit mode for these shortcuts to work. Otherwise, they will move around cells in the worksheet grid instead of moving the cursor within the cell being edited.
Highlighting content within a cell
You can use the Shift key in conjunction with the above navigation keyboard shortcuts to highlight part of a cell’s contents.
Used in conjunction with shortcuts for cut, copy and paste, this can save a huge amount of time.
Comparing cell edit mode with enter mode
When you start typing in a blank cell, you get into “Enter mode”. In this mode, using the arrow keys will accept any input to that cell and select the adjacent cell in the direction of the arrow key pressed.
To get from “Enter mode” to “Edit mode”, you need to press F2 or click in the cell.
When in “Edit mode”, pressing F2 will toggle between Enter and Edit modes.
You will quickly become used to knowing which mode you are in.
When you are in Enter mode, the status bar at the bottom of the screen or window shows “Enter” where it would otherwise show “Ready” or “Edit”.
Referencing other cells with point Mode
When in edit mode, you can insert a reference to another cell by clicking on that cell.
You can also use the arrow keys to select another cell to reference to, if your formula starts with = and the cursor is at an appropriate part of the formula (such as just after
+), or in a function (such as
This is called Point mode.
When you are in Point mode, the status bar at the bottom of the screen or window shows “Point” where it would otherwise show “Ready”, “Enter” or Edit”.
Suppose you are in edit mode, editing cell E4 and it contains the following formula (with your cursor at the end):
If you want to append the formula to also include the value from cell D3, you could type + then D then 3.
However, you may prefer to “point” to cell D3 with the mouse by instead typing + then clicking on cell D3.
You could do this with the keyboard by typing + then using the arrows to point to cell D3.
Your formula would then become:
Effect of pressing arrow keys
When in “Edit mode”, pressing F2 will toggle between Enter and Edit modes. This controls what the arrow keys will do if you press any of them.
Arrow keys in Edit mode
In Edit mode, the arrow keys will navigate within the cell’s formula or contents. Press F2 to change to Enter mode, ready to point to other cells.
Effect of arrow keys in Enter mode
When in a formula with the cursor where a cell reference would go:
- the arrow keys will activate Point mode and point to other cells, adding the reference to the formula being edited.
When the current cell contents is not a formula:
- the arrow keys will accept the contents into the current cell, and select the adjacent cell in the direction of the arrow.
When editing cells, it can be helpful to think of F2 as a toggle between “Edit mode” and “Enter mode (ready to invoke Point mode)”.
Exiting cell edit mode
When you are done editing the cell, return to the normal datagrid “Ready” mode using one of the following shortcuts:
|To exit cell edit mode and:||Shortcut|
|Confirm all changes to the current cell||Enter*|
|Cancel all changes to the current cell||Esc|
* You can also choose where the selection moves after pressing Enter; we cover this in detail in another tip.
Check out the Bonus tip below for a quick way to edit and troubleshoot cells that contain references to lots of other cells.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
Download files for this tip
Get one for free.