Google Sheets: How to Extract Top N Values from Range


You can use the following syntax to extract the top N values from a data range in Google Sheets:

=query(A1:C16, "Select A,B,C Order by B Desc Limit 5")

This particular query looks at the data in the range A1:C16 and returns the rows with the 5 highest values in column B.

To extract the top 10 values, simply replace the 5 with a 10 in the formula.

The following example shows how to use this syntax in practice.

Example: Extract Top N Values from Range in Google Sheets

Suppose we have the following dataset in Google Sheets that shows the points and rebounds for 15 different basketball teams:

We can use the following query to extract the rows with the 5 highest points values:

=query(A1:C16, "Select A,B,C Order by B Desc Limit 5")

The query in cell E1 returns the rows with the five highest points values, automatically sorted in descending order.

Note that we can use the following query to extract the rows with the 10 highest points values:

=query(A1:C16, "Select A,B,C Order by B Desc Limit 10")

The query in cell E1 returns the rows with the 10 highest points values, automatically sorted in descending order.

We could also use the following query to simply return the 10 highest points values without any of the other columns:

=query(A1:C16, "Select B Order by B Desc Limit 10")

The query in cell E1 returns the 10 highest points values, automatically sorted in descending order.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Select a Random Sample in Google Sheets
How to Calculate Frequencies in Google Sheets
How to Use Multiple IF Statements in Google Sheets

Leave a Reply

Your email address will not be published.