Excel $ sign

Excel $ sign (Absolute and Relative Referencing)

Download this file to follow along with the video and to do the next exercise.

↓ Excel-$-signs example and exercises

One of the most common problems with spreadsheets is the lack of understanding with regards the use of absolute and relative referencing (the Excel $ sign).

In fact in our courses this is the single biggest reason for mistakes made later in the course. Everyone understands the VLOOKUPS/ SUMIFS/ etc and gets them correct in the first cell but then when they copy and paste them, the function is correct, but the answers are wrong and it is only because of the $ signs.

This referencing is how you tell a formula to react as it is copied to other cells. The best way to explain this is with some examples and a video clip.

Watch the video clip and/or read the text below

In the diagram below, cell C5 contains the formula =A2 and will therefore look at cell A2.

If we had to copy this cell and paste it to the right (in cell D5), Excel assumes that, as you originally referred to cell A2, and now you have moved one to the right, you want to also refer to one to the right. So a reference to A2 becomes a reference to B2.

If you copy a cell along a row Excel wants to change the columns (the letters in the formula).

If we had to copy this cell and paste it below (in cell C6), Excel assumes that, as you originally referred to cell A2, and now you have moved one down, you want to also refer to one down. So a reference to A2 becomes a reference to A3.

If you copy a cell down a column Excel wants to change the rows (the numbers in the formula).

If you copy this cell one to the right and one down (to cell D6 as shown below), Excel thinks you also want to refer to one to the right (from column A to column B) and one down (from row 2 to row 3). Hence the formula becomes = B3.

You can tell Excel to NOT follow this principal by using a $ in front of the letter or number that you want to stay static.

For example, a formulae containing =$A$2 will always refer to cell A2, no matter how it is copied (see below).

What you have told Excel is please don’t change the A or the 2 when you get copied and pasted. In cell D5 Excel would have wanted to change the A to a B, but the $ means that it stays an A. In cell C6, Excel would have wanted to change the 2 to a 3, but the $ means that it stays a 2.

You don’t have to put a $ sign in front of both the letter and the number. In the example below we have only put the dollar sign in front of the number. We have told Excel that it can do what it likes with the letter, but the number must always be a 2. So in cell D5, the reference to A2 changes to B2 because we have moved to the right, Excel wanted to change the A to a B and because there was no $, it could change it.

In cell C6 however, when Excel wanted to change the 2 to a 3, the $ forced it to stay a 2. Because the copy is downward, the reference to column A stays the same. In cell D6, the copy has been moved one down and one to the right. Excel realizes that it can change the A to a B, but is not allowed to change the 2.

In the example below we have only put the dollar sign in front of the letter. We have told Excel that it can do what it likes with the number, but the letter must always be an A. So in cell D5, the reference to A2 stays A2 because we have moved to the right, Excel wanted to change the A to a B but couldn’t because of the $.

In cell C6 however, when Excel wanted to change the 2 to a 3, the lack of a $ means that it is allowed to change. Because the copy is downward, the reference to column A stays the same. In cell D6, the copy has been moved one down and one to the right. Excel realizes that it can change the 2 to a 3, but is not allowed to change the A.

By using this knowledge it is possible to force Excel to follow certain patterns. Instead of building a formula per row or column, you may be able to create a single formula and copy it to many cells.