Absolute references ($)
An absolute reference (`$E$2`) always points to the same cell, wherever the formula is copied.
The problem
We want to apply the 10% discount in E2 to every price. Type =B2*(1-E2) in C2 and fill down, and C3 refers to E3 — it’s empty, so the result is wrong.
The fix — the $ sign
Write =B2*(1-$E$2). $ locks the column and row: filling down turns B2 into B3, but $E$2 stays put.
F4 and mixed references
While typing, put the cursor on a reference and press F4 to add $ automatically. Press again: $E$2 → E$2 (row locked) → $E2 (column locked) → E2.
Example
Formula:
=B2*(1-$E$2)→ result: 3600
Tips
- Always use an absolute reference for a single settings cell — exchange rate, tax rate, discount.
- Mixed references are useful in formulas filled both ways, such as a multiplication table.
Try it in the simulator
Type =B2*(1-$E$2) into C2 and fill it down to C5. Then change the discount in E2 to 0.2.
Книга1 - Excel
—☐✕
Буфер обмена
Calibri ▾11 ▾
Шрифт
Выравнивание
Общий ▾
Число
Стили
Ячейки
Редактирование
✕✓fx
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Discounted price | Discount | ||||||||||||||||||||
| 2 | Bread | 4000 | 0,1 | |||||||||||||||||||||
| 3 | Milk | 12000 | ||||||||||||||||||||||
| 4 | Eggs | 1500 | ||||||||||||||||||||||
| 5 | Tea | 25000 | ||||||||||||||||||||||
| 6 | ||||||||||||||||||||||||
| 7 | ||||||||||||||||||||||||
| 8 | ||||||||||||||||||||||||
| 9 | ||||||||||||||||||||||||
| 10 |
Лист1
Готово
−+100%
Test your knowledge
Do graded exercises on these topics in the “Practice” section and earn coins.
Go to exercises