What does the dollar sign mean in cell references?
When referring to a cell, what is the difference between these?
A1
A$1
$A1
$A$1For my situation, I need a formula that says “when A equals 8” but more importantly the difference between each of the four examples above.
02 Answers
The terminology is "absolute reference" vs. "relative reference" (and there's "mixed reference").
The dollar signs create an "absolute reference" and the row / column specified will remain fixed no matter where you move / copy+paste the cell. Example: $A$1 will stay $A$1 no matter where you move the cell.
Without it the address is a "relative reference" and is, more accurately, an offset from the current location. Moving the cell will adjust relative references accordingly. Example: A1 will become B4 if you move the cell containing the reference a column to the right and three rows down.
An address that only has one dollar sign (e.g. on just the row or just the column) is called a "mixed reference", since one of the row/column is absolute and the other is relative. Example: $A1 will become $A4 if you move the cell containing the reference a column to the right and three rows down. Likewise A$1 will become B$1.
More information can be found here.
0Dragging the cell has different results when you apply the $ sign to a cell reference.
Consider the $ as a lock. In an ordinary case (A1), whenever you drag a cell, its references will be automatically adjusted to the created cells, relative to the position of the original cell.
An example. Suppose you are referring cell A1 from D2. If you drag the D2 cell to the E3 cell, E3 will now reference B2 (because the offset from the original cell is (1,1))
If you lock the row reference ($A1), when you drag the cell, the row reference will be preserved. In the aforementioned example, E3 will reference to A2. Locking the column reference (A$1) will make it so that the column reference is preserved. In the example, E3 now references to B1.
Referencing $A$1 "locks" the reference, as such that any cells created from dragging will not alter that specific reference based on the offset to the original cell.
Microsoft also has the following topics on the subject:
3
