Absolute and Relative References
Excel Guides / Shortcuts & Formatting
Shortcuts & Formatting
What the dollar signs do, how F4 adds them, and which kind to use where.
Updated 12 Oct 2026 · 5 min read · By Sky Dashboards
Every formula you copy in Excel rewrites itself. Usually that is exactly what you want, until a column of results quietly points at the wrong cells. Dollar signs control which parts of a reference move when you copy. Get them right and one formula can fill a whole table.
In this guide
Relative references: the default
Type =B2*C2 in D2 and copy it down to D3. Excel does not copy the text, it copies the relationship: 'two cells to the left times one cell to the left'. In D3 that becomes =B3*C3. Copy it one column right instead and it becomes =C2*D2.
That is why a row-by-row calculation needs only one formula. The trouble starts when part of the formula should not move, such as a tax rate or a target that sits in a single cell.
The four reference types
A dollar sign locks whatever comes straight after it: the column letter, the row number, or both.
| Reference | Type | Copied down a row | Copied right a column |
|---|---|---|---|
A1 |
Relative | Becomes A2
|
Becomes B1
|
$A$1 |
Absolute | Stays $A$1
|
Stays $A$1
|
A$1 |
Mixed, row locked | Stays A$1
|
Becomes B$1
|
$A1 |
Mixed, column locked | Becomes $A2
|
Stays $A1
|
Dollar signs only affect copying
The $ has nothing to do with currency. It only changes what happens when you copy or fill. If you insert or delete rows, Excel still updates $A$1 so it points at the same cell as before. Cutting and pasting a formula also leaves its references alone, whatever the dollar signs say.
Add dollar signs with F4
Typing dollar signs by hand is slow and easy to get wrong. While editing a formula, put the cursor in a reference (or just after it) and press F4. Each press moves on one step: A1 becomes $A$1, then A$1, then $A1, then back to A1.
⌨ Cycle absolute and relative references
WindowsF4
MacCmd + T
On many laptops you need Fn + F4. To change several references at once, select that part of the formula in the formula bar and press F4.
Tip
F4 works on ranges too. With the cursor in B2:B50, one press gives $B$2:$B$50.
Lock an input cell
A coffee roaster lists prices before tax and keeps the tax rate in one cell, E2. The first formula is fine. Copy it down without dollar signs and the rest go wrong without a word of warning:
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Product | Price | Inc. tax | Tax rate | ||
| 2 | Pour-over kit | $45.00 | $49.50 | 10% | ||
| 3 | Beans 250g | $18.00 | $18.00 | |||
| 4 | Ceramic cup | $24.00 | $24.00 |
C2 holds =B2*(1+E2). Copied down, the tax reference slid to the empty cells E3 and E4, so no tax was added. There is no error to warn you.
Lock the tax rate as $E$2 and every row points at the same cell:
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Product | Price | Inc. tax | Tax rate | ||
| 2 | Pour-over kit | $45.00 | $49.50 | 10% | ||
| 3 | Beans 250g | $18.00 | $19.80 | |||
| 4 | Ceramic cup | $24.00 | $26.40 |
Only the price reference moves. $E$2 stays fixed, so changing the rate in E2 updates every row.
Named cells are absolute already
Click E2, type TaxRate in the Name Box to the left of the formula bar and press Enter. Now =B2*(1+TaxRate) copies safely and reads better.
Mixed references: one formula for a grid
Mixed references earn their keep when a formula reads a label from the left and a header from the top, like a times table. A yoga studio wants a price grid for class packs: pack size down column A, price per class across row 1.
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | Classes | $18 | $20 | $22 | |
| 2 | 5 | $90 | $100 | $110 | |
| 3 | 10 | $180 | $200 | $220 | |
| 4 | 20 | $360 | $400 | $440 |
B2 holds =$A2*B$1, filled right and down. In D4 it has become =$A4*D$1: 20 classes at $22 is $440.
$A2 locks the column, so every copy still reads the pack size from column A. B$1 locks the row, so every copy still reads the price from row 1. Write it once in B2, fill right, fill down, done. The same pattern drives monthly summary grids, where a SUMIFS reads the product from column A and the month from the header row.
Which type to use where
-
Relative (
A1) for row-by-row maths in a log: units times price, revenue minus cost. Most formulas in a data sheet are fully relative. -
Absolute (
$A$1) for single inputs such as a tax rate, target or exchange rate, and for fixed ranges inside lookups, for example=SUMIFS($D$2:$D$500,$B$2:$B$500,G2). -
Row locked (
A$1) for headers across the top, such as months or price points. -
Column locked (
$A1) for labels down the side, such as product names or pack sizes.
Watch out
In that SUMIFS example, the criteria cell G2 stays relative so each row looks up its own product. Locking it as $G$2 is a common slip: every row then returns the same total.
References in Excel Tables
Inside an Excel Table, formulas use column names instead of cell addresses. =[@Price]*(1+TaxRate) means 'this row's Price', and Excel fills it down the whole column for you, so the relative or absolute question mostly goes away.
One quirk: a whole-column reference such as tblSales[Price] does not change when you copy and paste it, but it shifts to the next column when you drag it sideways with the fill handle. To pin it, repeat the column name as a range: tblSales[[Price]:[Price]].
Frequently asked questions
What does the $ sign mean in an Excel formula?
It locks the part of the reference that follows it. $A$1 locks both the column and the row, A$1 locks only the row and $A1 locks only the column. It has nothing to do with currency.
What is the shortcut for an absolute reference?
F4 on Windows, or Fn+F4 on many laptops. On a Mac, press Cmd+T. Each press moves to the next reference type.
Why does F4 not add dollar signs?
F4 only toggles references while you are editing a formula. Otherwise it repeats your last action. Press F2 first, put the cursor in the reference, then press F4.
Do absolute references break if I insert rows?
No. Dollar signs only affect copying and filling. When you insert or delete rows or columns, Excel updates every reference, absolute or not, so it still points at the same cell.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates →