Check Sheet Logo
Check Sheet

SORTN

The SORTN formula 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 display_ties_mode parameter.

Usage

Use the SORTN formula with the syntax shown below, it has 1 required parameter and 3 optional parameters:

=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)
Parameters:
  1. range (required):
    The range of cells to be sorted.
  2. n (optional):
    The number of rows to return after sorting. If not specified, the formula will return all rows in the sorted range.
  3. display_ties_mode (optional):
    Determines how ties are treated in the sorted data. Valid options are ALL, FIRST, or LAST. If not specified, the default value is ALL.
  4. 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 TRUE for ascending or FALSE for descending. Additional sort column pairs can be added as needed.

Examples

Here are a few example use cases that explain how to use the SORTN formula in Google Sheets.

Sort and filter top results

Use 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

Use SORTN with the ALL 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

Use SORTN with the FIRST or LAST display_ties_mode parameter to sort and exclude tied results based on a specified number of rows and one or more sort columns.

Common Mistakes

SORTN not working? Here are some common mistakes people make when using the SORTN Google 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.

Related Formulas

The following functions are similar to SORTN or are often used with it in a formula:

  • SORT

    The SORT formula 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.

  • FILTER

    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.

  • QUERY

    The QUERY function 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.

  • INDEX

    The INDEX formula 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.

Learn More

You can learn more about the SORTN Google Sheets function on Google Support.