Bird
Raised Fist0
Excelspreadsheet~10 mins

Relative references (A1) in Excel - Interactive Code Practice

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Practice - 5 Tasks
Answer the questions below
1fill in blank
easy

Complete the formula to add the values in cells A1 and B1.

Excel
=A1[1]B1
Drag options to blanks, or click blank then click option'
A+
B-
C*
D/
Attempts:
3 left
💡 Hint
Common Mistakes
Using a minus (-) instead of plus (+).
Using multiplication (*) or division (/) by mistake.
2fill in blank
medium

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

Excel
=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 subtraction (-) by mistake.
3fill in blank
hard

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

Excel
=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.
4fill in blank
hard

Complete the formula to divide the value in D4 by the value in E4.

Excel
=D4[1]E4
Drag options to blanks, or click blank then click option'
A*
B/
C+
D-
Attempts:
3 left
💡 Hint
Common Mistakes
Using multiplication (*) or addition (+) instead of division.
Using subtraction (-) by mistake.
5fill in blank
hard

Fill both blanks to create a formula that adds A5 and B5, then multiplies the result by C5.

Excel
=(A5[1]B5)[2]C5
Drag options to blanks, or click blank then click option'
A+
B*
C-
D/
Attempts:
3 left
💡 Hint
Common Mistakes
Using subtraction (-) or division (/) instead of addition or multiplication.
Forgetting parentheses to control order of operations.

Practice

(1/5)
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