Relative and Absolute References in Excel: When to Use the Dollar Sign
On this page
A formula can work perfectly in the first row and fail as soon as you copy it. Relative and absolute references in Excel explain why. The dollar sign is not a currency symbol inside a reference. It tells Excel which part of the address should stay fixed.
Before you start: Use a new practice workbook. The names and figures below are fictional. Enter the headers in row 1 and the first record in row 2 unless a step says otherwise. Formulas use English function names and commas; your regional settings may require semicolons instead. Save a copy before experimenting.
Create a price and discount sheet
Enter Discount rate in A1 and 10% in B1. Put Product in A3, Price in B3 and Discounted price in C3. Then enter these records in rows 4 through 6:
| Product | Price |
|---|---|
| Notebook | 120 |
| Pen | 25 |
| Folder | 80 |
B1 should contain the numeric percentage 10%, which Excel stores as 0.1. It should not contain the text ten percent. Each product has its own price, but all products share the one discount rate.
See how a relative reference moves
If you write =B4*(1-B1) in C4, it calculates the correct first answer, 108. But copying it down one row changes the references to =B5*(1-B2). B2 is not the discount rate. In our sheet it is blank, so the copied formula can return a plausible but wrong price.
The references move because they are relative. Excel preserves the same positional relationship between the formula cell and the cells it refers to. That is useful for B4, because the next product's price is in B5. It is unhelpful for the shared setting in B1.
Lock the shared input with an absolute reference
Replace the formula in C4 with =B4*(1-$B$1). Copy C4 into C5:C6. The price reference moves to B5 and B6, but $B$1 stays exactly the same.
Your results should be Notebook 108, Pen 22.5 and Folder 72. Displaying two decimal places can make the pricing column easier to read, but the number format does not itself round the stored calculation.
Now change B1 to 20%. The results should become 96, 20 and 64. This is the advantage of keeping one shared input in a clearly labelled cell. You can test a new scenario without rewriting three formulas.
Learn the four reference forms
| Reference | What stays fixed when copied |
|---|---|
| B1 | Neither the column nor the row |
| $B$1 | Both column B and row 1 |
| $B1 | Column B only |
| B$1 | Row 1 only |
The last two are mixed references. They are helpful when a formula will be copied across columns as well as down rows. The important question is not “where should I put dollar signs?” but “which input should move in each direction?”
Build a small multiplication grid
On a second sheet, enter 2, 3 and 4 in B1:D1. Enter 10 and 20 in A2:A3. In B2, use =$A2*B$1. Copy it across to D2 and then down through row 3.
$A2 always looks in column A while allowing the row to change. B$1 always looks in row 1 while allowing the column to change. The first result row is 20, 30 and 40. The second is 40, 60 and 80. This small grid is a useful way to understand mixed references without a complicated business example.
Check the first, middle and last formula
After filling a range, inspect the formula bar in several cells. A wrong fixed reference often creates repeated values that still look reasonable. Check the meaning of each address, not just whether Excel shows an error.
Avoid locking every reference automatically. If you use $B$4 for each product's price, all rows will use the Notebook price. Absolute references solve a specific copying problem; they do not make every formula safer.
Try it yourself
Return to the price sheet. Put Service rate in D1 and 5% in E1. In D4 calculate the original price plus that service rate. Copy down. Which reference should be fixed, and what should the three answers be?
Check your answer
Use =B4*(1+$E$1) in D4 and copy it down. B4 should move to each product's price. $E$1 should remain fixed because all products share the same service rate.
The results are 126, 26.25 and 84. This exercise applies the service rate to the original price, not the discounted price. If you use C4 instead of B4, you are calculating a different scenario.
Keep learning: Return to the Excel learning path to choose your next lesson.