An absolute reference keeps a cell fixed when you copy a formula. In =B2*$E$1, the dollar signs lock E1: fill the formula down and every row still multiplies by E1, while B2 moves to B3, B4 and so on. To add the dollar signs, click the reference in the formula and press F4 (on a Mac, Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
C2 was typed once and filled down. Click C4: its formula is =B4*$E$1. The sales cell moved to row 4, the rate stayed in E1. Change the rate in E1 to 8% and every commission updates.
Relative vs absolute references
| Reference | Name | Copied one row down and one column right |
|---|---|---|
A1 | relative | B2 |
$A$1 | absolute | $A$1 |
A$1 | mixed: row locked | B$1 |
$A1 | mixed: column locked | $A2 |
A plain reference like B2 is relative: Excel stores it as "the cell in this position from me", so a copy one row lower points one row lower. That is exactly what you want for per-row data, and it is the default. The $ in front of a column letter or a row number locks that part.
The classic mistake: filling down without $
Here is the commission sheet again, with =B2*E1 in C2 and no dollar signs. The first row is right. The rest are 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
C3 holds =B3*E2: the rate reference moved down to E2, which is empty, and an empty cell counts as 0. Fix it here: click C2, change the formula to =B2*$E$1 and press Enter. The whole column follows, because C3:C6 are copies of C2. When the fixed cell is a divisor, as in =B2/B7 for a share of a total, the same mistake shows #DIV/0! instead of 0 (percentage of total is the usual case).
Press F4 to add the dollar signs
While typing or editing a formula, put the cursor in a reference (or just after it) and press F4. Each press moves to the next form:
E1 -> $E$1 -> E$1 -> $E1 -> E1
On many laptops F4 controls the screen or the sound, so press Fn+F4. On a Mac, use Cmd+T, or Fn+F4. You can also type the $ signs yourself.
Mixed references: lock only the row or the column
A mixed reference has one dollar sign. $A2 always reads column A but lets the row move; B$1 always reads row 1 but lets the column move. With both in one formula, a single formula filled across a grid builds a multiplication table:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
B2 holds =$A2*B$1. Click F6: it holds =$A6*F$1, the row number from column A times the column number from row 1, so it shows 25. Remove one dollar sign in B2 and the table falls apart, because the copies start multiplying neighboring cells instead of headers.
The same pattern prices a list at several discounts: =$A2*(1-B$1) with prices down column A and discount rates across row 1.
Running total with a half-locked range
A range can be locked at one end only. =SUM($B$2:B2) starts at B2 forever, while its end moves down as the formula is filled, so each row adds everything up to itself:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
C6 holds =SUM($B$2:B6) and shows 2230, the total of all five months. The same half-locked range makes =COUNTIF($A$2:A2,A2) count how many times a value has appeared so far, which is how duplicates after the first are flagged.
Practice: one formula for the whole table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Your turn: In B2, write the price of the first product after the discount in B1. Use $ so that the same formula, filled across and down to D5, gives every price in the table.
The sheet copies your formula into every cell of B2:D5, the way the fill handle would, and Check reads all twelve results. Without the right dollar signs, the copies in row 3 or column C read the wrong price or the wrong discount.
Absolute references to another sheet or a lookup table
The dollar signs work the same way with a sheet name: =B2*Settings!$B$1. They matter most in lookups, where the table must stay put while the lookup value moves: =VLOOKUP(A2,$E$2:$F$10,2,FALSE) filled down keeps searching E2:F10, while =VLOOKUP(A2,E2:F10,2,FALSE) slides the table down one row per copy and starts missing the first rows (VLOOKUP). If a fixed cell is used in many formulas, you can also give it a name with Formulas > Define Name and write =B2*Rate; a name defined this way points at the same cell from every formula, like $E$1.
Frequently Asked Questions
What does the $ sign mean in an Excel formula?
It locks the part of the reference that follows it. In $E$1 both the column E and the row 1 are locked, so the reference stays E1 wherever the formula is copied. E$1 locks only the row and $E1 only the column.
What is the shortcut for absolute reference in Excel?
Click inside the reference while editing the formula and press F4 (Fn+F4 on many laptops). Each press cycles through $A$1, A$1, $A1 and A1. On a Mac, press Cmd+T, or Fn+F4.
What is the difference between relative and absolute references?
A relative reference such as B2 moves when you copy the formula: one row down it becomes B3. An absolute reference such as $B$2 stays $B$2. Use absolute references for a single cell every row needs, like a rate or a total.
What is a mixed reference in Excel?
A reference with one dollar sign: $A2 keeps the column and lets the row move, B$1 keeps the row and lets the column move. =$A2*B$1 filled across and down a grid builds a multiplication table.
Why does my formula show 0 or #DIV/0! after I drag it down?
A reference that should stay fixed moved with the copy. If row 2 has =B2/B7, row 3 gets =B3/B8, and B8 is empty. Lock the total with =B2/$B$7 and fill again.
