Tutorials

Absolute vs Relative References in Excel (The $ Sign Explained)

What does the $ sign mean in Excel? Learn relative, absolute and mixed cell references, the F4 shortcut and a worked example.

By ExcelPanda Team · Published · 5 min read

Relative references (the default)

Type =B2*C2 in D2 and copy it to D3. Excel changes it to =B3*C3. The reference is relative: it is stored as a position relative to the formula, so it shifts with the formula. This is what you want most of the time.

Absolute references and the dollar sign

Sometimes every row has to use the same cell, such as a single tax rate in F1. Write the reference as $F$1:

=B2*$F$1

Copy that down and B2 becomes B3, B4 and so on, while $F$1 stays put. The dollar signs lock the column and the row.

Mixed references

Types of cell reference

ReferenceTypeWhat happens when copied
A1RelativeColumn and row both change
$A$1AbsoluteNothing changes
$A1MixedColumn is locked, row changes
A$1MixedRow is locked, column changes

The F4 shortcut

While editing a formula, click on or next to a reference and press F4. Each press cycles from A1 to $A$1 to A$1 to $A1 and back. On a Mac, use Cmd+T. On many laptops you may need to hold Fn as well. You can also type the dollar signs yourself.

A worked example

A multiplication table is the classic use of mixed references. Put 1 to 9 down column A and 1 to 9 across row 1, then in B2 write:

=$A2*B$1

Copy it across and down. The column is locked to A and the row is locked to 1, so every cell multiplies the right pair. For more practical formulas to try, see Excel formulas for beginners.

Frequently asked questions

What does the dollar sign mean in an Excel formula?

It locks the part of the reference that follows it, so that part does not change when you copy the formula.

How do I make a reference absolute on a Mac?

Click the reference in the formula bar and press Cmd+T. On many Macs, Fn+F4 also works. You can always type the dollar signs by hand.

Why did my copied formula give the wrong answer?

Usually a reference that should be locked was relative, so it moved when you copied it. Lock the cell that must stay fixed.

What is a mixed reference?

A reference that locks only the column ($A1) or only the row (A$1).

Sources and disclaimers

ExcelPanda is not affiliated with or endorsed by Microsoft. Microsoft and Excel are trademarks of the Microsoft group of companies.