Welcome to another blog post in my Excel #FormulaFriday series. If you’re like me, you probably spend a lot of time in Excel working with formulas. And if you’re like me, you also probably spend a lot of time cursing Excel when the formulas don’t work the way you expect them to. Especially when you want to move around the formula using the arrow keys, does Excel annoyingly insert cell references unexpectedly? I’ve got some good news for you: editing Excel formulas is easy. This blog post will show you how to edit Excel formulas in a dialogue box easily, without that annoying cell reference insert.
So, before you think, the only way to get around this issue is to point and select with the mouse pointer. Think again. You can navigate with your arrow keys.
This is where understanding the difference between Edit Mode and Enter Mode in Excel.
Edit Mode In Excel.
In Edit mode, some commands are unavailable. For example, when Excel is in Edit mode, you cannot apply conditional formatting or change the alignment of the contents of a cell.
Also, the arrow keys behave somewhat differently when Excel is in Edit mode. Instead of moving the cursor from cell to cell, in Edit mode, the arrow keys move the cursor around in the cell. This is exactly the behaviour which allows editing of the Excel formula.
Enable Edit Mode.
Look to the bottom left of your Excel Window. You will probably see Enter. All you need to do is go into Edit mode, and this is done by firstly pressing the F2 key on your keyboard. You will now notice that the word Edit will appear in the status bar. This small step is key to being able to edit your formulas easily.
So remember press F2 to get into Edit Mode to edit a formula and Excel will allow you to easily edit your formulas in dialog boxes. Give it a go.