Relative & Absolute References

The real power of a formula shows when you copy it. Write a formula once, drag it down 500 rows, and Excel adjusts it for every row. That adjustment is helpful most of the time, but it is also the cause of the most common Excel mistake: a copied formula that quietly points at the wrong cell.

In this lesson you will learn how relative references change when copied, how the $ sign locks a reference, how mixed references build a whole table from one formula, and how to refer to other sheets and to named cells.

Relative references

A normal reference such as B2 is relative. Excel remembers it as a position: "the cell two columns to my left on the same row". When you copy the formula, the position stays the same and the address changes.

Write this in D2 and drag the fill handle down to D4:

=B2*C2

Each row multiplies its own price and quantity. This is exactly what you want for a column of row-by-row calculations.

When relative references go wrong

Priya manages a sales team in Pune. Each person earns a commission at the rate stored once in F1:

She writes =B2*F1 in C2. It gives 2100, which is right. Then she copies it down:

The rate reference moved down too. F2 and F3 are empty, and Excel treats an empty cell as 0, so Kabir and Isha get nothing. There is no error message, which makes this mistake dangerous.

Absolute references with $

A dollar sign in front of the column letter or row number locks it. $F$1 is an absolute reference: it always points at F1, wherever you copy the formula.

=B2*$F$1

The sales reference still moves with each row, and the rate stays fixed. The $ has nothing to do with dollars or rupees; it simply means "do not change this part".

You do not need to type the dollar signs. Click inside the reference in the formula bar (or while typing the formula) and press F4. Each press cycles through the four types:

On a Mac, the shortcut is Cmd+T, or Fn+F4 on some keyboards.

Mixed references: one formula, a whole table

A mixed reference locks only the column or only the row. It is perfect for a grid where each cell combines a row heading with a column heading. Rohan wants a price list showing three discount levels for each product:

Type one formula in C3:

=$B3*(1-C$2)

Copy it across to E3 and down to row 5. $B3 locks column B, so every cell uses the price from its own row. C$2 locks row 2, so every cell uses the discount from its own column.

Row 3: 1140    1080    1020
Row 4: 807.5   765     722.5
Row 5: 2374.05 2249.1  2124.15

Nine answers from one formula. Ask yourself two questions whenever you build a grid: "When I copy across, should this column change?" and "When I copy down, should this row change?" Lock whatever should not change.

References to other sheets

To use a cell on another sheet, put the sheet name and ! before the address. If the name has spaces, Excel wraps it in single quotes:

=Rates!B2*'Oct Sales'!C5

When several sheets have the same layout, such as one sheet per month, a 3D reference adds the same cell across a run of sheets. This totals D10 on every sheet from Apr to Jun, including any sheets placed between them:

=SUM(Apr:Jun!D10)

Named cells

Instead of $F$1, you can give the cell a name. Select F1, click the Name Box (left of the formula bar), type CommissionRate and press Enter. Now the formula reads like a sentence:

=B2*CommissionRate

A name always points at the same cell, so it behaves like an absolute reference. Names cannot contain spaces and must not look like a cell address (GST18 is a cell in column GST, so it is not allowed; GST_Rate is fine). Manage, edit or delete names in Formulas > Name Manager.

Common mistakes

• Copying a formula that uses a single rate cell without $. The result is 0 or a wrong value, with no error shown. Lock the rate: $F$1.
• Locking everything. =$B$2*$C$2 copied down gives the same answer in every row. Lock only what must stay fixed.
• Mixing up F$1 and $F1. The $ sits in front of the part it locks: before the letter for the column, before the number for the row.
• Deleting a cell that formulas use. Those formulas show #REF!. Check before deleting.

Key points

• Relative references (B2) shift when a formula is copied; this suits row-by-row calculations.
• Absolute references ($F$1) never shift; use them for single values such as rates.
• Mixed references ($B3, C$2) lock only the column or only the row, which builds grids from one formula.
• Press F4 to cycle through the four reference types.
• Use Sheet!Cell for other sheets, Apr:Jun!D10 for 3D totals, and names such as CommissionRate for readability.

=$A2*B$1

B2: 1   (1 × 1)
K2: 10  (1 × 10)
B11: 10 (10 × 1)
K11: 100 (10 × 10)
F6: 25  (5 × 5)