How to refer to a cell in Excel and have that reference remain unchanged

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

Here are some related posts:

How to set up a running total column in Excel How to find values in one set that are duplicated in another set in Excel How to change the case of your text in Excel Keep your row and column headings visible as you scroll in Excel How to reset or clear the last cell in Excel to make your worksheet smaller