Mixed references in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a mixed reference in Google Sheets?

A mixed reference is a cell reference where either the row or the column is fixed (absolute), but not both. For example, $A1 fixes the column A but allows the row to change, and A$1 fixes the row 1 but allows the column to change.

beginner

How do you write a mixed reference that fixes the row but allows the column to change?

You write the reference with a dollar sign before the row number only. For example, A$1 fixes row 1 but lets the column change when copied.

beginner

How does a mixed reference behave when copied across columns?

If the column is not fixed (no $ before the column letter), it changes as you copy across columns. If the column is fixed (with $), it stays the same.

intermediate

Example: What happens to the reference $B3 when copied one cell to the right?

The reference stays $B3 because the column B is fixed with $, so it does not change. The row 3 is relative and stays 3 because copying right does not change rows.

intermediate

Why use mixed references instead of fully absolute or fully relative references?

Mixed references let you lock either the row or the column while allowing the other to change. This is useful for formulas that need to adjust in one direction but stay fixed in the other, like when copying formulas across a table.

Which of these is a mixed reference?

A $A$1

B $A1

C A1

D 1$A

Option B fixes the column A but allows the row to change, which is a mixed reference. Option A is fully absolute, C is fully relative, and D is invalid.

If you copy the formula with reference A$2 from cell B2 to C3, what will the reference become?

A B$2

B A$3

C B3

D A2

The column changes from A to B because it is relative, but the row 2 is fixed with $, so it stays 2.

What does the $ symbol do in a cell reference?

A Changes the cell color

B Makes the cell reference invalid

C Fixes the row or column so it does not change when copied

D Adds a comment to the cell

The $ symbol locks either the row or the column in a reference to keep it fixed when copying formulas.

Which reference will change both row and column when copied?

A A1

B $A$1

C $A1

D A$1

A1 is a fully relative reference, so both row and column change when copied.

You want to fix the row number 5 but allow the column to change when copying a formula. Which reference should you use?

A $5A

B $A$5

C $A5

D A$5

A$5 fixes the row 5 but allows the column letter to change.

Explain what a mixed reference is and give an example.

Think about locking only the row or only the column.

Describe how mixed references behave when copying formulas across rows and columns.

Consider $ before column or row and how that affects copying.