F2: the most useful key in Excel

F2 is probably the most useful key in Excel. Below's a description of what it does.

It toggles between Edit mode and Enter mode. If you're navigating across the spreadsheet, hit F2 when you've settled upon a cell, and the cursor will show that you're now editing its contents. The left and right arrow keys will move within the formula or value as opposed to continuing to move you around the spreadsheet.

But here's the neat trick. Imagine you hit F2 and you're in the middle of editing a formula. If you need to insert a cell reference, hit F2 again. This will move back into Enter mode, allowing you to use the cursors to choose the cell or range that you're looking to reference.

Below is an example.


Imagine you've got that formula in cell C1. But instead of counting the number of instances of the value 1 in cells A1 through A4, you instead meant to check the equivalent cells in column D.

Go to cell C1 and hit F2. The cursor will start flashing at the end of the closing bracket. Hit Left three times and it will move to the position before the comma. Hit Backspace five times and you'll get rid of your reference to column A. Now hit F2. Visually, there will be no evidence of you having pressed F2, apart from a tiny change in the bottom-left corner of your screen. Just below your sheet tabs, the word Edit will change to the word Enter.

Now you can use your cursor keys (together with Shift and CTRL if you like) to select your range in column D. Hit Enter and your formula is complete.

I've found that few people know about this toggle, and people end up typing all cell references once they're editing a formula that already exists.

This entry was posted in Quick tips and tagged , . Bookmark the permalink.

17 Responses to F2: the most useful key in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *