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.

Practice

1. What does the mixed reference $A1 mean in Excel?

easy

A. Neither the column nor the row is fixed; both change when copied.

B. The row 1 is fixed, but the column letter changes when copied.

C. Both the column and row are fixed and do not change when copied.

D. The column A is fixed, but the row number changes when copied.

Solution

1. Step 1: Understand the $ symbol before the column letter

   The $ before A means the column A is fixed and will not change when the formula is copied across columns.

2. Step 2: Understand the absence of $ before the row number

   The row number 1 is not fixed, so it will change when the formula is copied across rows.

3. Final Answer:

   The column A is fixed, but the row number changes when copied. -> Option D

4. Quick Check:

   Mixed reference $A1 fixes column only [OK]

Hint: Dollar before column fixes column; before row fixes row [OK]

Common Mistakes:

• Thinking $A1 fixes the row instead of the column
• Confusing $A1 with A$1
• Assuming both row and column are fixed

2. Which of the following is the correct syntax for a mixed reference that fixes the row 5 but allows the column to change?

easy

A. $5A

B. A$5

C. $A$5

D. 5$A

Solution

1. Step 1: Identify the correct placement of $ for fixing row

   To fix the row 5, the $ must be placed before the row number: A$5.

2. Step 2: Check the options for correct syntax

   A$5 uses A$5 which fixes row 5 and allows column to change. Other options have incorrect order or fix both row and column.

3. Final Answer:

   A$5 -> Option B

4. Quick Check:

   Row fixed with $ before number = A$5 [OK]

Hint: Put $ before row number to fix row, before column letter to fix column [OK]

Common Mistakes:

• Placing $ after the row number
• Swapping column letter and row number
• Using $ before both column and row when only one should be fixed

3. If cell B2 contains the formula = $A1 + A$1 and you copy this formula to cell C3, what will be the resulting formula in C3?

medium

A. = $B2 + B$2

B. = $A3 + C$1

C. = $A2 + B$1

D. = $A3 + B$1

Solution

1. Step 1: Analyze the $A1 part when copied from B2 to C3

   $ before A fixes the column A, so column stays A. Row 1 changes relative to row move: from row 2 to 3 is +1, so row 1 becomes 2. So $A1 becomes $A2.

2. Step 2: Analyze the A$1 part when copied from B2 to C3

   $ before 1 fixes the row 1, so row stays 1. Column A changes relative to column move: from B to C is +1, so A becomes B. So A$1 becomes B$1.

3. Final Answer:

   = $A2 + B$1 -> Option C

4. Quick Check:

   Fixed column stays, fixed row stays, others shift [OK]

Hint: Fixed parts stay; unfixed parts shift with copy [OK]

Common Mistakes:

• Changing fixed column or row incorrectly
• Not adjusting relative parts when copying
• Mixing up column letters and row numbers

4. You have a formula in cell D4: =SUM($B2:B$5). When copying this formula to cell E6, the formula becomes =SUM($B4:C$5). Is this correct? If not, what is the correct formula?

medium

A. =SUM($B4:C$5)

B. =SUM($B4:B$5)

C. =SUM($B4:C$7)

D. =SUM($B2:C$7)

Solution

1. Step 1: Trace the start reference $B2 from D4 to E6

   Copy is +1 column (D->E), +2 rows (4->6). $B fixes column B; row 2 relative +2 -> $B4.

2. Step 2: Trace the end reference B$5

   Column B relative +1 -> C; $5 fixes row 5 -> C$5.

3. Step 3: Resulting formula is =SUM($B4:C$5)

   This matches the formula stated in the question, confirming it is correct.

4. Final Answer:

   =SUM($B4:C$5) -> Option A

5. Quick Check:

   $B2:B$5 -> $B4:C$5 (+1col +2row) [OK]

Hint: Shift relative parts by copy distance; fixed parts stay [OK]

Common Mistakes:

• Not shifting row or column correctly
• Assuming fixed parts change
• Miscalculating relative shifts for range endpoints

5. You want to create a formula in cell B2 that multiplies the value in column A of the same row by the value in row 1 of the same column, and then copy it across columns and rows. Which mixed reference formula should you use in B2?

hard

A. = $A2 * B$1

B. = A$2 * $B1

C. = $A$2 * $B$1

D. = A2 * B1

Solution

1. Step 1: Fix the column for the value in column A but allow row to change

   The value in column A of the same row means column A fixed ($A), row relative (2). So use $A2.

2. Step 2: Fix the row for the value in row 1 but allow column to change

   The value in row 1 of the same column means row 1 fixed ($1), column relative (B). So use B$1.

3. Step 3: Combine both parts in multiplication formula

   The formula is = $A2 * B$1, which will adjust correctly when copied across rows and columns.

4. Final Answer:

   = $A2 * B$1 -> Option A

5. Quick Check:

   Fix column for first, fix row for second [OK]

Hint: Fix column for vertical, fix row for horizontal references [OK]

Common Mistakes:

• Fixing both row and column unnecessarily
• Not fixing any part causing wrong references
• Swapping fixed parts between references