Do you remember playing Battleship (the board game). You called out the coordinates for your shot using letters for the row labels, and numbers for the column labels. Excel is laid out the same way (just reversed).
When you reference a cell you use its column and row coordinates. For example, A1 represents the cell in the first column (“A”) and the first row (“1”). When you reference that cell such as below you are using a relative reference.
=A1
This is called relative because if you copy that formula to another cell, the reference will adjust to be relative to the original reference. For example, if you copy the formula above to the cell immediately below the formula will be changed to the one below automatically. You basically move down one more row, so the row indicator is also increased by one.
=A2
This is done so that you can copy formulas and have the cell references be relative to the original formula. This makes it really easy to fill down a column and have the formulas be correct for each row or column you fill in.
How to keep the reference constant
Have you ever wished you could just reference cell A1 and not have the reference change when you copied the formula? You can do this with an absolute reference. An absolute reference is one that locks part or all of the column/row coordinates. You just place a dollar sign (“$”) in front of the part(s) that you don’t want to change.
So, in order to make the original reference absolute you would use the following.
=$A$1
And when you copied that formula to the cell immediately below you would get the following.
=$A$1
There is no change in the cell reference because its column and row references are “locked” with those dollar signs.
You can also just lock either the column or the row and let the other coordinate be relative. So you might see formulas with references such as the following.
=$A1
=A$1
[…] Note that the dollar signs (“$”) in the cell references are important. They make the references “absolute” so they don’t shift as you apply the formula to other cells. If you want to read a bit more about an absolute reference you can take a look at another post of mine about absolute versus relative references. […]
[…] Click here to read my post about absolute versus relative Excel references. […]