SORT and SORTN functions in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to sort the range A1:B5 by the first column in ascending order.

Google Sheets

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

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

A2

B3

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 as the column number causes an error.

Using 2 sorts by the second column, not the first.

The first column is column 1, so we use 1 to sort by it.

2fill in blank

medium

Complete the code to sort the range A1:C10 by the third column in descending order.

Google Sheets

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

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

A2

B1

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 2 sorts by the second column instead of the third.

Using TRUE sorts in ascending order, not descending.

We want to sort by the third column, so the second argument is 3.

3fill in blank

hard

Fix the error in the formula to get the top 3 values from range A1:A10 sorted descending.

Google Sheets

=SORTN(A1:A10, [1], 0, 1, FALSE)

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

A3

B2

C1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 returns only the top value, not top 3.

Using 0 returns all values, not limited to top 3.

The second argument is the number of items to return, so use 3 for top 3 values.

4fill in blank

hard

Fill both blanks to sort range B2:D8 by the second column ascending and then the third column descending.

Google Sheets

=SORT(B2:D8, [1], TRUE, [2], FALSE)

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

A2

B1

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up column numbers causes wrong sorting order.

Using TRUE for descending order is incorrect.

The first sort is by column 2 ascending, the second sort is by column 3 descending.

5fill in blank

hard

Fill all three blanks to get the top 5 unique values from A1:A20 sorted descending.

Google Sheets

=SORTN(A1:A20, [1], [2], 1, [3])

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

A5

B1

CFALSE

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 for display_ties_mode includes duplicates.

Using TRUE sorts ascending, not descending.

We want 5 items, unique display mode (1), and descending order (FALSE).

Practice

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

easy

A. It arranges all data rows by one or more columns.

B. It picks only the top 3 rows from a data range.

C. It filters data based on a condition.

D. It sums up values in a column.

Solution

1. Step 1: Understand the purpose of SORT

   The SORT function organizes data rows by sorting them according to one or more columns.

2. Step 2: Compare with other functions

   Unlike functions that filter or sum data, SORT rearranges all rows based on column values.

3. Final Answer:

   It arranges all data rows by one or more columns. -> Option A

4. Quick Check:

   SORT = Arrange rows by column [OK]

Hint: SORT rearranges all rows by column values [OK]

Common Mistakes:

• Confusing SORT with filtering functions
• Thinking SORT picks only top rows
• Assuming SORT sums values

2. Which of the following is the correct syntax for the SORTN function to get the top 5 rows sorted by column B ascending from range A1:C10?

easy

A. SORTN(A1:C10, 5, 0, 2, TRUE)

B. SORTN(A1:C10, 5, 0, 2, FALSE)

C. SORTN(A1:C10, 5, 1, 2, TRUE)

D. SORTN(A1:C10, 5, 0, 1, TRUE)

Solution

1. Step 1: Identify SORTN parameters

   SORTN(range, n, display_ties_mode, sort_column, is_ascending) picks top n rows sorted by a column.

2. Step 2: Match parameters to question

   We want top 5 rows (n=5), no tie display (0), sort by column 2 (B), ascending (TRUE).

3. Final Answer:

   SORTN(A1:C10, 5, 0, 2, TRUE) -> Option A

4. Quick Check:

   SORTN syntax with correct params [OK]

Hint: Remember SORTN(range, n, 0, col, TRUE) for top n ascending [OK]

Common Mistakes:

• Using 1 instead of 0 for display_ties_mode
• Sorting by wrong column number
• Setting ascending to FALSE when ascending needed

3. Given the data in A1:B5:
A1:B5
1, 50
2, 40
3, 60
4, 30
5, 70
What is the result of =SORT(A1:B5, 2, TRUE)?

medium

A. [5, 70], [3, 60], [1, 50], [2, 40], [4, 30]

B. [4, 30], [1, 50], [2, 40], [3, 60], [5, 70]

C. [1, 50], [2, 40], [3, 60], [4, 30], [5, 70]

D. [4, 30], [2, 40], [1, 50], [3, 60], [5, 70]

Solution

1. Step 1: Understand SORT parameters

   SORT(range, sort_column, is_ascending) sorts rows by the specified column ascending if TRUE.

2. Step 2: Sort data by column 2 ascending

   Column 2 values: 50, 40, 60, 30, 70. Sorted ascending: 30, 40, 50, 60, 70. Corresponding rows: [4,30], [2,40], [1,50], [3,60], [5,70].

3. Final Answer:

   [4, 30], [2, 40], [1, 50], [3, 60], [5, 70] -> Option D

4. Quick Check:

   SORT by column 2 ascending = sorted rows [OK]

Hint: Sort by column values ascending to reorder rows [OK]

Common Mistakes:

• Sorting by wrong column
• Assuming descending order when TRUE means ascending
• Mixing row order with original data

4. You wrote =SORTN(A1:A10, 3, 0, 2, TRUE) but it returns an error. What is the likely problem?

medium

A. The range A1:A10 has fewer than 3 rows.

B. The ascending parameter must be FALSE, not TRUE.

C. The sort column number 2 is outside the range columns.

D. The display_ties_mode parameter cannot be 0.

Solution

1. Step 1: Check range and sort column

   The range A1:A10 has 1 column (A). Sort column 2 is outside the range columns, causing an error.

2. Step 2: Rule out other options

   Fewer than 3 rows (A) returns available rows without error; TRUE (B) is valid for ascending; 0 (C) is valid for display_ties_mode.

3. Final Answer:

   The sort column number 2 is outside the range columns. -> Option C

4. Quick Check:

   Sort column must be within range columns [OK]

Hint: Check if sort column number fits range columns [OK]

Common Mistakes:

• Using sort column number larger than columns in range
• Confusing display_ties_mode values
• Assuming TRUE must be FALSE for ascending

5. You have sales data in A2:C20 with columns: Product, Region, Sales. You want to find the top 3 sales amounts but only for the "East" region. Which formula correctly sorts and filters this data?

hard

A. =SORTN(A2:C20, 3, 0, 3, FALSE)

B. =SORTN(FILTER(A2:C20, B2:B20 = \"East\"), 3, 0, 3, FALSE)

C. =SORTN(FILTER(A2:C20, B2:B20 = \"East\"), 3, 0, 3, TRUE)

D. =SORT(FILTER(A2:C20, B2:B20 = \"East\"), 3, FALSE)

Solution

1. Step 1: Filter data by region "East"

   Use FILTER(A2:C20, B2:B20 = "East") to get only rows where Region is East.

2. Step 2: Use SORTN to get top 3 sales descending

   SORTN(filtered_data, 3, 0, 3, FALSE) picks top 3 rows sorted by Sales (column 3) descending (FALSE).

3. Final Answer:

   =SORTN(FILTER(A2:C20, B2:B20 = "East"), 3, 0, 3, FALSE) -> Option B

4. Quick Check:

   Filter then SORTN top 3 descending sales [OK]

Hint: Filter first, then SORTN top n descending [OK]

Common Mistakes:

• Sorting ascending instead of descending
• Not filtering before sorting
• Using SORT instead of SORTN for top n