Using dollars in cell references

Dollars are inserted into cell references to influence what happens when that cell reference is copied from one cell to another. It only affects behaviour when the cell reference is copied and pasted elsewhere. It does not have any effect if the cell reference is cut and pasted.

A dollar can be inserted before either the column letter or the row number (or both). The dollar fixes that bit of the cell reference (either the row or the column). So the dollar will mean that wherever that reference is copied within the worksheet, it will always reference the same row or column.

Whenever you reference a cell, you have four options that determine what happens when that cell is copied elsewhere in the workbook. Let’s assume a formula in cell A1 is referencing cell C1, and that A1 is then copied into cell B2. Below is the behaviour based on different references to C1.

=C1

• If A1 is copied into B2, then the formula in cell B2 will reference cell D2 instead of C1
• The entire cell reference floats and moves around as it is copied
• This is the most common type of cell reference. Either there is no intention to copy it elsewhere, or where it is being copied, it’s important that it always refers to the cell two cells to its right

=$C1

• If A1 is copied into B2, then the formula in cell B2 will reference cell C2 instead of C1
• The column reference is fixed by the $, but the row reference floats, and moves around as it is copied
• Wherever this formula is copied, it will always refer to column C, but the row reference will vary depending on where you copy it to
• This is useful if you always want a formula to refer to a unit price column, for example, but have several columns that calculate the annual price

=C$1

• If A1 is copied into B2, then B2 will reference cell D1
• The column reference floats, and moves around as it is copied, but the row reference is fixed by the $
• This is the same as the previous example, but locks the row instead of the column

=$C$1

• If A1 is copied into B2, then B2 will reference cell C1
• The entire cell reference is locked, wherever the formula is copied in the worksheet
• This is useful when referring to a static value such as an exchange rate or an overall unit cost

Excel makes it easy to toggle between the four values above in creating your formulae. When you're in the middle of entering a formula, put the cell reference in the formula, either by clicking the cell itself or typing its reference. Once you've done this, repeatedly tapping F4 will toggle between the four options above. It's handy and quick.

If you need any help, get in touch and I'll try to help.

• Tweet