Absolute references ($A$1) in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an absolute reference in Excel?

An absolute reference is a cell reference that does not change when you copy or move a formula. It uses dollar signs ($) before the column letter and row number, like $A$1.

beginner

How do you write an absolute reference for cell B3?

You write it as $B$3. The dollar signs lock both the column B and the row 3 so they stay fixed when copying formulas.

beginner

What happens if you copy a formula with a relative reference like A1 to another cell?

The reference changes relative to the new position. For example, copying a formula with A1 one cell down changes the reference to A2.

beginner

Why use absolute references in formulas?

Use absolute references to keep a specific cell fixed in formulas, like a tax rate or constant value, so it doesn’t change when copying the formula to other cells.

intermediate

What is the difference between $A1, A$1, and $A$1?

$A1 locks the column A only.<br>A$1 locks the row 1 only.<br>$A$1 locks both column A and row 1.

What does the formula reference $C$5 mean in Excel?

A The reference will change when copied.

B Only the column C is locked.

C The reference locks column C and row 5 when copied.

D Only the row 5 is locked.

$C$5 locks both the column and row, so the reference stays fixed when copying formulas.

If you copy a formula with reference A1 from cell B2 to C3, what will the reference become?

A A1

B B2

C B1

D A2

Copying a relative reference moves it relative to the new cell. From B2 to C3 moves one column right and one row down, so A1 becomes B2.

Which reference type should you use to keep the row fixed but allow the column to change?

A A1

B $A$1

C $A1

D A$1

A$1 locks the row 1 but allows the column to change when copying.

Why might you use an absolute reference in a formula that calculates sales tax?

A To keep the tax rate cell fixed when copying the formula.

B To make the tax rate change for each row.

C To avoid using formulas.

D To lock the entire worksheet.

Using an absolute reference for the tax rate cell keeps it fixed so the formula always uses the same tax rate.

What symbol is used to create an absolute reference in Excel?

A $

B &

C #

D *

The dollar sign $ is used to lock columns and/or rows in cell references.

Explain what an absolute reference is and why it is useful in Excel formulas.

Think about how formulas behave when copied across cells.

Describe the difference between relative, absolute, and mixed references with examples.

Use the dollar sign placement to explain locking.