Sorting (single and multi-column) in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Sorting a Single Column Alphabetically

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

Anna, John, Mike, Zoe, Bob

Which formula will sort these names alphabetically in ascending order?

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

B =SORT(A2:A6, 0, TRUE)

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

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

💡 Hint

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

The SORT function syntax is SORT(range, sort_column, is_ascending). Here, the range is A2:A6, sorting by the first column (1), ascending (TRUE).

📊 Formula Result

intermediate

Sorting by Date in Descending Order

You have dates in cells B2:B7:

2023-01-10, 2022-12-25, 2023-03-01, 2022-11-15, 2023-01-01, 2022-12-31

Which formula sorts these dates from newest to oldest?

A =SORT(B2:B7, 2, FALSE)

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

C =SORT(B2:B7, 1, TRUE)

D =SORT(B2:B7, 0, FALSE)

💡 Hint

Descending order means FALSE for the third argument.

SORT(range, sort_column, is_ascending) with is_ascending FALSE sorts descending. Here, sort_column is 1 because B2:B7 is a single column.

📊 Formula Result

advanced

Sorting by Multiple Columns

You have a table with names in column A and scores in column B:

A2:A6: Anna, John, Mike, Zoe, Bob
B2:B6: 85, 92, 85, 92, 85

You want to sort the table first by scores descending, then by names ascending.

Which formula achieves this?

A =SORT(A2:B6, 2, TRUE, 1, FALSE)

B =SORT(A2:B6, 1, TRUE, 2, FALSE)

C =SORT(A2:B6, 2, FALSE, 1, TRUE)

D =SORT(A2:B6, 1, FALSE, 2, TRUE)

💡 Hint

The SORT function can take multiple pairs of sort_column and is_ascending arguments.

SORT(range, sort_column1, is_ascending1, sort_column2, is_ascending2) sorts first by column 2 descending (FALSE), then by column 1 ascending (TRUE).

🎯 Scenario

advanced

Sorting a Table with Dates and Text

You have a table with event names in column A and event dates in column B:

A2:A7: Meeting, Workshop, Seminar, Conference, Webinar, Training
B2:B7: 2023-05-10, 2023-04-15, 2023-05-10, 2023-03-20, 2023-04-15, 2023-05-10

You want to sort the table by date ascending, then by event name ascending.

Which formula will correctly sort this table?

A =SORT(A2:B7, 2, TRUE, 1, TRUE)

B =SORT(A2:B7, 2, FALSE, 1, FALSE)

C =SORT(A2:B7, 1, TRUE, 2, TRUE)

D =SORT(A2:B7, 1, FALSE, 2, TRUE)

💡 Hint

Remember the order of sorting columns and their ascending/descending flags.

SORT(A2:B7, 2, TRUE, 1, TRUE) sorts first by date ascending (column 2), then by event name ascending (column 1).

❓ data_analysis

expert

Analyzing Sort Output with Mixed Data Types

You have this data in columns A and B:

A2:A6: 10, 2, 30, 4, 25
B2:B6: Apple, Banana, Cherry, Date, Elderberry

You apply the formula:

=SORT(A2:B6, 1, TRUE)

What will be the value in cell B3 of the sorted output?

A Apple

B Cherry

C Date

D Banana

💡 Hint

Look at the sorted order of column A and match the corresponding B values.

Sorting column A ascending gives: 2,4,10,25,30. The third smallest number is 10, which corresponds to 'Apple' in column B.

Practice

1. What does the SORT function do in Google Sheets?

easy

A. It arranges data in a specified order based on one or more columns.

B. It deletes duplicate rows from the data.

C. It calculates the sum of selected cells.

D. It changes the font style of the selected cells.

Solution

1. Step 1: Understand the purpose of SORT

   The SORT function is designed to reorder data based on column values.

2. Step 2: Compare with other options

   Deleting duplicates, summing cells, or changing font are different functions, not sorting.

3. Final Answer:

   It arranges data in a specified order based on one or more columns. -> Option A

4. Quick Check:

   SORT = Arrange data order [OK]

Hint: SORT arranges data by columns, not formatting or calculations [OK]

Common Mistakes:

• Confusing SORT with SUM or formatting functions
• Thinking SORT deletes data
• Assuming SORT changes cell styles

2. Which of the following is the correct syntax to sort data in range A2:C10 by the first column in ascending order?

easy

A. =SORT(A2:C10, 1, FALSE)

B. =SORT(A2:C10, TRUE, 1)

C. =SORT(A2:C10, 1, TRUE)

D. =SORT(A2:C10, 2, TRUE)

Solution

1. Step 1: Recall SORT syntax

   The syntax is SORT(range, sort_column_index, is_ascending).

2. Step 2: Match syntax to options

   =SORT(A2:C10, 1, TRUE) uses correct order: range A2:C10, column 1, ascending TRUE.

3. Final Answer:

   =SORT(A2:C10, 1, TRUE) -> Option C

4. Quick Check:

   SORT(range, column, TRUE) = ascending [OK]

Hint: Remember: SORT(range, column_number, TRUE/FALSE) [OK]

Common Mistakes:

• Swapping the order of TRUE and column number
• Using FALSE for ascending order
• Selecting wrong column index

3. Given the data in A1:B4:
Name | Score
Alice | 85
Bob | 92
Carol | 78
What will be the result of =SORT(A2:B4, 2, FALSE)?

medium

A. Bob 92Alice 85Carol 78

B. Carol 78Alice 85Bob 92

C. Alice 85Bob 92Carol 78

D. Bob 92Carol 78Alice 85

Solution

1. Step 1: Understand sorting by second column descending

   Sorting by column 2 (Score) with FALSE means highest to lowest.

2. Step 2: Order the scores

   Scores: Bob 92, Alice 85, Carol 78. Sorted descending: 92, 85, 78.

3. Final Answer:

   Bob 92
   Alice 85
   Carol 78 -> Option A

4. Quick Check:

   SORT by Score descending = highest first [OK]

Hint: FALSE means descending order, highest values first [OK]

Common Mistakes:

• Mixing TRUE and FALSE for ascending/descending
• Sorting by wrong column index
• Ignoring header row in sorting

4. You want to sort data in A2:C10 first by column 2 ascending, then by column 3 descending. Which formula fixes the error in this incorrect formula?
=SORT(A2:C10, 2, TRUE, 3)

medium

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

B. =SORT(A2:C10, 2, TRUE, 3, FALSE)

C. =SORT(A2:C10, 3, FALSE, 2, TRUE)

D. =SORT(A2:C10, 2, TRUE, 3, TRUE)

Solution

1. Step 1: Identify correct multi-column SORT syntax

   Multi-column SORT needs pairs: column index and TRUE/FALSE for each.

2. Step 2: Fix missing TRUE/FALSE for second column

   The original formula misses the order for column 3. =SORT(A2:C10, 2, TRUE, 3, FALSE) adds FALSE for descending.

3. Final Answer:

   =SORT(A2:C10, 2, TRUE, 3, FALSE) -> Option B

4. Quick Check:

   Multi-column SORT needs column and order pairs [OK]

Hint: Always pair column number with TRUE/FALSE in SORT [OK]

Common Mistakes:

• Omitting TRUE/FALSE for each sort column
• Swapping column order and sort order
• Using wrong TRUE/FALSE for desired order

5. You have a list of employees with columns: Name (A), Department (B), and Salary (C). You want to sort the list first by Department ascending, then by Salary descending. Which formula correctly sorts this data in range A2:C100?

hard

A. =SORT(A2:C100, 1, TRUE, 3, FALSE)

B. =SORT(A2:C100, 3, FALSE, 2, TRUE)

C. =SORT(A2:C100, 2, FALSE, 3, TRUE)

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

Solution

1. Step 1: Identify sorting order for each column

   Department (column 2) ascending = TRUE; Salary (column 3) descending = FALSE.

2. Step 2: Match formula with correct column and order pairs

   =SORT(A2:C100, 2, TRUE, 3, FALSE) matches: column 2 TRUE, column 3 FALSE.

3. Final Answer:

   =SORT(A2:C100, 2, TRUE, 3, FALSE) -> Option D

4. Quick Check:

   Sort by Dept ascending, Salary descending = =SORT(A2:C100, 2, TRUE, 3, FALSE) [OK]

Hint: Sort by main column ascending, then secondary descending [OK]

Common Mistakes:

• Reversing sort order for columns
• Sorting by wrong column numbers
• Using inconsistent TRUE/FALSE values