There are three different uses of dollars signs in formulas. In cell B1 we will insert the different "$" formula references to cell A1:
- =$A$1
- =$A1
- =A$1
What do these mean?
- =$A$1 - This means that if you copy this formula to any cell (example: D3) in any other column or row, it will still be and always be a reference to A1.
- =$A1 - This means that the column reference will always be "A", but the row will change based on the row of the cell you're placing the formula in. So, if you copy it to D3, then the value with show what is in $A3.
- =A$1 - This means that the row reference will always be "1", but the column will change based on the row of the cell you're placing the formula in. So, if you copy it to D3, then the value with show what is in D$1.
What do we use these for? Let's take a look:
In column "A" I have placed the values 1,2,3,4 in cells A1, A2, A3, A4. The values in cells B1 to C4 will be the result of copying that formula to each of those cells.
=$A$1
No matter where the formula is copied (B1:C4), the result is the value in "A1"
=$A1
The copied formula will always show a value from column "A", but the row will be based on the cell the formula is copied to.
For this next example, I changed the data a bit to show how the formula works. The typed values are in cells A1, B1, C1, D1. The formula will be copied to cells A2 to D5.
=A$1
As you can see, the formula maintains the values in Row 1 (Row has the "$"), but changes with the columns (Column "A" does NOT have a "$").
In conclusion, the use of the dollar sign "$" is very useful when you are planning on having to copy the formula across multiple cells. A properly placed "$" will allow you to ensure formula integrity.
*Special Note: If you have a formula using two "$" such as "$G$1", you need to be sure if you insert a row or column that the cell (such as G1) is still the cell you want to be referencing. It won't change with the shift of rows/columns.
ReplyDeleteAnother way to update the location of the dollar signs ($) in the formula is if you put the cursor in the formula in the cell location part (i.e. A1) and hit F4, it will cycle the $ sign in the formula, as follows:
ReplyDeleteHit F4 once and it will put a $ in front of both the column and the row
Hit F4 again and it puts it to one in front of the row only
Hit F4 again and it puts one in front of the column only
Hit F4 again and all dollar signs are removed.
If you have a range like Sum(A1:A20) and highlight the A1:A20, or even just the 1:A part of it and hit F4 the same cycle will occur for both cell references.