Absolute vs relative references in Excel: when to use $
A relative reference like B2 shifts when you copy a formula. An absolute reference like $B$2 stays fixed. Use $ for anything every row should point to, like a total, a rate or a lookup table.
The examples use this small sales table, the same one in the practice box below.
| A Rep | B Sales | D Target | E Region | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | 1,000 | East |
| 3 | Luis | 830 | 1,000 | West |
| 4 | Priya | 2,100 | 1,000 | East |
| 5 | Marcus | 475 | 1,000 | North |
| 6 | Jen | 990 | 1,000 | West |
| 7 | Omar | 1,600 | 1,000 | East |
The four kinds of reference
| Reference | Copy down | Copy across |
|---|---|---|
B2 | Row changes | Column changes |
$B$2 | Stays B2 | Stays B2 |
$B2 | Row changes | Column stays B |
B$2 | Row stays 2 | Column changes |
Example: share of the total
To show each rep's share of team sales, put this in C2: =B2/SUM($B$2:$B$7). For Dana that's 17.3% once you format it as a percent.
Copy it down to C3 and Excel writes =B3/SUM($B$2:$B$7). The B3 moved, which is what you want. The total range stayed put.
Without the $ signs, C3 would become =B3/SUM(B3:B8). That divides by the wrong total, and every row below gets worse.
The F4 shortcut
In Excel, click inside a reference in the formula bar and press F4. It cycles through B2, $B$2, B$2 and $B2. On a Mac, use Command + T. In Google Sheets, it's also F4.
Where tests check this
Try it: Dana's share of total
In C2, show Dana's sale as a fraction of total team sales. Write it so it could be copied down to C3 through C7.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
What does the $ sign mean in Excel?
It locks the column letter or row number that follows it, so it doesn't change when the formula is copied.
When should I use a mixed reference like B$2?
When you copy a formula both down and across, like a multiplication table, and need one part to stay fixed in only one direction.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How VLOOKUP works in Excel, why you should always end it with FALSE, how to lock the table with $, its limits, and a live exercise.
How to use SUM in Excel with a real example, the mistakes that cost points on Excel tests, and a live exercise to practice.
A one-page cheat sheet of the Excel formulas that show up on pre-employment tests, with the syntax, a real example and the mistake to avoid for each.