Start for free

All Excel functions

Absolute references ($) in Excel: how it works

A $ locks part of a reference, so it doesn't shift when you fill a formula. $A$1 always stays A1. F4 switches between the forms.

Try the first exercises for freeIn the lesson Absolute and relative references

What does a $ do in a formula?

If you fill a formula down or to the right, Excel adjusts the references: =A2*B2 becomes =A3*B3 in the row below. That's called a relative reference, and usually it's exactly what you want.

If a formula always has to look at the same cell, such as a tax rate or a total, you lock that cell with dollar signs: $E$1. The dollar sign in front of the letter locks the column, and the dollar sign in front of the number locks the row.

When do you lock a cell?

  • A rate in one cell, such as the tax in E1: =A2*$E$1.
  • A total of which every row is a share: =B2/$B$10.
  • The table in VLOOKUP, so that it doesn't shift: =VLOOKUP(E2,$A$2:$C$50,3,FALSE).
  • A table you fill both to the right and down, such as a multiplication table: =$A2*B$1.

Step by step

B2 contains =A2*$E$1. A2 can shift along, but the rate stays in E1. Change the numbers or the formula and see what happens.

B2
A
B
C
D
E
F
G
H
1
Amount
Tax
Rate
20%
2
100
20
3
250
4
40
5
6
7
8
9
10
Ready

This is how to go about it:

  1. Click B2 and start the formula with the cell with the amount and a multiplication sign.=A2*
  2. Click the cell with the rate, E1.=A2*E1
  3. Press F4 straight away, or Cmd + T on a Mac. Excel puts dollar signs around it: $E$1.=A2*$E$1
  4. Press Enter and fill the formula down. A2 becomes A3 and A4, but $E$1 stays where it is.

More examples

A share of the total

C2 contains =B2/$B$5. Each region is divided by the total in B5. If you fill the formula down, B2 moves along, but $B$5 doesn't.

C2
A
B
C
D
E
F
G
H
1
Region
Revenue
2
Leeds
1610
54%
3
York
415
4
Bristol
980
5
Total
3005
6
7
8
9
10
Ready

A discount from one cell

B2 contains =A2*(1-$E$1). All the prices get the same discount from E1. Change E1 to 20%, and all the prices adjust.

B2
A
B
C
D
E
F
G
H
1
Price
After discount
Discount
15%
2
80
68
3
120
4
45
5
6
7
8
9
10
Ready

Locking only the row or the column

B2 contains =$A2*B$1. Column A and row 1 are locked. If you copy the formula to C3, it becomes =$A3*C$1: 3 times 10.

B2
A
B
C
D
E
F
G
H
1
5
10
15
2
2
10
3
3
4
4
5
6
7
8
9
10
Ready

The four forms

A1 shifts in both directions. $A1 locks the column, A$1 the row and $A$1 both.

Why a formula shifts

If you fill =A2*B2 down one row, Excel turns it into =A3*B3. That's usually just what you want. But if the tax rate is in one fixed cell, the formula looks one row lower at an empty cell and you get 0. That's what the $ is for.

Example: tax on a column of amounts

If the rate is in E1 and the amounts are in column A, write =A2*$E$1 and fill it down. A2 moves along to A3 and A4, while $E$1 stays where it is.

Locking only the row or the column

For a times table, you lock just one side. With =$A2*B$1, column A and row 1 stay fixed, so you can fill the formula both to the right and down.

Where it often goes wrong

  • If you forget the $ on a table in VLOOKUP, you get #N/A further down.
Want to try it yourself?
Practise this in the lesson Absolute and relative references. The first exercises are free.

Try the first exercises for free

Frequently asked questions

What's the shortcut?

F4 while editing the formula, and Cmd + T on a Mac.

Read next

Creating a formulaFilling and copying formulasOrder of operationsCalculating percentagesWorking with multiple sheets