Absolute Reference in Excel: How $A$1 Works
The dollar sign tells Excel to keep a reference fixed when you copy a formula. Once you see it work, it takes a minute to learn.
Relative, absolute and mixed
| Type | Looks like | When copied |
|---|---|---|
| Relative | A1 | Row and column both change |
| Absolute | $A$1 | Row and column stay fixed |
| Mixed, row fixed | A$1 | Row stays, column changes |
| Mixed, column fixed | $A1 | Column stays, row changes |
The F4 shortcut
While editing a formula, click on a reference and press F4. It cycles through $A$1, A$1, $A1 and back to A1. On some laptops use Fn+F4.
Example: a tax rate in one cell
Put the rate 8% in E1 and prices in B2:B4 (50, 20, 80). In C2 write =B2*$E$1 and copy down. Without the dollar signs the reference would slide to E2, E3 and break.
| Price (B) | Tax in C: =B2*$E$1 | Result |
|---|---|---|
| 50 | =B2*$E$1 | 4.00 |
| 20 | =B3*$E$1 | 1.60 |
| 80 | =B4*$E$1 | 6.40 |
Change E1 to 9% and every row updates at once. That is the point of keeping inputs in their own cells.
Example: mixed references in a table
For a multiplication table with 1 to 5 across row 1 and down column A, put =$A2*B$1 in B2 and copy it over the whole grid. Column A stays fixed while you copy right, and row 1 stays fixed while you copy down.
Common mistakes
- Forgetting the dollar sign on the cell that should stay fixed, so results drift when copied.
- Locking too much: $A$1 on a reference that should move.
- Moving a formula with Cut and Paste does not change its references, but Copy and Paste does.
Absolute references are essential in percentage formulas, VLOOKUP table ranges and SUMIF ranges you copy down.
Free pricing tips for your business
Get short, practical tips on pricing jobs and using our free calculators. No spam, unsubscribe any time.
Frequently asked questions
What does the dollar sign do in Excel?
It locks the column letter, the row number or both, so they do not change when you copy the formula.
How do I make a cell reference absolute?
Click the reference in the formula bar and press F4, or type the dollar signs yourself.
What is a mixed reference?
A reference where only the column or only the row is locked, such as $A1 or A$1.
Do named ranges act like absolute references?
Yes. A named range always points to the same cells, so it is a clean alternative to $A$1.
Need a custom Excel sheet or automation?
Talk to usMore free tools: All free tools · Percentage formulas · VLOOKUP guide · SUMIF and SUMIFS