Sorting data (single and multi-level) in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Sorting a list of names alphabetically

You have a list of names in cells A2:A6:

Anna, John, Mike, Zoe, Beth

Which formula in cell B2 will sort these names alphabetically from A to Z?

A =SORT(A2:A6, 0, 1)

B =SORT(A2:A6, 1, -1)

C =SORT(A2:A6, 2, 1)

D =SORT(A2:A6, 1, 1)

💡 Hint

The SORT function sorts data by column number and order: 1 for ascending, -1 for descending.

Option D sorts the range A2:A6 by the first column in ascending order (A to Z). Option D sorts descending (Z to A). Options A and C use invalid column numbers for this range.

📊 Formula Result

intermediate

Sorting data by date in descending order

You have dates in cells B2:B7:

1/1/2023, 3/15/2023, 2/10/2023, 4/5/2023, 1/20/2023, 3/1/2023

Which formula will sort these dates from newest to oldest?

A =SORT(B2:B7, 1, -1)

B =SORT(B2:B7, 1, 1)

C =SORT(B2:B7, 2, -1)

D =SORT(B2:B7, 0, -1)

💡 Hint

Descending order means newest dates first.

Option A sorts the dates in B2:B7 by the first column in descending order (-1), showing newest dates first. Option A sorts ascending (oldest first). Options C and D use invalid column numbers.

🎯 Scenario

advanced

Multi-level sorting of sales data

You have a table with columns:
Product (A2:A10), Region (B2:B10), Sales (C2:C10).

You want to sort the data first by Region (A to Z), then by Sales (largest to smallest).

Which formula will do this correctly?

A =SORT(A2:C10, {2,3}, {-1,1})

B =SORT(A2:C10, {1,3}, {1,-1})

C =SORT(A2:C10, {2,3}, {1,-1})

D =SORT(A2:C10, 2, 1, 3, -1)

💡 Hint

Use arrays for columns and sort orders in SORT for multi-level sorting.

Option C sorts by column 2 (Region) ascending, then column 3 (Sales) descending. Option C sorts by Product then Sales, which is wrong. Option C reverses sort orders. Option C is invalid syntax.

❓ Function Choice

advanced

Choosing the right function for multi-level sorting

You want to sort a table by two columns: Date (oldest first) and Amount (largest first).

Which Excel function and syntax will achieve this?

A SORT(data_range, {1,2}, {1,-1})

B SORTBY(data_range, Date_column, 1, Amount_column, -1)

C SORT(data_range, 1, 1, 2, -1)

D SORTBY(data_range, Amount_column, -1, Date_column, 1)

💡 Hint

SORTBY lets you specify columns and sort orders separately.

Option B uses SORTBY with Date ascending (1) then Amount descending (-1). Option B uses SORT assuming Date is column 1 and Amount column 2. Option B is invalid syntax. Option B reverses the order of sorting columns.

❓ data_analysis

expert

Analyzing sorted data output

You have this data in A1:C5:

| Name | Score | Date |
| Anna | 85 | 1/5/2023 |
| John | 92 | 1/3/2023 |
| Mike | 85 | 1/4/2023 |
| Zoe | 92 | 1/2/2023 |

You apply this formula:
=SORT(A2:C5, {2,3}, {-1,1})

What is the order of names in the sorted output?

Excel

=SORT(A2:C5, {2,3}, {-1,1})

A Zoe, John, Mike, Anna

B John, Zoe, Anna, Mike

C John, Zoe, Mike, Anna

D Zoe, John, Anna, Mike

💡 Hint

Sort by Score descending, then Date ascending.

Scores sorted descending: 92, 92, 85, 85.
For ties, Date ascending: Zoe(1/2) before John(1/3).
For 85 scores: Mike(1/4) before Anna(1/5).
Correct order: Zoe, John, Mike, Anna.