8

When I enter an Excel formula by hand avoiding the mouse, I can conveniently reference cells by using the arrow keys (->,<- etc.). For example, I can enter the formula =A2&B2 in cell C2 by entering

=<-&<-<-

The result looks like this:

enter image description here

If I want to change from B2 to B3, I can just press the downward arrow on the keyboard at this time.

How do I do the same thing later, after having left this cell (e.g. by pressing Enter)?

In other words, how do I get the flashing dashed line back when re-entering a cell with F2?

enter image description here

Edit:

Why I want this

Imagine the formula was entered into cell B3000 or even on a different sheet, and now I want to correct a cell reference from B2 to something nearby, like A3. It would be nice if I could select the reference to B2, somehow get back into "Point mode" (see oldest two answers below) to quickly modify that reference with the arrow keys.

Jonas Heidelberg
  • 2,120

3 Answers3

7

Pressing F2 will shift between Excel's editing modes. It's easy to tell which mode you're currently using by checking the bottom left corner of the Excel Window.

Generally, Enter is the mode that will overwrite the current contents of the cell highlighted.

Enter

Generally, Edit will place the cursor back in your cell to change the cell's internal contents.

Edit

And, generally, Point is used when a dialog box is being used to select a cell or range (like selecting data for a chart).

Point

EDIT: So, in answer to your question (finally), once your cell has a formula in it, you'll need to select F2 once to go into Edit mode, add an operator (like +), then select F2 a second time to enter Enter mode, then use your arrow keys and it automatically changes to Point mode to select your cell.

EDIT: To modify an existing entry (without adding an additional operator), you need to use F2 to enter Edit mode, then use your arrow keys to find the cell reference in your formula that you want to modify, then delete the old reference and press F2 again to enter Enter mode, then you can use your arrow keys to select your new reference in place of your old.

dav
  • 10,618
4

Do the following:

  1. Press F2 to edit the formula
  2. Cursor to the cell reference you want to change, shift+cursor to select (e.g. in your example press [Shift]+[Cursor left] twice to select "B2" within the formula)
  3. Press F5 to open the "Go To" box.
  4. Press Enter and you get the "marching ants" around the cell B2 as you wanted.
  5. Now use can cursor keys to change the cell reference, starting at the previous location.

If you want you can change the cell reference in the "Go To" box to something else before you press enter in step 4, but you specifically asked to start at cell B2 in which case you just press Enter immediately.

ssollinger
  • 389
3

You just press F2 again after highlighting the cell in question from within the formula.

F2 to highlight your formula (as you do today). Then using your keyboard, highlight the cell name in question (from within the formula bar) whilst holding down shift

EG, =A2&B2

...where B2 is highlighted and then press F2 again. Then use your cursor arrows.

Or, highlight the A2 and then press F2; you'll change the cell reference of which ever is highlighted.

Or, move the cursor to where you want to add extra code - eg, put it after the B2 and press F2 and you can the dotted box back which will allow you to (for example) select another cell reference, building your formula.

Dave
  • 25,513