The Mystery of Dollar Signs in your Spreadsheet, or How Do Cells Find Each Other?

Spreadsheets allow us to point to other cells when entering formulas. This is called a reference. A reference can point to a single cell (C1) or to a range of cells (C1:C10). References can use relative location (one cell down and two to the right) or absolute (C1). This post provides an overview and examples of these two methods for specifying cell location.

Cell references are relative references by default. That is, they identify other cells based on distance in both horizontal and vertical directions. This boils down to something like two cells to the right and one cell down from my current location.

The cell A1 has a formula that references the cell C2.

The cell A1 has a formula that references the cell C2.

When you copy and paste cells with formulas that contain relative references, your spreadsheet will follow this 1 down & 2 to the right from my current location type of pattern. This pattern is a relative reference.

When A1 was copied to A2, the spreadsheet automatically shifted the cell reference while keeping its relative position the same: 1 cell down and 2 over to the right from my current location.

When A1 was copied to A2, the spreadsheet automatically shifted the cell reference while keeping its relative position the same: 1 cell down and 2 over to the right from my current location.

The second option for locating another cell is by using an absolute reference. This method relies on a precise location, such as C2. The dollar signs in a cell reference act like anchors and keep a column and/or a row in a locked position.

If we create a formula and reference C2, but add a dollar sign in front of the letter C – like so, $C2 – and start copying and pasting the cell containing the formula from one cell to another, it will continue to point to the same column, even as the row it points to changes.

The formula contains a cell reference that includes a dollar sign ($) in front of the letter C.

The formula contains a cell reference that includes a dollar sign ($) in front of the letter C.

Adding a dollar sign ($) to the cell reference stopped the automatic shifting of the column that would otherwise have occurred when copying and pasting our formula. In this example, shifting did occur by row. This is because there was not a $ placed in front of the 2 in the formula's cell reference.

Adding a dollar sign ($) to the cell reference stopped the automatic shifting of the column that would otherwise have occurred when copying and pasting our formula. In this example, shifting did occur by row. This is because there was not a $ placed in front of the 2 in the original formula’s cell reference.

Alternately, if we place a dollar sign in front of the number 2 in our cell reference, and then copy and paste the formula-containing cell, it will continue to point to a cell in the 2nd row, even if the column portion of the cell reference changes.

The formula contains a cell reference that includes a dollar sign $ in front of the number 2.

The formula contains a cell reference that includes a dollar sign $ in front of the number 2.

Adding a dollar sign $ to the cell reference stopped the automatic shifting of the row that would otherwise have occurred when copying and pasting our formula. In this example, shifting did occur by column. This is because there was not a $ placed in front of the D in the original formula's cell reference.

Adding a dollar sign $ to the cell reference stopped the automatic shifting of the row that would otherwise have occurred when copying and pasting our formula. In this example, shifting did occur by column. This is because there was not a $ placed in front of the C in the original formula’s cell reference.

Finally, if you use two dollar signs, one in front of the letter D and the other in front of the number 2, as shown in the example, and then copy and paste the formula-containing cell, it will continue to point to D2. This is an example of an absolute reference while the two previous examples contained mixed references.

The formula contains a cell reference that includes a dollar sign ($) in front of both the letter D and the number 2.

The formula contains a cell reference that includes a dollar sign $ in front of both the letter D and the number 2.

Even after being pasted into a different column and a different row, our formula continues to point to D2, thanks to the use of dollar signs.

Even after being pasted into a different column and a different row, our formula continues to point to D2, thanks to the use of dollar signs.

In short, the use of dollar signs $ when referencing a cell indicates a strict or an absolute reference.

Mystery solved. You now know the difference and significance between using absolute and relative cell references. Right?

If not, help me out! Any particular spreadsheet how-tos you want to see? Comment below!

Leave a Reply

Your email address will not be published. Required fields are marked *