Find the Top/Bottom N Values in a List (LARGE, SMALL, SORTN) in Google Sheet

Google Sheets is an incredibly powerful tool for managing and analyzing data, and one of its most useful features is the ability to find the top or bottom N values in a dataset. Whether you’re looking to identify the highest or lowest performers, top-selling products, or the smallest and largest numbers in a financial report, Google Sheets makes it easy with functions like LARGE, SMALL, and SORTN. These functions can save you time and help you make better, data-driven decisions by easily pulling out the most significant values from your list.

In this article, we’ll explain how to use these functions in Google Sheets to find the top or bottom N values in a dataset. We’ll break it down step by step, so even beginners can easily follow along and apply these techniques to their own spreadsheets.

Why Finding the Top/Bottom N Values Is Useful

When working with large datasets, it’s often helpful to focus on the highest or lowest values to make data analysis more manageable. Here’s why finding the top or bottom N values is so important:

• Identify Key Performers: Quickly pinpoint top sales, highest grades, or best-performing team members.
• Spot Trends: Recognize patterns in your data by focusing on the most significant or least significant entries.
• Improve Decision Making: Make informed decisions based on the best or worst performers in your dataset.
• Save Time: Avoid manually sorting and filtering large amounts of data to find the top or bottom entries.

How to Use the LARGE and SMALL Functions in Google Sheets

The LARGE Function: Finding the Largest Values

The LARGE function in Google Sheets allows you to find the Nth largest value in a dataset. For example, if you want to find the top 3 highest sales figures in a list, you can use the LARGE function to return those values. The syntax is:

=LARGE(range, N)

• range: The range of cells containing the data.
• N: The position of the value you want. For example, if you want the 1st largest value, you would use 1, the 2nd largest value would be 2, and so on.

Here’s an example of how to use the LARGE function:

=LARGE(A2:A10, 1)

This formula will return the highest value from the range A2:A10. To find the second highest value, change the second argument to 2:

=LARGE(A2:A10, 2)

The SMALL Function: Finding the Smallest Values

The SMALL function is the opposite of LARGE. It allows you to find the Nth smallest value in a dataset. The syntax is similar:

=SMALL(range, N)

• range: The range of cells containing the data.
• N: The position of the smallest value you want. For example, to find the smallest value, use 1, the second smallest value is 2, and so on.

For instance, to find the 1st smallest value in the range A2:A10, use:

=SMALL(A2:A10, 1)

And to find the 3rd smallest value, use:

=SMALL(A2:A10, 3)

Using the SORTN Function for More Flexibility

If you need more flexibility when finding the top or bottom N values, the SORTN function is a powerful tool. Unlike LARGE and SMALL, which return a single value, SORTN can return multiple values sorted in ascending or descending order.

The SORTN Function Syntax

=SORTN(range, N, [is_ascending], [by_col], [custom_sort])

• range: The range of cells containing the data.
• N: The number of values to return.
• [is_ascending]: TRUE for ascending order or FALSE for descending order. Default is FALSE.
• [by_col]: Set to FALSE to sort by rows (default) or TRUE to sort by columns.
• [custom_sort]: Optional sorting criteria if you need a custom sort.

Example: Using SORTN to Find the Top 5 Values

Suppose you have a list of sales figures, and you want to find the top 5 highest values. Use the following SORTN formula:

=SORTN(A2:A20, 5, FALSE)

This formula will return the top 5 highest values in the range A2:A20, sorted in descending order. If you wanted the bottom 5 values, simply change FALSE to TRUE:

=SORTN(A2:A20, 5, TRUE)

Real-Life Example: Analyzing Student Grades

Imagine you’re a teacher tracking student grades and you want to identify the top and bottom 3 students in terms of performance. Here’s how you can apply these functions:

Sample Student Data

Student	Grade
Alice	95
Bob	85
Carla	75
David	88
Eva	90

To find the top 3 students, you can use the LARGE function:

=LARGE(B2:B6, 1)  // Top 1

=LARGE(B2:B6, 2)  // Top 2

=LARGE(B2:B6, 3)  // Top 3

Alternatively, to identify the bottom 3 students, you can use the SMALL function:

=SMALL(B2:B6, 1)  // Bottom 1

=SMALL(B2:B6, 2)  // Bottom 2

=SMALL(B2:B6, 3)  // Bottom 3

Benefits of Using LARGE, SMALL, and SORTN in Google Sheets

• Time-Saving: Quickly find the top or bottom N values without having to manually sort your data.
• Enhanced Insights: Easily identify key data points, whether you’re analyzing sales, performance, or other metrics.
• Versatility: These functions work for both numerical data and rankings, providing flexibility for various use cases.
• Automation: Use these functions in combination with other Google Sheets tools like conditional formatting and charts to automate data analysis.

Quick Reference Cheat Sheet for LARGE, SMALL, and SORTN

• LARGE Formula: =LARGE(range, N) – Find the Nth largest value in a dataset.
• SMALL Formula: =SMALL(range, N) – Find the Nth smallest value in a dataset.
• SORTN Formula: =SORTN(range, N, [is_ascending]) – Return the top/bottom N values, sorted as desired.

Finding the top or bottom N values in a dataset is a simple yet powerful way to extract meaningful insights from your data in Google Sheets. Whether you’re analyzing student grades, sales figures, or any other type of data, using LARGE, SMALL, and SORTN functions allows you to quickly and efficiently identify key values. With these tools, you can make more informed decisions and save valuable time in your analysis process.