Custom sorting rules in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sort the range A1:A10 in ascending order.

Excel

=SORT(A1:A10, [1], TRUE)

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

A2

B1

C0

D-1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or 2 as the sort index causes errors or unexpected results.

Leaving the sort index blank.

The second argument in SORT specifies the column to sort by. Since we have a single column range, use 1.

2fill in blank

medium

Complete the formula to sort the range B2:C10 by the second column in descending order.

Excel

=SORT(B2:C10, [1], FALSE)

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

A0

B3

C2

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 instead of 2 sorts by the first column.

Using 0 or 3 causes errors.

The second argument is the column index to sort by within the range. Here, column 2 means the second column of B2:C10.

3fill in blank

hard

Fix the error in the formula to sort the range A1:A5 by a custom list order: "High", "Medium", "Low".

Excel

=SORTBY(A1:A5, MATCH(A1:A5, [1], 0))

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

A{"High", "Medium", "Low"}

B("High", "Medium", "Low")

C["High", "Medium", "Low"]

DHigh, Medium, Low

Attempts:

3 left

💡 Hint

Common Mistakes

Using square brackets or parentheses instead of curly braces.

Not quoting text values inside the array.

Custom lists in Excel formulas use array constants with curly braces and double quotes for text.

4fill in blank

hard

Fill both blanks to sort range B1:B6 by a custom list "Red", "Green", "Blue" and then by values ascending.

Excel

=SORTBY(B1:B6, MATCH(B1:B6, [1], 0), [2])

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

A{"Red", "Green", "Blue"}

BTRUE

C1

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE/FALSE instead of 1/0 for sort order in SORTBY.

Using square brackets for the custom list.

The MATCH function uses a custom list array in curly braces. The third argument of SORTBY is the sort order: 1 for ascending.

5fill in blank

hard

Fill all three blanks to create a formula that sorts range A1:A8 by a custom list "Small", "Medium", "Large", then by values descending, and finally by the original order ascending.

Excel

=SORTBY(A1:A8, MATCH(A1:A8, [1], 0), [2], SEQUENCE(ROWS(A1:A8)), [3])

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

A{"Small", "Medium", "Large"}

B0

C1

D-1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 instead of -1 for descending order.

Mixing up the order of sort orders.

The custom list is an array constant in curly braces. The second sort order is -1 for descending. The third sort order is 1 for ascending.