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.

Improve this question
6
  • Feel free to improve kbd markup as well as phrasing of my question!!
    – Jonas Heidelberg
    Oct 9, 2012 at 9:30
  • 1
    Not an answer since it does not return the "marching ants" border and allow you to use the arrow keys, but a very useful technique which maybe you have overlooked: Once you have hit F2 and it shows coloured cell borders relating to the coloured references in the formula, you can move the coloured boxes and it will update the formula to use the new reference. eg in your example you could drag the green box from cell B2 to B3 and it will replace that in the formula (you can also drag the cell handles to extend to a larger range for example)
    – AdamV
    Oct 9, 2012 at 11:10
  • @AdamV: thanks for adding this, surely helpful for some people! I was specifically looking for a keyboard-only alternative to what you describe (hence the mouseless tag).
    – Jonas Heidelberg
    Oct 10, 2012 at 9:21
  • @JonasHeidelberg, did you read my second edit? I thought that addressed your concern about modifying an existing reference (rather than adding an additional). As best I can tell, Excel needs some reason to allow you to enter Point mode in an existing formula. The only two reasons that I've found are to 1) add an operator and reference or 2) modify an existing reference. Any info on why the second edit doesn't answer your question would be appreciated.
    – dav
    Oct 17, 2012 at 16:29
  • @DavidVandenbos yes I read you second edit, but it didn't address 100% of my question. See the accepted answer for what I was looking for. Sorry for not illustrating my question better...
    – Jonas Heidelberg
    Oct 19, 2012 at 12:54

3 Answers 3

Reset to default
4
+100

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.

Improve this answer
1
  • Perfect, this is exactly what I was looking for :-).
    – Jonas Heidelberg
    Oct 19, 2012 at 12:51
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.

Improve this answer
5
  • This is a nice summary - I hadn't noticed the Enter/Edit/Point text. However, F2 doesn't answer my question, because there doesn't seem to be a way to get back into Point mode for an existing cell reference in my formula. I'll clarify my question.
    – Jonas Heidelberg
    Oct 10, 2012 at 18:53
  • Please re-read my last paragraph (I just finished an edit). The order is important to get the results, but it is possible.
    – dav
    Oct 10, 2012 at 19:35
  • I completely understand, but I don't want to add an operator and another cell reference (which is what you describe) - I just want to correct an existing cell reference by reactivating Point mode for it and moving it around using the arrow keys.
    – Jonas Heidelberg
    Oct 15, 2012 at 13:33
  • I just updated answer for your last comment.
    – dav
    Oct 15, 2012 at 13:40
  • using F2 the 2nd time as explained in this answer works but will always start at the cell containing the formula. If you want to start somewhere else (e.g. at the cell used previously as reference as you state above) then you need to use F5 to open the Go To dialog. See my answer below. I believe using F5 is the only method you can change the reference to another sheet without using a mouse (apart from just typing the reference of course)
    – ssollinger
    Oct 19, 2012 at 12:52
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.

Improve this answer
2
  • Thanks - this is not exactly what I was thinking about, but a very usable second best option! (Since your suggestion basically erases the previous cell reference, using arrow keys starts from the cell the formula is in, not the cell that was previously referenced)
    – Jonas Heidelberg
    Oct 9, 2012 at 10:34
  • Yes, but you can do the above suggestion, not select anything and only press F2 again to continue building your formula - see the last paragraph in my answer (updated). Basically, pressing F2 again will return your dotted box!
    – Dave
    Oct 9, 2012 at 10:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .