Relative references in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of the formula when copied?

You have the formula =A1+B1 in cell C1. If you copy this formula from C1 to C2, what will be the formula in C2 and what will it calculate if A2=3 and B2=4?

A Formula in C2: <code>=A2+B2</code>, Result: 7

B Formula in C2: <code>=A1+B1</code>, Result: 5

C Formula in C2: <code>=B2+A2</code>, Result: 7

D Formula in C2: <code>=C1+B1</code>, Result: 9

💡 Hint

Think about how relative references change when you copy formulas down a row.

Relative references adjust based on the position where the formula is copied. Copying from C1 to C2 shifts the references from A1 and B1 to A2 and B2, so the formula becomes =A2+B2 and calculates 3+4=7.

❓ Function Choice

intermediate

Which formula correctly sums the values in the row when copied?

You want to sum values in columns A and B for each row starting from row 2. Which formula should you put in cell C2 so that when copied down, it sums the correct row's A and B values?

A =SUM($A2:$B2)

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

C =SUM(A$2:B$2)

D =SUM(A2:B2)

💡 Hint

Consider how relative and absolute references behave when copying formulas down rows.

Using =SUM(A2:B2) with relative references allows the formula to adjust to each row when copied down, summing the correct cells in columns A and B for that row.

🎯 Scenario

advanced

Fix the formula to keep column fixed but allow row to change when copied

You have the formula =A1*B1 in cell C1. You want to copy it down column C so that the reference to column A stays fixed but the row number changes (A1, A2, A3, ...). Which formula achieves this?

A =$A$1*B1

B =A$1*B1

C =$A1*B1

D =A1*$B1

💡 Hint

Use $ to fix the column but leave the row relative.

Using $ before the column letter fixes the column (A), while leaving the row number relative allows it to change when copied down. So =$A1*B1 fixes column A but changes row.

❓ data_analysis

advanced

How many cells will show the value 10 after copying the formula?

In cells A1 to A5, the values are 2, 4, 6, 8, 10 respectively. In cell B1, you enter the formula =A1*5 and copy it down to B5. How many cells in B1:B5 will show the value 10?

A 1

B 2

C 0

D 5

💡 Hint

Multiply each A cell by 5 and check which equals 10.

Only A1=2 multiplied by 5 equals 10, so only B1 shows 10.

📊 Formula Result

expert

What is the value in cell C3 after copying the formula?

You have the formula =SUM(A1:B1) in cell C1. You copy this formula down to C3. Given the following values:
A1=1, B1=2
A2=3, B2=4
A3=5, B3=6
What is the value in cell C3?

A 5

B 11

C 7

D 3

💡 Hint

Check how the relative references change when copying down two rows.

Copying the formula from C1 to C3 shifts the references from A1:B1 to A3:B3. Sum of A3 and B3 is 5+6=11.