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.