I used to rarely use the SORTN function in Google Sheets, but recently I discovered its immense potential when combined with other functions. This powerful function can solve a variety of problems in Google Sheets that were previously difficult to tackle.
Interestingly, the SORTN function is not as well-known as its counterpart, the SORT function. It seems that many Google Sheets users are not aware of this hidden gem. Additionally, it’s worth mentioning that the SORTN function is currently unavailable in Excel. However, I believe that Excel users will soon have access to this extraordinary function as well.
While there are alternative methods like using Query or a combination of Unique and Sort, they may not always be the most effective solution. The SORTN function, on the other hand, offers unique capabilities such as removing duplicates. If you’re curious to learn more about removing duplicates in Google Sheets, check out this detailed guide on Crawlan.com.
I highly recommend mastering the use of the SORTN function in Google Sheets. It deserves more attention than the SORT function as it provides better data sorting capabilities. In fact, the SORTN formula can even replace other functions like UNIQUE and Array_Constrain to some extent.
Purpose of the SORTN Function in Google Sheets
The SORTN function in Google Sheets is designed to extract the first ‘n’ rows from a dataset after performing a sort. This sort can be in ascending or descending order, allowing you to filter the highest or lowest ‘n’ number of items from a range. Moreover, you have the flexibility to include or exclude duplicate rows from the output.
How to Use the SORTN Function in Google Sheets
While the syntax of the SORTN function may appear complex at first glance, it’s actually quite straightforward. Let’s dive into a few formulas that demonstrate how to use SORTN in Google Sheets.
Syntax and Arguments
The syntax of the SORTN function is as follows:
SORTN(RANGE, [N], [DISPLAY_TIES_MODE], [SORT_COLUMN1, IS_ASCENDING1], ...)
RANGE: The data to be sorted.[N]: The number of rows you want in the sorted output (optional, defaults to 1).[DISPLAY_TIES_MODE]: A number (0 to 3) representing the ties related to duplicates in the sort column.[SORT_COLUMN1, IS_ASCENDING1]: The column index number or range reference to sort, and the sort order.
Formula Examples
Here are some examples of using the SORTN function in Google Sheets.
In the following screenshot, the sample data range is A1:C6. After row 6, I’ve applied a few SORTN formulas.
Formula 1: Sorted Two Rows
=SORTN(A2:C6, 2, 0, C2:C6, FALSE)
This formula sorts the range A2:C6 based on the values in column C in descending order and returns the first two rows. It allows you to obtain the highest two values from Column C.
Formula 2: Sorted Two Rows + One More Identical Row
=SORTN(A2:C6, 2, 1, C2:C6, FALSE)
In this formula, we use tie mode 1. It returns the rows with the highest two values from the range, along with an additional row that has the same value as the second row. This way, we can include more winners with the same score in our output.
Formula 3: Sorted Two Rows Without Duplicates
=SORTN(A2:C6, 2, 2, C2:C6, FALSE)
By using tie mode 2, this formula removes any duplicate rows and returns the top two unique rows from the range. Duplicate removal can be based on specific columns, allowing for more precise data filtering.
Formula 4: Sorted Two Unique Rows + All Duplicates
=SORTN(A2:C6, 2, 3, C2:C6, FALSE)
Tie mode 3 is used in this formula. It returns all duplicate rows of the top two sorted rows. This can be particularly useful when you want to extract all duplicates for further analysis.
I encourage you to create a similar sample sheet and try out these formulas yourself. You can modify the values in column C to see how they affect the results.
In conclusion, the SORTN function in Google Sheets is a valuable tool for sorting and filtering data. By mastering its usage, you can enhance your data analysis capabilities and simplify complex tasks. If you’re interested in learning more about Google Sheets and other helpful features, visit Crawlan.com for additional tutorials and resources.
