Relative references in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to add the value in cell A1 to the value in cell B1.

Google Sheets

=A1[1]B1

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

A*

B-

C/

D+

Attempts:

3 left

💡 Hint

Common Mistakes

Using minus (-) instead of plus (+).

Using multiplication (*) or division (/) by mistake.

The plus sign + adds the values of two cells.

2fill in blank

medium

Complete the formula to multiply the value in cell C2 by 10.

Google Sheets

=C2[1]10

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

A*

B+

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using plus (+) instead of multiplication (*).

Using division (/) or minus (-) by mistake.

The asterisk * multiplies numbers in formulas.

3fill in blank

hard

Fix the error in the formula to subtract B3 from A3.

Google Sheets

=A3[1]B3

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

A*

B/

C-

D+

Attempts:

3 left

💡 Hint

Common Mistakes

Using plus (+) instead of minus (-).

Using multiplication (*) or division (/) by mistake.

The minus sign - subtracts one cell's value from another.

4fill in blank

hard

Fill both blanks to create a formula that divides the value in D4 by the value in E4.

Google Sheets

=D4[1]E4[2]2

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

A/

B*

C+

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using plus (+) or minus (-) instead of division (/).

Mixing up multiplication and division order.

The slash / divides numbers. Multiplying by 2 after division doubles the result.

5fill in blank

hard

Fill all three blanks to create a formula that adds A5 and B5, then multiplies the sum by C5.

Google Sheets

=(A5[1]B5)[2]C5[3]1

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

A+

B*

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the order of operations.

Using division (/) instead of subtraction (-) at the end.

First add A5 and B5 with +. Then multiply the sum by C5 with *. The last blank uses minus - with 1 to subtract 1 from the result.

Practice

1. What happens to a relative cell reference like A1 when you copy a formula from cell B1 to cell B2 in Google Sheets?

easy

A. It becomes an error because references cannot change.

B. It stays as A1 because it is fixed.

C. It changes to A2 to match the new row.

D. It changes to B1 to match the new column.

Solution

1. Step 1: Understand relative references behavior

   Relative references adjust based on the position where the formula is copied. Copying down one row changes the row number in the reference.

2. Step 2: Apply to the example

   Copying a formula from B1 to B2 moves it down one row, so A1 becomes A2.

3. Final Answer:

   It changes to A2 to match the new row. -> Option C

4. Quick Check:

   Relative reference row changes when copied down [OK]

Hint: Relative references adjust row/column based on copy direction [OK]

Common Mistakes:

• Thinking relative references stay fixed
• Confusing relative with absolute references
• Assuming column changes when copying down

2. Which of the following is the correct way to write a relative reference to cell C3 in a formula?

easy

A. C3

B. C$3

C. $C$3

D. $C3

Solution

1. Step 1: Identify relative vs absolute references

   Relative references have no dollar signs and change when copied. Absolute references use $ to fix row, column, or both.

2. Step 2: Check each option

   C3 is C3 with no $ signs, so it is fully relative.

3. Final Answer:

   C3 -> Option A

4. Quick Check:

   No $ means relative reference [OK]

Hint: No $ means relative reference in formulas [OK]

Common Mistakes:

• Using $ signs thinking they are needed for relative
• Mixing absolute and relative incorrectly
• Confusing syntax of $ for fixing references

3. If cell B2 contains the formula =A1+1 and you copy this formula to cell B3, what will be the formula in B3 and its result if A2=5?

medium

A. Formula: =A3+1, Result: 7

B. Formula: =A1+1, Result: 2

C. Formula: =B2+1, Result: 3

D. Formula: =A2+1, Result: 6

Solution

1. Step 1: Understand how relative references adjust when copied down

   Copying the formula from B2 to B3 moves it down one row, so A1 becomes A2.

2. Step 2: Calculate the result using A2=5

   The formula in B3 is =A2+1, so the result is 5 + 1 = 6.

3. Final Answer:

   Formula: =A2+1, Result: 6 -> Option D

4. Quick Check:

   Relative reference row updates, result matches calculation [OK]

Hint: Copying down changes row number in relative references [OK]

Common Mistakes:

• Not updating the row number in the reference
• Confusing column changes when copying down
• Calculating result without updating reference

4. You have a formula in cell C3: =A1+B1. When you copy it to cell C4, the formula becomes =A2+B2. But you want it to stay =A1+B1. What is the best fix?

medium

A. Change the formula to =A$1+B$1

B. Change the formula to =$A$1+$B$1

C. Change the formula to =A1+B1 again

D. Change the formula to =A2+B2

Solution

1. Step 1: Identify why formula changes when copied

   Relative references like A1 change when copied. To keep them fixed, use absolute references with $.

2. Step 2: Use absolute references to fix both row and column

   Using $A$1 and $B$1 fixes both row and column, so the formula stays the same when copied.

3. Final Answer:

   Change the formula to =$A$1+$B$1 -> Option B

4. Quick Check:

   Absolute references keep formula fixed when copied [OK]

Hint: Use $ before column and row to fix references [OK]

Common Mistakes:

• Using partial absolute references that don't fix both row and column
• Not using $ at all
• Trying to copy formula without fixing references

5. You have a list of prices in column A (A2:A6) and want to calculate a 10% tax for each price in column B using a formula in B2 copied down. Which formula correctly uses relative references to calculate tax for each row?

hard

A. =A2*0.10

B. =A$2*0.10

C. =$A2*0.10

D. =$A$2*0.10

Solution

1. Step 1: Understand the need for relative reference in row

   Each row in column B should multiply the price in the same row of column A. So the row number must change when copied down.

2. Step 2: Check which formula allows row to change

   =A2*0.10 uses A2 without $, so row changes when copied. Options A, C, D fix either row or column incorrectly.

3. Final Answer:

   =A2*0.10 -> Option A

4. Quick Check:

   Relative row reference changes per row [OK]

Hint: Use relative row to apply formula to each row correctly [OK]

Common Mistakes:

• Fixing row with $ so tax is same for all rows
• Fixing column unnecessarily
• Using absolute references that don't update per row