If you have used spreadsheets made by other people you may have noticed that sometimes there are dollar signs in the formulas. This may seem strange, but there is a good reason! And that reason is absolute reference. But what is that?!
For example, a formula such as =$A$1 * A2
What do the dollars do? Why are they there? Does it actually have anything to do with money?!
All good questions.
So first, off, no, it has nothing to do with money. The dollar signs in a cell reference make it an absolute reference. This is to distinguish it from a ‘normal’ reference which is called a relative reference. When you copy a formula, the cells that it references move as well, relative to where the formula is. Hence the name relative reference.
For example, if the formula above was in cell B2, and we copied it down to B3, it would now say =$A$1 * A3. Because the “A2” in the first formula is relative to the B2 where the formula is. So, when it is copied to B3, now the formula shows A3 as it is relative to which cell the formula is in.
However, the A1 is NOT relative. Because of the dollar signs it is an absolute reference, so it absolutely does not change when the formula is copied down. That part of the formulas doesn’t change!
If we copy the formula down again, it would say =$A$1*A4. So, you can see why this is useful. Because we want all the number multiplied by 10, we need to always reference the 10, in cell A1, wherever the formula goes, and that is where the absolute reference is so useful.
Now, you don’t actually have to type the dollar signs. When you are writing your formula push the F4 key just after you have typed the cell reference, (or go back in and edit the formula and click on the cell reference). This will put the dollar signs in for you. If you accidentally hit the F4 key again, you will notice something strange. There will only be one dollar sign in front of the number in your cell reference. Click again, and only a dollar sign in front of the letter! Click again, and all the dollar signs are gone. Keep clicking and it will cycle around from two dollar signs, one in front of the number, one in front of the letter, no dollar signs.
Why would it do this? Because sometimes you only want one part of the cell reference to be absolute. If we wanted to copy our example formula across to column C, we might also want to now not reference A1, but B1, but when we copy the formula down, we always want to reference B1. So, the 1 is the thing we need to stay the same, but the A needs to change to B. So, we could write the formula =A$1 * A2. Now, when we copy it DOWN, it will say =A$1 * A3, and if we copy it ACROSS it will say =B$1 * B2.
Now, you can understand why there are dollar signs in formulas and start to use them yourself.