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.

Practice

1. What happens to a relative cell reference like A1 when you copy a formula containing it from cell B1 to cell B2?

easy

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

B. It stays as A1 because references never change.

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

D. It becomes an error because references can't be copied.

Solution

1. Step 1: Understand relative references behavior

   Relative references adjust based on where the formula is copied. The row and column numbers change relative to the new position.

2. Step 2: Apply to copying from B1 to B2

   Copying down one row changes the row number in the reference from 1 to 2, 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 shift row/column when copied [OK]

Common Mistakes:

• Thinking references never change when copied
• Confusing relative with absolute references
• Assuming column changes when copying down

2. Which of the following formulas correctly uses a relative reference to add the value in cell B1 to the value in the current row's A column cell?

easy

A. =A1 + B1

B. =A1 + $B$1

C. =$A$1 + B1

D. =$A1 + B$1

Solution

1. Step 1: Identify relative references for current row

   To add the value in column A of the current row, use A1 as a relative reference that changes row when copied.

2. Step 2: Add value from column B, row 1

   Since B1 is fixed to row 1, but we want it relative, just use B1 as relative reference.

3. Final Answer:

   =A1 + B1 -> Option A

4. Quick Check:

   Relative references adjust row and column [OK]

Hint: Relative references have no $ signs [OK]

Common Mistakes:

• Using $ signs which fix references
• Mixing absolute and relative incorrectly
• Confusing column and row fixing

3. If cell C1 contains the formula =A1+B1 and you copy this formula to cell C2, what will be the formula in C2 and its result if A2=3 and B2=4?

medium

A. Formula: =A1+B1, Result: 5

B. Formula: =A2+B2, Result: 7

C. Formula: =B2+C2, Result: 7

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

Solution

1. Step 1: Understand formula copying with relative references

   Copying from C1 to C2 moves the formula down one row, so A1 becomes A2 and B1 becomes B2.

2. Step 2: Calculate result using values in A2 and B2

   Given A2=3 and B2=4, the formula =A2+B2 calculates 3 + 4 = 7.

3. Final Answer:

   Formula: =A2+B2, Result: 7 -> Option B

4. Quick Check:

   Relative references update row when copied down [OK]

Hint: Copying down changes row numbers in references [OK]

Common Mistakes:

• Not updating row numbers when copying
• Confusing which cells are referenced
• Calculating result with old cell values

4. You have the formula =A1+B$1 in cell C2. When copied to cell C3, what is the correct adjusted formula?

medium

A. =A2+B2

B. =A3+B2

C. =A3+B$1

D. =A2+B$1

Solution

1. Step 1: Analyze relative and absolute parts of the formula

   A1 is relative (both row and column change), B$1 fixes the row 1 but allows column to change.

2. Step 2: Adjust formula when copying from C2 to C3 (down one row)

   Copying down one row changes the relative row reference from 1 to 2, so A1 becomes A2. B$1 keeps row 1 fixed, column unchanged (no column shift), so it stays B$1.

3. Final Answer:

   =A2+B$1 -> Option D

4. Quick Check:

   Row fixed with $ stays same, relative row changes [OK]

Hint: Rows with $ don't change when copied down [OK]

Common Mistakes:

• Changing fixed row numbers
• Changing both row and column in fixed references
• Ignoring $ signs in references
• Mistaking the formula row reference for the cell row

5. You want to create a formula in cell D2 that multiplies the value in the same row's B column by the value in cell C1. You want to copy this formula down column D without changing the reference to C1. Which formula should you enter in D2?

hard

A. =B2*$C$1

B. =$B2*C1

C. =$B$2*C1

D. =B2*C1

Solution

1. Step 1: Identify relative and absolute references needed

   The reference to B2 should be relative to change row when copied down. The reference to C1 should be fixed to not change when copied.

2. Step 2: Use $ signs to fix C1 and keep B2 relative

   Fix both column and row of C1 with $C$1. Keep B2 relative.

3. Final Answer:

   =B2*$C$1 -> Option A

4. Quick Check:

   Fix reference with $ to keep it constant when copied [OK]

Hint: Use $ to fix references you don't want to change [OK]

Common Mistakes:

• Not fixing C1, so it changes when copied
• Fixing B2 so it doesn't update row
• Using partial $ incorrectly