Absolute references ($A$1) in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to keep the reference to cell A1 fixed when copying.

Excel

=SUM([1], B2)

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

AA1

B$A$1

CA$1

D$A1

Attempts:

3 left

💡 Hint

Common Mistakes

Using A1 without dollar signs causes the reference to change when copied.

Using only one dollar sign fixes only the column or the row, not both.

Using $A$1 locks both the column and row, so the reference stays fixed when copying the formula.

2fill in blank

medium

Complete the formula to multiply the value in B2 by the fixed value in cell C1.

Excel

=B2*[1]

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

AC1

BC$1

C$C1

D$C$1

Attempts:

3 left

💡 Hint

Common Mistakes

Using C1 without dollar signs changes the reference when copied.

Using only one dollar sign fixes only part of the reference.

The absolute reference $C$1 keeps the reference fixed when copying the formula to other cells.

3fill in blank

hard

Fix the error in the formula to always refer to cell D4 when copied.

Excel

=SUM(A1:D[1])

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

A4

B$4

C$D$4

D$D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using $4 is invalid inside the range reference like this.

Using $D$4 or $D4 is invalid inside the range reference like this.

Using 4 keeps the row number 4 fixed in the range when copying the formula.

4fill in blank

hard

Fill both blanks to create a formula that sums from fixed cell A1 to the current row in column B.

Excel

=SUM([1]:B[2])

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

A$A$1

BA1

CROW()

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Not fixing the start cell causes the range to shift when copied.

Using an absolute row reference like $1 does not adjust the range dynamically.

The start cell $A$1 is fixed, and 1 creates a relative row reference that adjusts to the current row for the end of the range.

5fill in blank

hard

Fill all three blanks to create a formula that multiplies the fixed price in A1 by the quantity in the current row of column B, only if quantity is greater than zero.

Excel

=IF(B[1] [2] 0, $A$1 * B[3], 0)

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

A1

B>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using a fixed row number like $1 instead of 1 makes the formula not adjust when copied.

Using < instead of > changes the condition logic.

1 creates relative references to the current row for B cells (e.g., B1, B2 when copied), > checks if quantity is greater than zero, and the formula multiplies fixed price $A$1 by quantity.

Practice

1. What does an absolute reference like $A$1 do in Excel?

easy

A. It locks both the column and row so the reference does not change when copied.

B. It locks only the column but allows the row to change when copied.

C. It locks only the row but allows the column to change when copied.

D. It allows both column and row to change when copied.

Solution

1. Step 1: Understand absolute reference syntax

   The dollar signs before the column letter and row number mean both are fixed.

2. Step 2: Effect when copying formula

   When copying, the reference $A$1 stays exactly the same, not shifting.

3. Final Answer:

   It locks both the column and row so the reference does not change when copied. -> Option A

4. Quick Check:

   Absolute reference = locks column and row [OK]

Hint: Dollar signs lock column and row in cell references [OK]

Common Mistakes:

• Thinking $ locks only column or only row
• Confusing absolute with relative references
• Assuming references always change when copied

2. Which of these is the correct way to write an absolute reference to cell B2 in Excel?

easy

A. B2

B. B$2

C. $B$2

D. $B2

Solution

1. Step 1: Identify absolute reference format

   Absolute reference requires $ before both column letter and row number.

2. Step 2: Check each option

   Only $B$2 locks both column B and row 2.

3. Final Answer:

   $B$2 -> Option C

4. Quick Check:

   Absolute reference = $ before column and row [OK]

Hint: Use $ before column and row for absolute reference [OK]

Common Mistakes:

• Using $ only before column or only before row
• Forgetting $ signs completely
• Confusing relative and absolute references

3. If cell C1 contains the formula =A1*$B$1 and you copy it to cell C2, what will the formula in C2 be?

medium

A. =A1*$B$2

B. =A2*$B$1

C. =A2*B1

D. =A1*B1

Solution

1. Step 1: Understand relative and absolute parts

   Reference A1 is relative, so it changes to A2 when copied down one row. Reference $B$1 is absolute, so it stays $B$1.

2. Step 2: Write new formula in C2

   Formula becomes =A2*$B$1 after copying down.

3. Final Answer:

   =A2*$B$1 -> Option B

4. Quick Check:

   Relative changes, absolute stays fixed [OK]

Hint: Relative changes, absolute ($) stays fixed when copied [OK]

Common Mistakes:

• Changing absolute reference when copying
• Not changing relative reference
• Mixing up row and column changes

4. You want to multiply each value in column A by the fixed tax rate in cell D1. Which formula correctly uses absolute reference when entered in B2 and copied down?

medium

A. =A2*$D$1

B. =$A$2*D$1

C. =A2*D1

D. =$A2*D1

Solution

1. Step 1: Identify fixed tax rate cell

   Tax rate is in D1 and must stay fixed, so use absolute reference $D$1.

2. Step 2: Use relative reference for values in column A

   Values in A2, A3, etc. should change when copied, so use relative A2.

3. Final Answer:

   =A2*$D$1 -> Option A

4. Quick Check:

   Fixed tax rate uses absolute reference [OK]

Hint: Lock tax rate cell with $ when copying formulas [OK]

Common Mistakes:

• Not using $ for tax rate cell
• Locking value cell instead of tax rate
• Mixing relative and absolute incorrectly

5. You have a table where you want to calculate total price in column D by multiplying quantity in column B by unit price in cell $C$1. You write the formula =B2*C1 in D2 and copy it down. What is the problem and how to fix it?

hard

A. The formula should use =B$2*$C1 to fix the problem.

B. The reference to B2 is absolute and should be relative; fix by using B$2.

C. The formula is correct; no fix needed.

D. The reference to C1 is relative and changes; fix by using $C$1.

Solution

1. Step 1: Identify the problem with copying formula

   Reference to C1 is relative, so when copied down it changes to C2, C3, etc., which is wrong because unit price is fixed.

2. Step 2: Fix by making unit price reference absolute

   Use $C$1 to lock both column and row so it stays fixed when copied.

3. Final Answer:

   The reference to C1 is relative and changes; fix by using $C$1. -> Option D

4. Quick Check:

   Fix unit price with absolute reference [OK]

Hint: Lock fixed cells with $ to avoid wrong reference changes [OK]

Common Mistakes:

• Not locking fixed cell reference
• Locking wrong cell or part of reference
• Assuming formula is correct without checking copy effect