Mixed references ($A1, A$1) in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a mixed reference in Excel?

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

beginner

Explain the difference between $A1 and A$1.

$A1 means the column A is fixed, but the row number can change when copied. A$1 means the row 1 is fixed, but the column can change when copied.

intermediate

How does the formula =SUM($A1:B$1) behave when copied across columns and rows?

When copied across columns, $A1 keeps column A fixed, so the start column stays A. When copied across rows, B$1 keeps row 1 fixed, so the end row stays 1. This means the range adjusts partly depending on the direction of copying.

beginner

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

Mixed references let you lock either the row or the column, which is useful when you want part of the reference to stay fixed while the other part changes as you copy formulas. This helps create flexible formulas for tables or grids.

intermediate

What happens if you copy a formula with reference $A1 from cell B2 to C3?

The column A stays fixed because of the $, so it remains A. The row number changes relative to the move: from row 2 to row 3, so the reference becomes $A3.

What does the mixed reference $B3 mean when copied down rows?

A Both column and row change

B Column B stays fixed, row changes

C Row 3 stays fixed, column changes

D Both column and row stay fixed

The $ before B fixes the column, so column B stays the same. The row 3 is relative and changes when copied across rows.

If a formula with reference A$5 is copied down one row, what happens to the reference?

A It becomes A$6

B It becomes B$5

C It stays A$5

D It becomes $A$6

The $ before 5 fixes the row, so the row number stays 5 even when copied down.

Which reference will keep the column fixed but allow the row to change?

A $A1

B $A$1

C A$1

D A1

$A1 fixes the column A but allows the row number to change.

When copying a formula with reference $C$4, what happens to the reference?

A Both column and row stay fixed

B Column changes, row fixed

C Both column and row change

D Column fixed, row changes

The $ before both column and row fixes both, so the reference does not change when copied.

Why might you use A$1 in a formula copied across columns?

A To allow both row and column to change

B To keep the column fixed while allowing the row to change

C To keep both row and column fixed

D To keep the row fixed while allowing the column to change

A$1 fixes the row 1, so when copied across columns, the row stays the same but the column changes.

Describe what a mixed reference is in Excel and give two examples.

Think about the $ sign before column or row.

Explain how copying a formula with reference $B2 behaves when copied down and when copied across.

Focus on what the $ sign locks.