Mixed references in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula with mixed references?

Given the formula =A$1 + $B2 entered in cell C3, and the following values:

- A1 = 10
- B2 = 5
- A3 = 3
- B3 = 7

What will be the value of the formula in cell C3?

A 22

B 17

C 15

D 12

💡 Hint

Remember that $ locks either the row or the column depending on its position.

The formula in C3 is =A$1 + $B2. A$1 refers to A1 (10). $B2 refers to B2 (5). The sum is 10 + 5 = 15. (A3 and B3 are distractors; relative references adjust only upon copying, not at entry.)

❓ Function Choice

intermediate

Which formula correctly sums a fixed column with a changing row?

You want to sum values from column C, but the row number should change as you copy the formula down. Which formula uses mixed references correctly to achieve this?

A =SUM(C$1)

B =SUM($C1)

C =SUM($C$1)

D =SUM(C1)

💡 Hint

Think about which part of the reference should stay fixed and which should change.

The formula =SUM($C1) locks the column C but allows the row to change as you copy down. So in row 2 it becomes $C2, in row 3 $C3, etc. This is the correct mixed reference for summing down a fixed column.

❓ data_analysis

advanced

How many unique cells are referenced when copying this formula?

If you enter the formula =B$2 + $C3 in cell D4 and copy it down 3 rows and right 2 columns (to cells E4:F7), how many unique cells are referenced in total by all copied formulas?

A 6

B 9

C 8

D 12

💡 Hint

Track how the mixed references change when copying across rows and columns.

Copied to E4:F7 (including original D4). E column formulas reference C$2 + $C3/$C4/$C5/$C6. F column: D$2 + $C3/$C4/$C5/$C6. Unique cells: C2, D2, C3, C4, C5, C6 (6 total).

🎯 Scenario

advanced

You want to multiply a fixed row by a fixed column in a table

You have a table where row 1 contains prices and column A contains quantities. You want to create a formula in cell B2 that multiplies the quantity in column A by the price in row 1, and then copy this formula across the table. Which formula uses mixed references correctly?

A =$A2*B$1

B =A$2*$B1

C =$A$2*$B$1

D =A2*B1

💡 Hint

Lock the column for quantities and lock the row for prices.

=$A2*B$1 locks column A for quantities (so it always refers to column A) and locks row 1 for prices (so it always refers to row 1). The row for quantities and column for prices change as you copy the formula across the table.

📊 Formula Result

expert

What is the value after copying this formula with mixed references?

You enter the formula =SUM($A2:B$1) in cell C3. What is the result of this formula if:
- A1=1, B1=2
- A2=3, B2=4
- A3=5, B3=6

Assuming the formula is copied from C3 to D4, what is the value in D4?

A 9

B 12

C 10

D 7

💡 Hint

Check how the mixed references shift when copying one row down and one column right.

Original formula in C3: =SUM($A2:B$1). In D4 (one row down, one column right), formula becomes =SUM($A3:C$1). This range includes A3 and B1 (since C1 is empty). Sum is 5 + 2 = 7.

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