Relative references (A1) in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula when copied?

You have the value 10 in cell A1 and 20 in cell A2. In cell B1, you enter the formula =A1+A2. Then you copy the formula from B1 to B2. What is the value shown in cell B2?

A 30

B 20

C 10

D 40

💡 Hint

Remember that relative references change based on the position where the formula is copied.

The formula in B1 is =A1+A2, which sums 10 + 20 = 30. When copied to B2, the formula adjusts to =A2+A3. Since A3 is empty (0), the sum is 20 + 0 = 20.

📊 Formula Result

intermediate

What value appears in cell C3 after copying?

Cell B2 contains the formula =A1*2. If you copy this formula from B2 to C3, what will be the formula in C3 and what value will it calculate if A2=5 and B2=10?

A Formula: =B2*2, Value: 10*2=20

B Formula: =B1*2, Value: 0*2=0

C Formula: =A2*2, Value: 5*2=10

D Formula: =B3*2, Value: 0*2=0

💡 Hint

Relative references adjust based on how many rows and columns you move the formula.

Copying the formula from B2 to C3 moves it 1 column right and 1 row down. The original formula references A1. Moving 1 column right and 1 row down changes A1 to B2. So the formula in C3 is =B2*2. Since B2=10, the value is 20.

❓ Function Choice

advanced

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

You want to write a formula in cell B2 that sums the values in the row above (cells A1 and B1). When you copy this formula to C2, it should sum B1 and C1. Which formula should you use in B2?

A =A1+B1

B =SUM(A1:B1)

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

D =SUM(A$1:B$1)

💡 Hint

Think about how to keep the row fixed but allow the columns to adjust when copying.

Option D uses mixed references with the row fixed ($1) and columns relative (A and B). When copied from B2 to C2, the formula adjusts to =SUM(B$1:C$1), summing the row above for the new columns. Option D uses absolute references, so it always sums A1:B1, not adjusting columns.

🎯 Scenario

advanced

You want to multiply each value in column A by the value in B1 and copy down. Which formula in C2 works correctly?

Column A has numbers starting from A2 downwards. Cell B1 has a multiplier. You want to write a formula in C2 that multiplies A2 by B1. When you copy this formula down column C, it should always multiply the value in column A of the same row by the fixed value in B1. Which formula should you use in C2?

A =A2*B1

B =A$2*B$1

C =$A2*$B$1

D =$A$2*B1

💡 Hint

Fix the reference to B1 so it does not change when copying down.

Option C uses mixed references: $A2 fixes the column A but allows the row to change, and $B$1 fixes the cell B1 so it does not change when copied. This ensures each row multiplies the value in column A of that row by the fixed multiplier in B1.

❓ data_analysis

expert

How many unique cells are referenced after copying this formula down 4 rows?

In cell B2, you enter the formula =A1 + A2. You then copy this formula down from B2 to B5 (4 rows total). How many unique cells in column A are referenced by all these formulas combined?

A 5 unique cells

B 4 unique cells

C 6 unique cells

D 8 unique cells

💡 Hint

List the references for each copied formula and count unique cells.

Formulas in B2 to B5 are: B2: =A1+A2; B3: =A2+A3; B4: =A3+A4; B5: =A4+A5. The unique cells referenced are A1, A2, A3, A4, A5. Total unique cells: 5.