The SORTN function lets you sort a range of data and return the first n items from that range. The function creates a new range of data with the new, sorted output from the function. SORTN can significantly reduce the number of steps it takes to get the data you need out of a table.
SORTN is similar to FILTER, which filters a specified range, but SORTN returns the highest n amounts while FILTER returns the matching rows.
Excel, the most popular spreadsheet program, does not have this function.
Contents
Purpose
This function returns n number of rows from a range of data.
Related Functions
SORT – Outputs a sorted range (or array) of data
FILTER – Outputs a range of data, including only items meeting specified criteria
UNIQUE – Outputs data with duplicates removed
QUERY – Can return a specified number of rows as one of its capabilities
MAX – Returns the largest number from a dataset
MIN – Returns the smallest number from a dataset
LARGE – Returns the nth largest number from a dataset
SMALL – Returns the nth smallest number from a dataset
RANK.EQ or RANK.AVG – Ranks a value in a dataset
Errors
#REF! – “Array result was not expanded because it would overwrite data in…” This error occurs when there are values in the cells either below or to the right blocking the output. Delete or move that data to resolve the error.
#VALUE! – The input should be one contiguous range of cells such as A1:B7 and not A1:B4,A5:B7.
Syntax
=SORTN(range,[n],[display_ties_mode],[sort_column1, is_acending1],...)
range– The range of data for the function to sortn– The number of items to returndisplay_ties_mode [OPTIONAL]– An optional number designating how to display ties.- 0: Show the first n rows in the sorted range.
- 1: Show the first n rows, plus any additional rows identical to the nth row.
- 2: Show the first n rows after removing duplicate rows.
- 3: Show the first n unique rows, but show every duplicate of these rows.
sort_column1 [OPTIONAL]– The column with the criteria you are sorting. It can only be one column at a time. If this value is not specified, the function will use the lowest index column in the range and subsequent columns if a tie-breaker is needed.is_ascending1 [OPTIONAL]– Two possible answers – TRUE for ascending (1,2,3), FALSE for descending (3,2,1)sort_column2 - [OPTIONAL]– Add as many of these as you want.
⚠️ This function’s output can spill below, and sometimes to the right, of its cell.
Tip: If you omit sort_column1 and is_ascending1, the function performs the sort on the lowest-index column in the range, with subsequent columns used to sort if there are ties.
Examples
Example 1
First, we use a simple SORTN function with one column of data and extract the two highest values. We set the display_ties to 0, which means the function should show the two highest values regardless of ties. The zero isn’t specified, but it is the default. Not setting zero is the same as typing “0”.
| A | |
| 1 | 2 |
| 2 | 5 |
| 3 | 3 |
| 4 | 3 |
| 5 | =SORTN(A1:A4,2) |
| 6 |
| A | |
| 1 | 2 |
| 2 | 5 |
| 3 | 3 |
| 4 | 3 |
| 5 | 2 |
| 6 | 3 |
Example 2
We have changed the display_ties parameter to “1” in this example. The “1” tells Sheets to show the two highest values, including ties. The two first values are 2, including all three occurrences of 2.
| A | |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
| 7 | =SORTN(A1:A6,2,1) |
| 8 | |
| 9 |
| A | |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
| 8 | 2 |
| 9 | 2 |
Example 3
We have set the display_ties parameter to 2, which means the function should show only two rows at most but not any duplicates.
| A | |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
| 7 | =SORTN(A1:A6,2,2) |
| 8 |
| A | |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
| 8 | 3 |
Example 4
We have set the display_ties parameter to 3, which means it should show only two unique values but show all duplicates of these items. 2 and 3 are the two highest items, so it shows all 2s and 3s no matter how many there are. Note: It seems that this function should also be showing all of the threes, but it is not. I submitted this as a question on the Google Docs forum and will update these notes with what I find out.
| A | |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
| 7 | =SORTN(A1:A6,2,3) |
| 8 | |
| 9 | |
| 10 | |
| 11 |
| A | |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
| 8 | 2 |
| 9 | 2 |
| 10 | 3 |
| 11 | 3 |
Example 5
Two-column SORTN, only sorted one column. Sorting without all the columns is typically unintentional and can create problems by mismatching the data in the rows.
| A | B | |
| 1 | Hen | Brown |
| 2 | Sheep | White |
| 3 | Bull | Brown |
| 4 | Goat | White |
| 5 | =SORTN(A1:A4,2,1) |
|
| 6 | ||
| A | B | |
| 1 | Hen | Brown |
| 2 | Sheep | White |
| 3 | Bull | Brown |
| 4 | Goat | White |
| 5 | Bull | |
| 6 | Goat |
Example 6
Two-column SORTN, specifying both columns in the range. This output is more likely what you wanted instead of option 5.
| A | B | |
| 1 | Hen | Brown |
| 2 | Sheep | White |
| 3 | Bull | brown |
| 4 | Goat | White |
| 5 | =SORTN(A1:B4,2,1) |
|
| 6 | ||
| A | B | |
| 1 | Hen | brown |
| 2 | Sheep | White |
| 3 | Bull | brown |
| 4 | Goat | white |
| 5 | Bull | brown |
| 6 | Goat | white |
Live Examples in Sheets
Go to this spreadsheet for several examples of the SORTN function that you can study and use anywhere you want.
Videos
