Absolute references in Excel: what the dollar sign does
A dollar sign locks part of a cell reference so it stops moving when the formula is copied. $B$7 always means B7, however far the formula is filled down or across.
Syntax
=B2*(1+$B$7)
B7
Relative. Moves with the formula when copied.
$B$7
Absolute. Column and row both stay put.
B$7
The row is fixed, the column can move.
$B7
The column is fixed, the row can move.
When to use it
Whenever a formula has to point at one fixed cell, such as a tax rate, an exchange rate or a total, while the rest of it moves from row to row.
What trips people up
This is the most common reason a filled-down column goes wrong after the first row. If the numbers drift as you go down, look for a reference that should have been pinned. Pressing F4 on a reference cycles through the four forms.
With the cursor on a reference, F4 cycles it through $B$7, B$7, $B7 and back to B7. On a Mac, Cmd+T does the same.
When would I need B$7 rather than $B$7?
When one formula is copied both down and across, such as a rate grid or a multiplication table. One half of the reference has to stay on its row while the other follows its column.
Does a dollar sign change the answer?
Not in the cell where you type it: B7 and $B$7 give the same result there. The difference only appears once the formula is copied or filled to other cells.
Related functions
SUM — SUM adds up everything in the range you give it, ignoring any text and empty cells along the way.
SUMIFS — SUMIFS adds up the numbers in one column, but only on the rows that satisfy every condition you give it.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
