ORDER BY clause in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Sorting a range by a single column

You have a table in range A1:B5 with names in column A and scores in column B. Which formula correctly sorts the table by scores in ascending order?

Google Sheets

A1:B5

Data:
A1: Name, B1: Score
A2: Alice, B2: 85
A3: Bob, B3: 92
A4: Carol, B4: 78
A5: Dave, B5: 90

A =SORT(A2:B5, 2, TRUE)

B =SORT(A2:B5, 1, FALSE)

C =SORT(A2:B5, 2, FALSE)

D =SORT(A2:B5, 1, TRUE)

💡 Hint

Remember, the second argument in SORT is the column index to sort by, and TRUE means ascending order.

The formula =SORT(A2:B5, 2, TRUE) sorts the range by the second column (scores) in ascending order. Other options either sort by the wrong column or in descending order.

📊 Formula Result

intermediate

Sorting by multiple columns

Given a table in A1:C6 with columns Name, Department, and Salary, which formula sorts first by Department ascending, then by Salary descending?

Google Sheets

A1:C6

Data:
A1: Name, B1: Department, C1: Salary
A2: John, B2: Sales, C2: 50000
A3: Jane, B3: HR, C3: 60000
A4: Mike, B4: Sales, C4: 55000
A5: Anna, B5: HR, C5: 58000
A6: Tom, B6: IT, C6: 62000

A =SORT(A2:C6, 2, TRUE, 3, FALSE)

B =SORT(A2:C6, {3,2}, {FALSE, TRUE})

C =SORT(A2:C6, {2,3}, {TRUE, FALSE})

D =SORT(A2:C6, 2, FALSE, 3, TRUE)

💡 Hint

Use arrays for columns and sort orders to sort by multiple columns.

Option C correctly sorts by column 2 (Department) ascending, then column 3 (Salary) descending. Options A and D have wrong syntax, and B reverses the order.

❓ Function Choice

advanced

Choosing the right function for sorting with conditions

You want to sort a list of products by price ascending but only include products with stock greater than 10. Which formula achieves this?

Google Sheets

A1:C6

Data:
A1: Product, B1: Price, C1: Stock
A2: Pen, B2: 1.5, C2: 5
A3: Notebook, B3: 3.0, C3: 15
A4: Eraser, B4: 0.5, C4: 20
A5: Pencil, B5: 1.0, C5: 8
A6: Marker, B6: 2.0, C6: 12

A =FILTER(A2:C6, SORT(C2:C6, 1, TRUE)>10)

B =SORT(FILTER(A2:C6, C2:C6>10), 2, TRUE)

C =SORT(A2:C6, 2, TRUE, C2:C6>10)

D =FILTER(SORT(A2:C6, 2, TRUE), C2:C6>10)

💡 Hint

Filter first, then sort the filtered data.

Option B correctly filters rows where stock > 10, then sorts by price ascending. Option B filters after sorting, which keeps all rows and then filters incorrectly. Options A and C have invalid syntax or logic.

❓ data_analysis

advanced

Analyzing output of SORT with mixed data types

Given the range A1:B5 with names and mixed numeric/text scores, what is the output of =SORT(A2:B5, 2, TRUE)?

Google Sheets

A1:B5

Data:
A1: Name, B1: Score
A2: Alice, B2: 85
A3: Bob, B3: "N/A"
A4: Carol, B4: 78
A5: Dave, B5: 90

A [[Alice, 85], [Carol, 78], [Dave, 90], [Bob, "N/A"]]

B [[Bob, "N/A"], [Carol, 78], [Alice, 85], [Dave, 90]]

C Error: Cannot sort range with mixed data types

D [[Carol, 78], [Alice, 85], [Dave, 90], [Bob, "N/A"]]

💡 Hint

Text values are sorted after numbers when sorting ascending.

SORT places numbers before text when sorting ascending. So rows with numeric scores come first sorted ascending, then rows with text values like "N/A" come last.

🎯 Scenario

expert

Complex sorting with dynamic range and multiple criteria

You have a dynamic list of employees in A1:D (Name, Department, Age, Salary). You want a formula that sorts all employees by Department ascending, then by Salary descending, but only includes employees older than 30. Which formula correctly does this?

Google Sheets

Data example (starting at A1):
Name | Department | Age | Salary
John | Sales      | 28  | 50000
Jane | HR         | 35  | 60000
Mike | Sales      | 40  | 55000
Anna | HR         | 32  | 58000
Tom  | IT         | 45  | 62000

Note: The list can grow or shrink.

A =SORT(FILTER(A2:D, C2:C>30), {2,4}, {TRUE, FALSE})

B =FILTER(SORT(A2:D, {2,4}, {TRUE, FALSE}), C2:C>30)

C =SORT(FILTER(A2:D, C2:C>=30), 2, TRUE, 4, FALSE)

D =SORT(FILTER(A2:D, C2:C>30), 2, TRUE, 4, FALSE)

💡 Hint

Filter first by age > 30, then sort by Department ascending and Salary descending using arrays.

Option A correctly filters employees older than 30, then sorts by Department ascending (column 2) and Salary descending (column 4). Option A filters after sorting, which includes all rows before filtering. Options C and D have incorrect SORT syntax for multiple columns.