Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.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.
=COUNTIF(A1:A4,1)
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.
Arghhh - So many years of clicking the cell to change contents wasted! Thank you 🙂
Thankfully, I discovered this within a month!
Thanks so much are due to everyone helped make it known.
After many years of relying on this, I now find that it does not work in Excel 2013. Is there something I am doing wrong?
Thanks!
Many keyboards today no longer have a dedicated row for the F1-12 keys - with laptops and condensed keyboards in particular. You may have the hold down the function key (FN) while pressing F2. I can assure you the function works in 2014 MS WORD native settings.
What is the use of F2 key?
Please answer me fast?
Did you read the post?
Does the F2 have any impact on the loss of my network connection? Weird I know. Thanks
Thanks a lot for the information on F2. I am wondering if there is a way to get the cursor to go to the top left hand corner of a cell when I press F2 instead of the default position of end of the text way down at the bottom. I do a lot of cell editing with large amount of text in it.
After pressing F2, just press CTRL+Home
I have been looking for this, also! So, I just tried it out. Works with CTRL... OR WITHOUT! Just hit HOME or END to go back and forth to the beginning or end of the cell data (after F2 gets you in the edit cell mode).
Thanks!
thanks!
sum, count, average or any other formula not working on this below mention data. Furmulas worked when we rearrange cell with the help of F2 function key or double click on cell. Have any other option to rearange complete sheet or worksheet imediately?
Great Tip. Thank you. But with "... Hit Backspace five times and you'll get rid of your reference to column A. Now hit F2. " no need to hit F2 again as cursor pointing will already be enabled. Simple go to new cell or cell range and then press enter.
Hello Dan
I have a range of formulas that have been entered as text. Now, I know that upon entering the cell by double clicking or by f2, the excel will calculate it as a formula and display the result.
The thing is, there are too many cells and I would like some option to calculate all of them without entering each and every single cell.
Thanks for your support and regards!
It depends how the text has been entered, Elvis. You should be able to create a new column that simply contains the formula:
=VALUE(A2)
where A2 is the text version.
Then format that new column as a date.
I use F2 a lot. I am finding at my new job that the F2 + Ctrl + <- moves me to the next period for example =33.33+33.33, it stops at every . instead of total value. This twice as many clicks of the <- that I'm used to. Is there away to make it not stop at the .33? Thanks!
=IF(F2<1, "NOT RECEIVED",D2-F2)
THIS FORMULA IS NOT WORKING SO PLEASE ADVISE SIR.
HOW TO SOLVE THIS FORMULA PLS HELP ME SIR.
send detail my email ok sir.
Thanks & Regards
FIROZ AKHTAR