SORTNformula is used to sort a range of data based on one or more columns and return a specified number of rows. It is commonly used to filter and sort large sets of data to quickly identify the top or bottom results. The formula can also handle ties in the data by either displaying all tied rows or only the first or last tied row based on the
- How to use
- Examples of using
SORTNformula not working?
- Similar formulas to
SORTN formula with the syntax shown below, it has 1 required parameter and 3 optional parameters:
- range (required):
The range of cells to be sorted.
- n (optional):
The number of rows to return after sorting. If not specified, the formula will return all rows in the sorted range.
- display_ties_mode (optional):
Determines how ties are treated in the sorted data. Valid options are
LAST. If not specified, the default value is
- sort_column1, is_ascending1 (optional):
One or more pairs of sort columns and sort orders, separated by commas. The sort column can be either a column number or a range of cells, and the sort order is specified as either
TRUEfor ascending or
FALSEfor descending. Additional sort column pairs can be added as needed.
ExamplesHere are a few example use cases that explain how to use the
SORTNformula in Google Sheets.
Sort and filter top results
SORTN to quickly sort and filter a large dataset to show only the top results based on a specified number of rows and one or more sort columns.
Sort and display tied results
SORTN with the
display_ties_mode parameter to sort and display all tied results based on a specified number of rows and one or more sort columns.
Sort and exclude tied results
SORTN with the
display_ties_mode parameter to sort and exclude tied results based on a specified number of rows and one or more sort columns.
SORTNnot working? Here are some common mistakes people make when using the
SORTNGoogle Sheets Formula:
Incorrect range format
One common mistake is not properly formatting the range argument. Ensure that the range is in the correct format, such as A1:B10.
Incorrect use of optional arguments
Another common mistake is using the optional arguments incorrectly. Make sure to follow the correct syntax and provide the correct arguments in the correct order.
Incorrect use of sort_column and is_ascending
Users may also make mistakes when using the sort_column and is_ascending arguments. Double-check that these arguments are being used correctly and providing the desired sort order.
The following functions are similar to
SORTN or are often used with it in a formula:
SORTformula in Google Sheets is used to sort a range of data based on one or more columns. This function can be used to sort data in ascending or descending order, and can also sort by multiple columns. The sorted data can be used for further analysis or presentation.
The FILTER formula in Google Sheets is used to filter a range of data based on specified conditions. It returns a new range that only includes the rows that meet the specified conditions. This formula is commonly used to extract specific data from a larger dataset.
QUERYfunction in Google Sheets is used to query data from a specified dataset. The function allows users to filter and sort data based on specific criteria. The function is commonly used in data analysis and reporting, as well as in creating dynamic dashboards.
INDEXformula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.
You can learn more about the
SORTN Google Sheets function on Google Support.