
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$1Copy 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
| Reference | Type | What happens when copied |
|---|---|---|
| A1 | Relative | Column and row both change |
| $A$1 | Absolute | Nothing changes |
| $A1 | Mixed | Column is locked, row changes |
| A$1 | Mixed | Row 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$1Copy 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.
.png)