How to use F2 and double-clicking to edit Excel formulas
Have you ever had a formula that references so many cells that you can’t keep it all straight? Well, there are two easy ways to make it much easier. These methods give you a “visual” on your formulas, highlighting each cell that’s in the formula. It also quickly positions you to edit the formula (faster than using the formula bar).
Here’s how you do it:
Get A Visual On Formula References (enter “Edit mode”) (keyboard): F2 – on the formula cell
Or the mouse version:
Get A Visual On Formula References (enter “Edit mode”) (mouse): double-click – on the formula cell
When you double-click or hit F2, you’ll see each cell in the formula surrounded by a colored border (as shown in the animation above and the graphic below). Assuming there aren’t too many cells, each border color will be different. This coloring will be true of the formula itself that you see within the cell and true of the border of the actual cells being referenced. Let’s say cell C1 is part of the formula that’s in A7, as shown below. When I double-click on A7 (or hit F2), ‘C1’ turns green in the formula in cell A7, and the cell C1 itself gains a matching green border — so you can relate them easily (the same with the other referenced cells). Similarly with A1:A3 – but they’re blue instead of green.
What’s even neater is that you can change the formula by actually dragging or expanding the cells borders (also shown in the animation above). For example, you could expand the A1:A3 range to be A1:A5 by dragging the fill handle on the bottom right (see below). The formula automatically updates to incorporate this change. Or you could pull the green border from cell C1 to cell C3 if you wanted to add C3 instead of C1 (by pulling on one of the straight borders).
I use this technique all the time to understand my formulas better and to modify/correct them. One slight benefit of using F2 is that your cursor always gets positioned at the same place – the end of the formula. Whereas, when you double-click, the landing spot of your cursor will depend on where the cursor is when you double-click (it could end up in the middle of the formula). If I’m going to be typing changes to the formula, I’ll typically use F2 since it gives me a predictable starting point. (Note: when typing change to the formula using F2 or double-click, you can just start typing directly into the formula as it appears in the cell — you don’t have to go to the formula bar). But if I’m just going to be dragging the references around, I may double-click because I don’t care where I end up within the cell.
Hopefully, these tricks will save you time. Let me know if you think I left anything out.