Mixed references in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to lock the column but allow the row to change when copied down.

Google Sheets

=SUM([1]1:A10)

Drag options to blanks, or click blank then click option'

AA

BA$

C$A

D$A$

Attempts:

3 left

💡 Hint

Common Mistakes

Using $ before the row number instead of the column letter.

Locking both column and row with $A$1 which prevents any change.

Using $A locks the column A, but allows the row number to change when copied down.

2fill in blank

medium

Complete the formula to lock the row but allow the column to change when copied across.

Google Sheets

=AVERAGE(B[1]:E5)

Drag options to blanks, or click blank then click option'

A5$

B$5

C5

D$E

Attempts:

3 left

💡 Hint

Common Mistakes

Putting $ after the row number like 5$, which is invalid.

Locking both column and row with $E$5.

Using $5 locks the row 5, so when copied across columns, the row stays fixed.

3fill in blank

hard

Fix the error in the formula to correctly lock the column B but allow the row to change.

Google Sheets

=SUM(B[1]:B10)

Drag options to blanks, or click blank then click option'

A$2

B2$

C$B2

DB$2

Attempts:

3 left

💡 Hint

Common Mistakes

Putting $ after the row number.

Locking both column and row accidentally.

Using B$2 locks the row 2, but here we want to lock the column B only, so $B2 is incorrect. The correct is $B2 but since the code is B{{BLANK_1}}, the blank should be $2 to lock the row 2. However, the question asks to lock the column B only, so the correct is $2 to lock the row 2 is wrong. The correct answer is $2 to lock the row 2, but the question is about locking column B only. The correct answer is $B2 but the blank is after B, so the correct is $2. So the best fit is $2.

4fill in blank

hard

Fill both blanks to create a formula that locks the column A and the row 1 in the reference.

Google Sheets

=IF($[1]1 > 10, $A$[2], 0)

Drag options to blanks, or click blank then click option'

AA

B1

C2

DB

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column letter in first blank.

Not locking the row number in second blank.

The first blank should be B to compare column B row 1. The second blank is 1 to lock row 1 in the reference $A$1.

5fill in blank

hard

Fill all three blanks to create a formula that sums values from a fixed column B and a variable row.

Google Sheets

=SUM($[1]$[2]:[3]10)

Drag options to blanks, or click blank then click option'

AB

B2

DC

Attempts:

3 left

💡 Hint

Common Mistakes

Not locking the column or row correctly.

Using wrong column letter in the range end.

The first blank locks column B with B, the second locks row 2 with 2, and the third is column C for the range end.

Practice

1. What does a mixed reference like $A1 mean in Google Sheets?

easy

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

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

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

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

Solution

1. Step 1: Understand the dollar sign placement

   In $A1, the dollar sign before A means the column is fixed.

2. Step 2: Analyze the row number

   The row number 1 has no dollar sign, so it changes when copied.

3. Final Answer:

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

4. Quick Check:

   Mixed reference $A1 fixes column A [OK]

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

Common Mistakes:

• Thinking $ fixes both row and column
• Confusing which part the $ fixes
• Assuming no change happens when copying

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

easy

A. A$1

B. $A$1

C. $A1

D. A1$

Solution

1. Step 1: Identify the dollar sign position for fixing row

   To fix the row, place $ before the row number, like A$1.

2. Step 2: Check other options

   $A$1 fixes both row and column, $A1 fixes column only, and A1$ is invalid syntax.

3. Final Answer:

   A$1 -> Option A

4. Quick Check:

   Dollar before row number fixes row [OK]

Hint: Put $ before row number to fix row only [OK]

Common Mistakes:

• Placing $ after the row number (invalid)
• Fixing both row and column unintentionally
• Confusing column and row positions

3. Given the formula =B$2 + $C3 in cell D4, what will be the formula in cell D5 after copying it down one row?

medium

A. =B$2 + $C4

B. =B$3 + $C4

C. =B$3 + $C3

D. =B$2 + $C3

Solution

1. Step 1: Analyze the first reference B$2

   Column B is relative, row 2 is fixed by $. Copying down one row keeps row 2 fixed, so it stays B$2.

2. Step 2: Analyze the second reference $C3

   Column C is fixed by $, row 3 is relative. Copying down one row changes row 3 to 4, so it becomes $C4.

3. Final Answer:

   =B$2 + $C4 -> Option A

4. Quick Check:

   Mixed references adjust only unfixed parts [OK]

Hint: Only parts without $ change when copied [OK]

Common Mistakes:

• Changing fixed rows or columns
• Not updating relative row numbers
• Confusing which part is fixed

4. Given the formula =SUM($A1:B$2) in cell C3. When copying this formula to cell D4, what will be the formula?

medium

A. =SUM($A1:B$2)

B. =SUM($A1:C$2)

C. =SUM($A2:B$2)

D. =SUM($A2:C$2)

Solution

1. Step 1: Understand how references change when copied

   Copying from C3 to D4 moves one column right and one row down. $A1 changes to $A2 (row changes), B$2 changes to C$2 (column changes).

2. Step 2: Check the resulting range

   The range becomes $A2:C$2.

3. Final Answer:

   =SUM($A2:C$2) -> Option D

4. Quick Check:

   Mixed references adjust range correctly [OK]

Hint: Trace relative column/row shifts separately [OK]

Common Mistakes:

• Forgetting to adjust relative row from 1 to 2
• Not updating relative column from B to C
• Assuming no changes when copying
• Confusing fixed vs relative parts

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 this formula across and down the table. Which formula uses mixed references correctly?

hard

A. =$A$2*B1

B. =A2*$B$1

C. =$A2*B$1

D. =A$2*$B1

Solution

1. Step 1: Fix the column for the value in column A

   The value in column A of the same row means column A fixed, row relative: $A2.

2. Step 2: Fix the row for the value in row 1 of the same column

   The value in row 1 of the same column means row 1 fixed, column relative: B$1.

3. Step 3: Combine for multiplication

   The formula is =$A2*B$1, which copies correctly across and down.

4. Final Answer:

   =$A2*B$1 -> Option C

5. Quick Check:

   Fix column for A, fix row for 1 [OK]

Hint: Fix column for A, fix row for 1 to copy correctly [OK]

Common Mistakes:

• Fixing both row and column incorrectly
• Not fixing the correct part of the reference
• Using absolute references that prevent copying