Welcome to the first post of 2015! I hope you've had a great break and are ready to explore more of what Excel has to offer in the New Year.
Many of you will know Bill Jelen from his many Excel books and/or his excellent (no pun intended) website, www.mrexcel.com. Well Bill is compiling a new book, MrExcel XL - 40 Greatest Excel Tricks and he sent me the following tweet over the Christmas break:
My Greatest Excel Tip of All Time would be....
FANFARE....
"Learn how to use absolute references using $ and F4"
I have copied the information from my original post below, but if you like, you can still see the original, The dollar sign ($) in a formula - Fixing cell references, here.
Relative cell references
When you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position:
EXAMPLE:
If we have the very simple formula "=A1" in cell B1 it will change as follows when copied and pasted:
Pasted to B2, it becomes "=A2"
Pasted to C2, it becomes "=B2"
Pasted to A2, it returns an error!
In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula.
The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A.
This behaviour is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in.
But in some situations, you want some or all of the references to remain fixed when they are copied elsewhere.
The dollar sign ($)
This is where the dollar sign is used.
EXAMPLE:
Take an example where you have a column of Sales values in Pounds Sterling in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into the formula but it would be more sensible to refer to a cell where the exchange rate is held, so that it can be updated whenever it is needed.
The simple formula for cell B2, would be "=A2*E1", however if you copy this down, then the formula in cell B3, would read "=A3*E2" as both references would move down a row as described above.
This is where the dollar sign ($) is used. The dollar sign allows you to fix either the row, the column or both on any cell reference, by preceding the column or row with the dollar sign. In our example if we replace the formula in cell B2 with "=A2*$E$1", then both the "E" and the "1" will remain fixed when the formula is copied. i.e. in cell B3, the formula will read "=A3*$E$1", still referring to the cell with the exchange rate in it.
In this example we have fixed both the row and the column, but in other situations, you may just want to fix one or the other, for example:
Above we have a spreadsheet calculating the times tables where we want to every cell in the white area to be the product of its row and column heading. This is easy using the dollar symbol. In cell B2, the formula without dollars would be "=A2*B1", but for this formula to work when copied to each column, we need it to always look at column A for the first reference and to work for each row, we need to always look at row 1 for the second. Using the dollar sign to do this, it becomes "=$A2*B$1". This can then be copied to every cell in the white area.
Quick Tip
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.
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".
Using the F4 key has been second nature for such a long time and so very useful that I can't imagine that everyone doesn't know this shortcut.
ReplyDeleteKeep the tips coming in - most are very helpful and an eye opener to many a seasoned user.
Much appreciated. A Very Happy New Year to Glen.
Thanks. You'd be surprised, it's not as well known as you think.
DeleteI would suggest that a named range is more appropriate in most situations, especially for a single cell, such as an exchange rate.
ReplyDeleteUsing named ranges makes the formulas a lot more readable and should always be preferred to using the dollar sign in the formula.
Just saying.
Fair point - either approach would have worked for the exchange rate, but a named range wouldn't have highlighted how to use the dollar sign. And there are many situations where named ranges aren't a possible substitute for dollar signs.
Delete@Glen, I haven't used a direct cell reference for a couple of years now and I have yet to find a situation where names cannot be used.
DeleteStrictly speaking, it is not the range that is named but the formula that references it. Although absolute range references are most common, there is nothing to stop you creating a formula as a relative reference and F4 works fine in the name manager 'refers to' box.
Peter B
That's true, but I think I'll save named ranges for a separate post.
DeleteIt's a bit of a pain though when you use Excel on a Mac as the F4 key doesn't do this! But on Windows the F4 key is brilliant.
ReplyDeleteI don't use a Mac myself, but I understand that Cmd + T does the same thing.
DeleteThanks for that, I'll try that when I next get a chance!
DeleteMy Excel “Tip of All Time” is one step beyond your example. I’ve used F4 to place the $ sign as needed for years. But I’ve used one other step that is a real time saver. In your first example, assuming the values in column A were already entered, put the cursor in cell B2, click and drag down to B7, highlighting B2 through B7. This is a “plan ahead” step, showing where all the answers are going to be. Now type in “=A2*E1” and hit the F4 key once to change E1 to $E$1, then hold Ctrl and press Enter. Notice that all the formulas were entered in one step.
ReplyDeleteCtrl-Enter is my all time quick way to copy paste in a single step. But you must plan ahead, first, highlighting the places where the answers are to be copied to.
In your second example, assuming all the numbers in row 1 and column A are in place, put the cursor in cell B2, and highlight all the cells down to M13. Now enter “=A2” and hit F4 three times to get $A2, press * and enter B1, and press F4 twice to get B$1. At this point (instead of pressing Enter, and doing copy paste operations) hold Ctrl and press Enter. All the formulas are entered in a single step.
I learned Ctrl-Enter so long ago, I have no idea where I saw it first. But I have not seen anyone describing it in a long time. It’s my “Tip of All Time.”
Happy New Year.
Bill Simpson
bsimp@flash.net
214-341-5170 (in Texas, USA)
Thanks, I'd not seen that one before. I would normally copy down by dragging the square at the bottom right of the cell (or double-clicking it if there are already formulae in the adjacent cells). That's the same number of steps if we're talking about copying down a column (less if the double-click is an option), but I would imagine Control Enter really comes into it's own where the cells in which you want to enter the same formula are not held in one continuous column.
DeleteThe use of absolute references is one of my favourites. However, one of my other favourites is using the replace function when making copies of an array which involves formulas with absolute references. A good example would be the times tables grid above. If you wanted another grid, but with the numbers across the top and side in a different order, just copy the table right? Not quite.
ReplyDeleteIf you copy it with the $ signs intact, the references remain. So I select the array, use replace, and replace $ with nothing, then copy and paste the array across. This makes all the absolute references into relative references in both tables, but keeps all the formulas doing what they should. You can then alter the values in the header row and first column in either table.
Note: only do this if you don't want to adjust the array's size at a later date. Then you'll need to go back and put in your absolute references again.
Nice tip Eddie. You can do all sorts with replace in functions if you apply a little creativity!
DeleteGreat post..! Worthy for sure.
ReplyDelete