From the Not Just Numbers blog:
We’re all learning, all of the time, when it comes to Excel. This week, I thought I would cover a topic I’ve covered before, but from a different angle.
Using the dollar sign in an Excel formula is one of those simple, but powerful, things that I constantly find that people have different layers of understanding of.
Part of the problem, is that once we think we know something, we are much less likely to learn any more about it.
I see this regarding the dollar sign all of the time, and although there is nothing complicated about it, I typically see four different levels of understanding. There may even be a fifth, as I’m still learning too!
As this is such a powerful tool, you might want to check to make sure that you’re not missing any of them.
Level One
“I’ve seen them in other people’s formulae, and wondered what they did.”
I would certainly recommend reading my earlier post on them, as you are missing out on a really powerful (and simple) tool.
Level Two
“I use them to fix a reference to a cell, for when I copy a formula”
Yes, you can fix a cell by using two dollar signs, e.g. $A$1, but did you know that each dollar sign has its own purpose. The one before the column fixes the column, and the one before the row fixes the row.
So $A1 will always refer to column A (and the row will change relatively), whereas A$1 will always refer to row 1 (and the column will change relatively).
Again, I would recommend reading my earlier post for a fuller explanation.
Level Three
“I use them in almost every formula to fix cells, rows or columns. I don’t know what I’d do without them!”
I was at this level for years. In fact I was at this level when I wrote the blog post referred to above! Then one day, I was looking over a client’s shoulder and saw the dollar signs changing at the press of a key. It was one of those “Hang on! Show me what you did there.” moments.
Using the function key F4 while on a cell reference in a formula (whether it already has dollars or not), will toggle through all of the options, e.g. if the reference is A1, then repeatedly pressing F4 will have the following effect:
First press: $A$1
Second press: A$1
Third press: $A1
Fourth press: A1
Keep pressing and it will go through that loop each time.
When I discovered this, I wrote a follow-up post. Keep learning!
Level Four
“I press F4, probably more than any other key!”
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.