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.

Practice

1. What does sorting data in Excel help you do?

easy

A. Change the font style of cells

B. Delete duplicate rows automatically

C. Organize data to make it easier to read and analyze

D. Create charts from data

Solution

1. Step 1: Understand the purpose of sorting

   Sorting arranges data in order, such as alphabetically or numerically, to make it easier to find and compare information.

2. Step 2: Identify what sorting does not do

   Sorting does not delete data, create charts, or change formatting like font style.

3. Final Answer:

   Organize data to make it easier to read and analyze -> Option C

4. Quick Check:

   Sorting = Organize data [OK]

Hint: Sorting arranges data to help you see patterns fast [OK]

Common Mistakes:

• Thinking sorting deletes data
• Confusing sorting with formatting
• Believing sorting creates charts

2. Which of these is the correct way to start sorting data by a single column in Excel?

easy

A. Click Insert > Chart, then select the column

B. Select the column, then click Data > Sort, choose the column, and pick ascending or descending

C. Right-click the column and choose Delete

D. Select the column and press Ctrl + C

Solution

1. Step 1: Identify the sorting process

   To sort, you select the data, go to the Data tab, click Sort, then choose the column and order (A-Z or Z-A).

2. Step 2: Eliminate incorrect options

   Options A, C, and D do not start sorting; they relate to charts, deleting, or copying.

3. Final Answer:

   Select the column, then click Data > Sort, choose the column, and pick ascending or descending -> Option B

4. Quick Check:

   Data > Sort = Start sorting [OK]

Hint: Use Data tab > Sort for single column sorting [OK]

Common Mistakes:

• Trying to sort by copying or deleting
• Using Insert tab instead of Data tab
• Not selecting the correct column before sorting

3. You have a table with columns: Name, Department, and Salary. You sort first by Department (A to Z), then by Salary (Largest to Smallest). What will be the order of rows?

medium

A. Rows sorted only by Department alphabetically ignoring Salary

B. Rows sorted only by Salary from highest to lowest ignoring Department

C. Rows sorted randomly without any order

D. Rows grouped by Department alphabetically, and within each Department, sorted by Salary from highest to lowest

Solution

1. Step 1: Understand multi-level sorting order

   Sorting first by Department (A to Z) groups rows by department alphabetically.

2. Step 2: Apply second level sorting by Salary

   Within each Department group, rows are sorted by Salary from largest to smallest.

3. Final Answer:

   Rows grouped by Department alphabetically, and within each Department, sorted by Salary from highest to lowest -> Option D

4. Quick Check:

   Multi-level sort = Group then sort inside group [OK]

Hint: Sort top level first, then next level for layered order [OK]

Common Mistakes:

• Ignoring second level sort
• Sorting only by one column
• Assuming sorting is random

4. You tried to sort a table by two columns but noticed the rows got mixed up and data no longer matched correctly. What is the most likely mistake?

medium

A. You sorted only one column without selecting the entire table

B. You used the filter option instead of sort

C. You sorted by the wrong columns

D. You copied the data instead of sorting

Solution

1. Step 1: Identify the cause of mismatched rows

   If you sort only one column without selecting all related columns, rows get mixed and data mismatches.

2. Step 2: Confirm correct sorting method

   Always select the entire table or all columns before sorting to keep rows intact.

3. Final Answer:

   You sorted only one column without selecting the entire table -> Option A

4. Quick Check:

   Select all data before sorting [OK]

Hint: Always select full table before sorting to keep rows aligned [OK]

Common Mistakes:

• Sorting single column only
• Confusing filter with sort
• Sorting wrong columns accidentally

5. You have a sales report with columns: Region, Salesperson, and Sales Amount. You want to sort the data so that it first shows regions alphabetically, then within each region, salespersons alphabetically, and finally by sales amount from highest to lowest. Which sorting steps should you follow?

hard

A. Sort by Region (A to Z), then Salesperson (A to Z), then Sales Amount (Largest to Smallest)

B. Sort by Sales Amount (Largest to Smallest), then Salesperson (A to Z), then Region (A to Z)

C. Sort by Salesperson (A to Z), then Region (A to Z), then Sales Amount (Smallest to Largest)

D. Sort by Region (Z to A), then Sales Amount (Smallest to Largest), then Salesperson (Z to A)

Solution

1. Step 1: Determine the correct order of sorting levels

   Start sorting with the highest priority column first: Region (A to Z) to group data by region alphabetically.

2. Step 2: Apply second and third level sorting

   Next, sort by Salesperson (A to Z) within each region, then by Sales Amount (Largest to Smallest) within each salesperson group.

3. Final Answer:

   Sort by Region (A to Z), then Salesperson (A to Z), then Sales Amount (Largest to Smallest) -> Option A

4. Quick Check:

   Sort top level first, then next levels in order [OK]

Hint: Sort from broadest to narrowest criteria stepwise [OK]

Common Mistakes:

• Sorting in wrong order
• Sorting sales amount ascending instead of descending
• Starting with lowest priority column