Absolute references ($) in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does an absolute reference in Google Sheets look like?

An absolute reference uses the $ sign before the column letter and/or row number, like $A$1. This keeps the reference fixed when copying formulas.

beginner

Why use $A$1 instead of A1 in a formula?

Using $A$1 locks the reference to cell A1, so when you copy the formula to other cells, it always points to A1 instead of changing relative to the new position.

intermediate

What happens if you use $A1 in a formula?

The column A is fixed, but the row number changes when you copy the formula down or up. This is called a mixed reference.

intermediate

How does A$1 behave when copied across cells?

The row 1 is fixed, but the column changes when you copy the formula left or right. This is another form of mixed reference.

beginner

Give a real-life example where absolute references are useful.

Imagine you have a tax rate in cell B1. You want to calculate tax for many prices in column A. Using =A2*$B$1 lets you copy the formula down without changing the tax rate cell.

What does the formula =A1*$B$2 do when copied from row 1 to row 5?

A A1 changes to A5, B2 stays fixed

B Both A1 and B2 change relative to the new row

C A1 stays fixed, B2 changes to B5

D Both A1 and B2 stay fixed

The reference A1 is relative, so it changes to A5 when copied down. The reference $B$2 is absolute, so it stays fixed on B2.

Which reference locks only the column when copied?

A $A$1

B $A1

C A$1

D A1

The $ before the column letter locks the column. So $A1 locks column A but allows the row to change.

If you want a formula to always refer to cell C3 no matter where you copy it, which reference should you use?

A $C$3

B $C3

C C$3

D C3

Using $ before both column and row locks the reference completely.

What is the result of copying the formula =B$2 + $C3 one cell down and one cell right?

A =C$2 + $D4

B =B$3 + $D3

C =C$2 + $C4

D =B$3 + $C4

B$2 locks row 2 but column changes from B to C. $C3 locks column C but row changes from 3 to 4.

Why might you want to use mixed references like $A1 or A$1?

A To lock both row and column

B To make formulas easier to read

C To avoid using absolute references

D To allow only row or column to change when copying formulas

Mixed references lock either the row or the column, letting the other part change when copying formulas.

Explain what an absolute reference is in Google Sheets and why it is useful.

Think about how formulas behave when copied to other cells.

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

Focus on which parts of the reference stay fixed or change.