Google SheetsSORT and SORTN functions in Google Sheets
SORT returns a range in sorted order; SORTN returns only the first few rows after sorting.
SORT gives you a sorted copy of your data that re-sorts itself when the data changes. The original stays in the order it was entered. SORTN goes one step further and returns only the top few rows, which makes "top five" lists a one-formula job.
Syntax
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2], ...)=SORTN(range, [n], [display_ties_mode], [sort_column, is_ascending], ...)| Argument | What it is |
|---|---|
range | The data to sort. |
sort_column | The number of the column to sort by, counted from the left of the range, or a separate range of the same height. |
is_ascending | TRUE for A to Z and smallest first. FALSE for the reverse. |
n | For SORTN: how many rows to return. |
display_ties_mode | For SORTN: 0 returns exactly n rows. 1 also returns rows that tie with the last one. 2 removes duplicate rows first. 3 returns n distinct rows with their duplicates. |
Examples
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Item | Category | Price | Stock |
| 2 | A101 | Basmati Rice 5 kg | Grocery | 650 | 40 |
| 3 | A102 | Toor Dal 1 kg | Grocery | 160 | 75 |
| 4 | B201 | Notebook | Stationery | 60 | 300 |
| 5 | B202 | Ball Pen | Stationery | 10 | 0 |
| 6 | C301 | LED Bulb | Electronics | 110 | 200 |
| 7 | C302 | Power Bank | Electronics | 1299 | 18 |
| 8 | D401 | Steel Bottle | Home | 450 | 55 |
=SORT(B2:D8,3,FALSE)Result:Power Bank Electronics 1299 Basmati Rice 5 kg Grocery 650 Steel Bottle Home 450 Toor Dal 1 kg Grocery 160 LED Bulb Electronics 110 Notebook Stationery 60 Ball Pen Stationery 10 By the 3rd column of the range, the price, largest first.
=SORT(B2:C8,2,TRUE,1,TRUE)Result:LED Bulb Electronics Power Bank Electronics Basmati Rice 5 kg Grocery Toor Dal 1 kg Grocery Steel Bottle Home Ball Pen Stationery Notebook Stationery Two levels: by category, then by item name.
=SORT(B2:B8,E2:E8,FALSE)Result:Notebook LED Bulb Toor Dal 1 kg Steel Bottle Basmati Rice 5 kg Power Bank Ball Pen The sort column can be a range outside the data: names in order of stock, largest first.
=SORTN(B2:D8,3,0,3,FALSE)Result:Power Bank Electronics 1299 Basmati Rice 5 kg Grocery 650 Steel Bottle Home 450 SORTN: the top 3 by price.
=SORTN(B2:D8,2,0,3,TRUE)Result:Ball Pen Stationery 10 Notebook Stationery 60 The two cheapest items.
=SORT(UNIQUE(C2:C8))Result:Electronics Grocery Home Stationery With only a range, SORT sorts by the first column, ascending.
How SORT works
After the range, arguments come in pairs: which column to sort by, and TRUE for ascending or FALSE for descending. Add more pairs for further levels. The second example sorts by category and, inside each category, by name.
The sort column can be given in two ways.
- A number: the position of the column inside the range. 1 is its first column.
- A range: any column of the same height, even one that is not part of what you are returning. The third example lists names in order of stock without showing the stock.
With the range alone and nothing else, SORT uses the first column, ascending.
SORTN: the first n rows
SORTN sorts and then keeps only the first n rows. =SORTN(B2:D8, 3, 0, 3, FALSE) reads: from this range, return 3 rows, with tie mode 0, sorted by column 3 descending.
The third argument decides what happens with ties and duplicates.
| Mode | Returns |
|---|---|
| 0 | exactly n rows |
| 1 | n rows, plus any further rows that tie with the nth |
| 2 | n rows after removing duplicate rows |
| 3 | n distinct rows, along with all their duplicates |
Mode 1 is the fair one for a prize list: if two people share third place, both appear.
A sorted, filtered list
SORT wraps naturally around FILTER: =SORT(FILTER(A2:F13, C2:C13="North"), 6, FALSE) shows the North sales, largest first. Remember that the column number then counts inside the filtered result.
A custom order
To sort by your own order, such as Small, Medium, Large, give SORT the position of each value in that list: =SORT(A2:C20, MATCH(B2:B20, {"Small";"Medium";"Large"}, 0), TRUE).
Sorting in random order
=SORT(A2:A20, RANDARRAY(19), TRUE) shuffles a list of nineteen names. It reshuffles whenever the sheet recalculates, so copy the result and paste it as values to keep one order.
Things to know
- Leave the heading row out of the range, or it is sorted in with the data.
- Text sorts without regard to capital letters. Numbers stored as text sort as text, so "100" comes before "20".
- The result occupies the cells below and beside the formula. Typing into them gives
#REF!. - The result cannot be edited. Change the source data.
SORT, the menu, or QUERY?
- Data, Sort range rearranges the real data, once.
- SORT builds a live sorted copy.
- QUERY with
order bysorts and can filter and total in the same formula.
Common mistakes
- TRUE and FALSE the wrong way round. FALSE is descending.
- A column number counted from column A of the sheet, not from the start of the range.
- Expecting SORT to change the original data.
- A sort range of a different height from the data.
In Excel
Excel's SORT is =SORT(array, sort_index, sort_order), where the order is 1 or -1 instead of TRUE or FALSE, and several levels are given as lists in curly brackets. Sorting by an outside range is a separate function there, SORTBY. Excel has no SORTN.
Related functions
- FILTEROnly in Sheets
- UNIQUE and COUNTUNIQUEOnly in Sheets
- QUERYOnly in Sheets
- ARRAYFORMULAOnly in Sheets
The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.
This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.