Removing duplicates in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to remove duplicates from the range A1:A10.

Excel

=UNIQUE([1])

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

AA10:A20

BB1:B10

CA1:A1

DA1:A10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a single cell instead of a range.

Selecting the wrong column range.

The UNIQUE function removes duplicates from the specified range. Here, we want to remove duplicates from A1 to A10, so the correct range is A1:A10.

2fill in blank

medium

Complete the formula to remove duplicates from the range B2:B20 and sort the results.

Excel

=SORT(UNIQUE([1]))

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

AB2:B20

BA1:A10

CC1:C10

DB1:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong range inside UNIQUE.

Forgetting to wrap UNIQUE inside SORT.

The formula first removes duplicates from B2:B20 using UNIQUE, then sorts the unique values with SORT.

3fill in blank

hard

Fix the error in the formula that tries to remove duplicates from C1:C15 but is missing a function.

Excel

=[1](C1:C15)

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

AREMOVE

BUNIQUE

CFILTER

DSORT

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-existent function like REMOVE.

Using FILTER which filters by condition but does not remove duplicates.

The UNIQUE function is used to remove duplicates from a range. REMOVE is not a valid Excel function.

4fill in blank

hard

Fill both blanks to create a formula that removes duplicates from D1:D20 and returns only values greater than 10.

Excel

=FILTER(UNIQUE([1]), [2] > 10)

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

AD1:D20

BC1:C20

CUNIQUE(D1:D20)

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNIQUE inside the second blank which expects a range.

Using the wrong range in either blank.

The UNIQUE function removes duplicates from D1:D20. The FILTER function then returns only those unique values greater than 10 by comparing the range D1:D20 to 10.

5fill in blank

hard

Fill all three blanks to create a formula that removes duplicates from E1:E30, sorts them descending, and returns only values less than 50.

Excel

=FILTER(SORT(UNIQUE([1]), , [2]), [3] < 50)

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

AE1:E30

BFALSE

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE instead of FALSE for descending sort.

Using the wrong range in the filter condition.

The UNIQUE function removes duplicates from E1:E30. SORT sorts the unique values descending by using FALSE for ascending argument. FILTER returns only values less than 50 from the range E1:E30.