Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 93 ยท Formulas Fundamentals

Cell References: Relative, Absolute, Mixed

Understand Excel cell references: relative, absolute and mixed references, the F4 key, how formulas change when copied, and the R1C1 style.

A formula becomes powerful when you write it once and copy it to hundreds of cells. For that to work, Excel has to decide what a reference like B2 means in the new location. The answer depends on one small symbol: the dollar sign. Understanding it is the single most important skill in Excel formulas, and the cause of a large share of spreadsheet bugs.

In this lesson you will learn the three kinds of reference (relative, absolute and mixed), how the F4 key switches between them, what changes when you copy versus move a cell, and how the alternative R1C1 notation reveals what Excel is really storing. The syntax shown in code blocks is real Excel. The live editors are stand-ins: a small engine that copies formulas the same way Excel does.

Three kinds of reference

Relative B2 shifts when you copy.
Absolute $B$2 never moves.
Mixed $B2 or B$2 pins one part.

The $ sign means "do not change this part".

1 / 2

A1 style and what a reference means

Every cell has an address made of its column letter and row number: B2 is column B, row 2. When you write =B2*C2, you are not saying "multiply the cells at these fixed spots". You are saying "multiply the cell one column to the left of me, same row, by the cell in the same row, in this column". Excel remembers the references as offsets from the formula's own cell. That is why a copied formula adapts.

Relative references

A plain reference such as B2 is relative. Suppose C2 holds =A2*B2. Copy it down to C3 and Excel writes =A3*B3. Copy it right to D2 and it becomes =B2*C2. Each reference moved by the same distance as the formula did.

This is exactly what you want when every row is calculated the same way: row 3 should use row 3's data, row 4 should use row 4's, and so on. You write the formula once, double-click the fill handle (the small square at the bottom-right corner of the cell) and Excel fills it down as far as the neighboring column has data.

Absolute references

Sometimes one input must stay put no matter where the formula goes: a tax rate, an exchange rate, a target. Putting a $ before both the column letter and the row number makes the reference absolute. $B$1 always means cell B1, wherever the formula is copied.

TEXT
Tax rate in B1.   Prices in B3:B5.

C3:  =B3*(1+$B$1)     โ† copy this down to C4 and C5
C4:  =B4*(1+$B$1)
C5:  =B5*(1+$B$1)

The price reference B3 is relative so each row looks at its own price, and the tax rate $B$1 is absolute so every row uses the same rate. If you forget the dollar signs and copy =B3*(1+B1) down, the second formula becomes =B4*(1+B2), which points at an empty cell. Nothing warns you, and the numbers are quietly wrong.

Seeing it happen

The engine below lets you copy a formula the way Excel does. It holds a small price list. Column C uses an absolute tax-rate reference; column D makes the classic mistake and uses a relative one. Read the two columns side by side.

JS Copying formulas: absolute versus relative

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In the first block, column C gives 12, 30 and 48, while column D drifts: row 3 is right, but rows 4 and 5 multiply by the wrong cells. The second block shows why mixed references exist.

Mixed references

A mixed reference locks only one part. $A2 locks the column but lets the row move. B$1 locks the row but lets the column move. They shine when you fill a formula in two directions at once, as in the multiplication table above:

  • $A2 keeps looking at column A (the row labels) but follows the row.
  • B$1 keeps looking at row 1 (the column labels) but follows the column.

Look at E5 in the output: the formula there is =$A5*E$1. A single formula typed in B2 filled the whole block correctly. Without mixed references you would need to retype or edit every formula.

The F4 shortcut

You do not have to type dollar signs. While editing a formula, click inside a reference (or select it) and press F4. Each press cycles through the four forms:

TEXT
B2  โ†’  $B$2  โ†’  B$2  โ†’  $B2  โ†’  B2

On a Mac or a laptop where function keys do other things, try Fn+F4, or Cmd+T on Mac. If F4 seems to do nothing, make sure the cursor is on the reference.

When you build a formula and are unsure which kind of reference you need, ask: "when I copy this across, should this part follow me or stay?" Follow means relative, stay means absolute, and "stay in one direction only" means mixed.

Copy versus move

Copying and moving are not the same.

  • Copy and paste (or fill) adjusts relative references, as described above.
  • Cut and paste (or dragging a cell by its border) moves the cell and keeps its references pointing at the same cells. A formula =A1+B1 cut from C1 and pasted into E5 is still =A1+B1.
  • If you move a cell that other formulas refer to, those formulas follow it to its new home automatically.
  • Inserting or deleting rows and columns adjusts references as well. Deleting a row that a formula refers to leaves #REF!; see Formula Errors and How to Fix Them.

R1C1: what Excel really stores

Excel can display references in a second notation. In R1C1 style (File โ†’ Options โ†’ Formulas โ†’ "R1C1 reference style") every reference is written as a row and column: R for row and C for column. An absolute position is a number (R5C2 is row 5, column 2, which is $B$5). A relative position is an offset in square brackets (R[-1]C is "one row up, same column"). A bare R or C means "same row" or "same column".

Why would anyone use it? Because copied formulas look identical in R1C1. If one cell in a column looks different from its neighbors, it is probably a mistake. The second stand-in converts A1 formulas to R1C1 and shows the effect.

JS The same formula in A1 and R1C1

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The first loop prints four different A1 formulas, but the R1C1 column is the same text on every row: =RC[-1]*(1+R1C2). That equality is how auditors check that a column of formulas is consistent. The second block does the same for the mixed formula: although the A1 text differs in every cell, the R1C1 form is always =RC1*R1C ("same row, column 1" times "row 1, same column").

Referring to other sheets

A reference can point to another worksheet by adding the sheet name and an exclamation mark: =Sheet2!B2. If the name contains a space or special character, wrap it in single quotes: ='Sales 2024'!B2. The same relative and absolute rules apply to the part after the !. Referring to a range across several sheets is covered in Ranges and Named Ranges.

Common mistakes

  • Forgetting $ on a constant so the reference drifts down the column. Look for results that become wrong only after the first row.
  • Locking too much: using $B$2 where B$2 was needed makes a two-direction fill return the same value everywhere.
  • Typing numbers instead of referring to a cell, which makes the model hard to update.
  • Pasting values over a formula in the middle of a column; use R1C1 or Ctrl+\ (select cells that differ from the active cell in a row) to spot odd ones out.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Cell C2 contains =A2*B2. You copy it to C3. What does C3 contain?

    Both references are relative, so they move down by one row, just as the formula did.
  2. 2. You need the formula in every row to use the tax rate in B1. Which reference should you write for it?

    $B$1 is absolute: neither the column nor the row changes when you copy. B1 would drift down the column, and the mixed forms lock only one part.
  3. 3. Which key cycles a reference between B2, $B$2, B$2 and $B2 while you edit a formula?

    F4 cycles the dollar signs on the reference under the cursor. F2 only switches into edit mode.
  4. 4. You cut cell C1 (formula =A1+B1) and paste it into E5. What does E5 contain?

    Cut and paste moves the cell and keeps its references pointing to the same cells. Only copy and paste shifts relative references.
  5. 5. What is true about a column of correctly copied formulas when you view them in R1C1 style?

    R1C1 writes relative references as offsets, so formulas copied from one another read the same. A cell that looks different is worth checking.

Summary

  • A relative reference (B2) shifts when a formula is copied; an absolute reference ($B$2) does not; a mixed reference ($B2, B$2) locks the column or the row only.
  • Press F4 while editing to cycle through the four forms.
  • Use absolute references for constants such as rates; use mixed references for tables filled in two directions.
  • Copy and paste adjusts relative references; cut and paste (move) does not.
  • In R1C1 style, consistent formulas look the same, so mistakes stand out.
  • Put another sheet's name before ! to refer to it, with quotes if the name has spaces.

Next: Ranges and Named Ranges.