1

When your typing a formula, you can hit one of the arrow keys to select a cell with your keyboard:

enter image description here

That's nifty because you don't have to reach for your mouse to select a cell. However, while editing a function after you've already typed it out, you can't select a cell with your keyboard (I can understand if excel blocks out the left and right arrow keys because it'd then be confusing for the program to know whether I want to move the cursor within the active cell or whether I'm trying to move out of the cell to select a range -- but why block the up and down keys as well?)

So, when you're trying to edit a function, say, like here where I want to change the cell "D5" by clicking on a appropriate cell), you can't use your keyboard:

enter image description here

Any workarounds?

  • when you're trying to edit a function, say, like here where I want to change the cell "D5" by clicking on a appropriate cell), you can't use your keyboard ?? clear 'D5' symbols by backspace, then select another cell address using arrow keys, what's a problem? – Akina Aug 13 '18 at 8:45
  • That doesn't work. You can't select cells while editing even if you clear "D5" -- you'll either have to delete the whole cell (which can be cumbersome if a function is quite long and you only want to change one cell) or use the mouse to select the cells. – WorldGov Aug 13 '18 at 9:04
  • Oh! I understand it seems... you want to switch from "edit formula as a text" to "select cell as argument by arrows", is it? If so, I don't know the solution... – Akina Aug 13 '18 at 9:16
2

With the cell containing the function (e.g. SUM(E3,D5) selected, press F2, which will put the cell in edit mode, with the caret at the end.

Move the caret with the arrow keys and select the reference you want to change, with shift and arrow keys (e.g. E3 selected).

Press F2 to change to reference mode and use the arrow keys to move to the cell you want to refer to in the formula. Note that the formula is changing accordingly.

Press Enter to confirm the new formula.

0

Use named ranges. Instead of just picking cells as arguments to functions, create a named range with a meaningful name. Press Ctrl+F3 to get to the Name Manager Dialog box, New, and use the name 'Inventory', with a Refers to of E3:E5. Then you can create your function =Sum(Inventory). Later if you need to modify that range, go to the Name Manager Dialog box, select the row with the range you want to modify, press Tab, and you can reselect (or edit) the Refers to.

This has the added benefit of documenting your work. Another person can see the formula =Sum(Inventory) and know what Inventory means in your business context without having to know where in the workbook it exists.

Keyboard way to just create a new named range: Alt+M, M, D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.