If you have ever wondered what is $ in Excel formula notation, the short version is that the dollar sign locks part of a cell reference so it does not move when you copy the formula elsewhere. It has nothing to do with money or currency formatting. Instead, the $ is Excel’s way of telling a reference to stay put, and mastering it is the single biggest step from copying formulas that break to copying formulas that just work.
In this guide you will learn exactly how the dollar sign changes a reference, the real difference between absolute and relative references, and how the F4 shortcut key adds and removes the $ for you. Every example uses concrete cell addresses like =A1*$B$1 so you can type them into a blank spreadsheet and see the result for yourself.
Relative References: The Default Behavior
By default, Excel uses relative references. A reference like A1 has no dollar signs, so Excel treats it as a direction rather than a fixed spot. When you copy the formula down or across, the reference shifts by the same number of rows and columns.
Say cell C1 contains =A1*2. If you copy C1 down to C2, Excel automatically rewrites it as =A2*2. Copy it to C3 and it becomes =A3*2. This is exactly what you want when every row does the same calculation on its own data, such as multiplying each item’s quantity by two.
Absolute References: Locking a Cell With $
An absolute reference uses two dollar signs, one before the column letter and one before the row number, like $B$1. Both parts are locked, so the reference always points to B1 no matter where you copy the formula.
Imagine B1 holds a tax rate of 0.08. In cell C2 you type =A2*$B$1 to calculate tax on the amount in A2. When you copy that formula down to C3, it becomes =A3*$B$1. Notice that A2 changed to A3 (relative) but $B$1 stayed exactly the same (absolute). Every row now multiplies its own amount by the one shared tax rate in B1. Without the dollar signs, copying to C3 would give =A3*B2, and B2 is empty, so the math would break.
Mixed References: Locking Just One Part
Sometimes you want to lock only the column or only the row. That is a mixed reference, and it uses a single dollar sign.
- $B1 locks the column (B) but lets the row change. Copy it across and it stays in column B; copy it down and the row number increases.
- B$1 locks the row (1) but lets the column change. Copy it down and it stays in row 1; copy it across and the column letter advances.
Mixed references shine in multiplication tables. If you put =$A2*B$1 in the top-left cell of a grid and copy it across and down, every cell multiplies its row header in column A by its column header in row 1, producing a full times table with a single formula.
How to Add the $ Sign Step by Step
- Click the cell where you want your formula and type an equals sign to start.
- Begin the formula, for example type =A1*B1, but do not press Enter yet.
- Click inside the reference you want to lock, such as the B1 part of the formula in the formula bar.
- Press the F4 key. Excel instantly turns B1 into $B$1, adding both dollar signs at once.
- Press F4 again to cycle through the options: $B$1 (both locked), B$1 (row locked), $B1 (column locked), then back to B1 (nothing locked).
- Press Enter to finish, then copy the cell down or across and watch the locked parts stay in place.
- Check the results by clicking a copied cell and reading its formula in the formula bar to confirm the references behaved as expected.
Each row or column should calculate on its own neighboring data, like =A2*C2 copied down a price column. The reference should move with the formula.
Every formula should point to one fixed cell, like a single tax rate, exchange rate, or grand total. Lock it with $ so copying never breaks the link.
Dollar Sign Reference Cheat Sheet
| Reference | Type | What is locked | Example use |
|---|---|---|---|
| A1 | Relative | Nothing | =A1*2 copied down each row |
| $A$1 | Absolute | Column and row | =B2*$A$1 for a fixed rate |
| $A1 | Mixed | Column only | Lock column A across a grid |
| A$1 | Mixed | Row only | Lock row 1 down a grid |
For more spreadsheet walkthroughs and shortcut tricks, explore our library of how-to guides.
Frequently Asked Questions
Does the $ sign format cells as currency?
No. Inside a formula the $ only locks references. To show numbers as dollars, select the cells and apply the Currency format from the Home tab or press Ctrl+Shift+4. The two uses of $ are completely separate.
What is the difference between $B$1 and B1?
B1 is relative and changes when you copy the formula. $B$1 is absolute and always points to cell B1 no matter where the formula is copied. Use $B$1 for a value that should never shift, like a shared rate.
What does F4 do in an Excel formula?
While editing a reference, pressing F4 cycles through the four lock states: $B$1, B$1, $B1, and B1. It is the fastest way to add or remove dollar signs without typing them manually.
Can I use dollar signs with a range like A1:A10?
Yes. You can lock a whole range as $A$1:$A$10, which is common in functions like SUM or VLOOKUP so the lookup table stays fixed when the formula is copied to other cells.
Why does my formula break when I copy it?
It usually breaks because a reference that should be fixed was left relative, so it shifted during the copy. Add dollar signs to the cell that must stay constant, then copy again and the results will stay correct.
