From the Not Just Numbers blog:
Last week, I posted a really simple tip for copying down a formula to the bottom of your data.
This turned out to be one of my most popular posts, registering over 2,000 page views in under a week.
I said in that post:
I’ve lost count of the amount of times I’ve used this in front of an experienced Excel user who has stopped me and said “What did you just do there?”.
Given its popularity, I thought I would post another simple tip that has regularly elicited that same response.
Coincidentally, this also relates to my most popular post ever (written two years ago, it still drew over 5,000 page views last month!), The dollar sign ($) in a formula – Fixing cell references.
If you don’t know why you would want to add dollar signs to a formula, then I would recommend reading that post. If you do, read on for a simple, but often missed, tip for adding them quickly.
When entering a reference in a formula, there are four possible ways in which you can apply the dollar signs:
1. Fix both the column and the row, e.g. =$A$1
2. Fix just the row, e.g. =A$1
3. Fix just the columns, e,g. =$A1
4. Fix neither the column, nor the row, e.g. =A1
This can be quite fiddly, typing the dollars in the right place, particularly if you have entered the reference by clicking the cell and then need to click the cursor in the right place to enter the dollar sign(s).
This is where this simple tip comes in.
As long as the cursor is in the reference, or immediately before or after it, you can use the function key F4, to toggle through the options above (in the order shown).
That’s it, pressing F4 once adds both dollars, twice fixes the row, three times fixes the column, four times removes the dollars again. If you got over-excited and missed the right one, you can keep cycling through the options until you hit it again.
Simple, I know – but again, only if you already know it!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.