Tuesday 31 March 2015

Excel Tip: You don't know what you don't know - dollar signs revisited

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".

7 comments:

1. Glen, a good post. I've used F4 for many years but found that this doesn't work on Excel for Mac and despite much experimentation I haven't found a equivalent - I miss it so much when I'm working on my MacBook!!!!

1. I don't use a Mac myself, but I believe that CMD-t does the same thing.

2. Glen, you believe right (I just tried it), thanks so much. I've spent the best part of 2 years having to enter \$ manually, that is going to save me so much time!!!!

3. Pleased to be of assistance!

2. Glen--
Excellent suggestion about \$ signs! They are very valuable. And the F4 function is definitely the way to go!

I am in the habit of using them to lock down a specific row or column (or both), even when it isn't really necessary because I'm entering the formula in only one cell (or only one row or column). I do that for three reasons: (1) You never know when you might expand the spreadsheet into additional rows and/or columns, and the absolute reference becomes really necessary; (2) It gets you into the habit of remembering about relative vs. absolute references at all times; and (3) It's just plain good spreadsheet design!

1. Yes. I tend to do the same. Whenever I enter a reference I tend to consider what should be fixed. I rarely leave it completely naked of dollars!

3. There are 2 types of entering addresses of ranges:
1) typing of characters (primitive, bad),
2) meting out by mouse (brilliant).
When you mete a range out, normally Excel writes addresses without dollars, as relatives. Then you can use F4 for completing the absolute coordinates.
But is a difference what F4 changes: by entering it is whole range, both addresses, by following changes just the one address touched by cursor.
Very different behaves it in editing of noncell formulas, for example for conditioned format and for named formulas. Then is rule opposing: enters address with both dollars (absolute) and after F2 (switching editing mode: entering - changing) you change by F4 coordinates to relative ones.