OFFSET for dynamic ranges in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to create a dynamic range starting at cell A1 with 5 rows and 1 column.

Excel

=OFFSET(A1, 0, 0, [1], 1)

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

A0

B5

C10

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 for the height will create a range with zero rows.

Confusing the height with the width argument.

The OFFSET function here starts at A1 and extends down 5 rows and 1 column wide, so the height argument should be 5.

2fill in blank

medium

Complete the formula to create a dynamic range starting at B2, offset 2 rows down and 1 column right, with 3 rows and 2 columns.

Excel

=OFFSET(B2, [1], [2], 3, 2)

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

A1

B2

C3

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping row and column offsets.

Using zero offsets when movement is needed.

The formula moves 2 rows down and 1 column right from B2, so the row offset is 2 and the column offset is 1.

3fill in blank

hard

Fix the error in the formula to create a dynamic range starting at C3 with a height equal to the value in cell D1 and width 1.

Excel

=OFFSET(C3, 0, 0, [1], 1)

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

A5

BD1:D5

CSUM(D1)

DD1

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range like D1:D5 instead of a single cell.

Using a function like SUM incorrectly.

The height argument must be a single number. Using 'D1' references the value in cell D1 correctly.

4fill in blank

hard

Fill both blanks to create a dynamic range starting at A1, offset 0 rows and 0 columns, with height based on the count of numbers in column B and width 1.

Excel

=OFFSET(A1, [1], [2], COUNT(B:B), 1)

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

A0

B1

CCOUNT(B:B)

DB:B

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 for offset which moves the range away from A1.

Confusing the height argument with offset.

The offset rows and columns are both zero to start at A1. The height is set by COUNT(B:B) which counts numbers in column B.

5fill in blank

hard

Fill all three blanks to create a dynamic range starting at cell A1, offset 0 rows and 0 columns, with height equal to the number of non-empty cells in column A and width 1.

Excel

=OFFSET(A1, [1], [2], COUNTA([3]), 1)

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

A0

B1

CA:A

DB:B

Attempts:

3 left

💡 Hint

Common Mistakes

Using B:B instead of A:A for counting.

Setting offsets to 1 which shifts the range.

The offsets are zero to start at A1. COUNTA(A:A) counts all non-empty cells in column A to set the height.