HomeGoogle DocsSpreadsheetExtract Top N Number of Items From a Data Range in Google...

Extract Top N Number of Items From a Data Range in Google Sheets

Published on

I have a sales report in Google Sheets where I want to extract only the top 5 items based on sales volume. Is it possible, and if possible, how to do that? Yup! It’s possible in Google Sheets. Let’s see how to extract the top N number of items from a list in Google Sheets.

My below sample sales report contains sales volume, unit rate and sales amount of some landscaping materials.

From this list, I want to extract the top 5 items based on the volume of sales. You can see the sales volume of each item in column C in tonnage.

Sample Data - Extract N Number of Items in Google Sheets

How to Extract Top N Number of Items from a Data Range in Google Sheets

Here, I’ve only a small amount of data in the sales report. I set it so to enable you to manually match the formula output with the tope N numbers in the dataset.

Here I’m going to extract the top 5 number of items from this data range by using Google Sheets Query Function.

Formula and Result:

=Query(A1:E,"Select A,B,C,D,E where A<>'' Order by C Desc Limit 5")
Query Formula to Extract Top N Number of Items From a Data

The above Query formula can return the result that we want.

If you know the usage of Query formula in Google Sheets, you can easily change the number of items to be returned.

For that, you just want to change the number used in the Limit clause. I mean you can change the “limit 5” to “limit 3” to return the top 3 items.

You May Also Like: What is the Correct Clause Order in Google Sheets Query?

If you are not familiar with the Query, I can explain to you how this formula works.

Formula Explanation

The above formula sorts column C by descending order. So what happens?

It would return the items based on their sales volume as I have the sales volume in Column C.

In the Query formula, I’ve used the “limit” clause to restrict the returned rows to 5. As a result, the formula returns top 5 items based on their sales volume in Column C.

You can change the limit clause row number to 10 to extract the top 10 number of items.

To use this formula with your own data, you may only want to change the data range in the formula like A1:E to your data range.

If your data range is spread across A1:D, then the columns in “Select” clause must be like “Select A,B,C,D” or “Select *”.

This’s the possibly one of the easiest method to extract the top N number of items from a data range in Google Sheets.

As a side note, you can also get the same result by using Filter, Sortn (sorted N rows) or some other formulas in Google Sheets.

Here is the SORTN alternative to Query to extract the top 5 rows. The # 5 in the below formula represents the ‘N’.

=sortn(A2:E,5,0,3,0)

This formula has a feature. If you change the # 0 to 1 in the formula, then it would return the top 5 rows + 1 additional row identical to the fifth row, if any.

Related Articles

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

8 COMMENTS

  1. Thanks so much!

    Is there a way to add a condition to it?

    So, for your example, if you had another column titled “product type,” and you wanted the top quantity sales for all sand products?

    • Hi, Abigail Ryan,

      Assume column F contains the “Product Type.”

      Here are the formulas based on it.

      Query:

      =Query(A1:F,"Select A,B,C,D,E where lower(F)='sand' Order by C Desc Limit 5")

      SORTN:

      =sortn(filter(A2:E,F2:F="Sand"),5,0,3,0)

    • Hi, Brad,

      We can do that. In the below example, cell E1 controls the Query Limit clause.

      =Query(A1:B,"Select A,sum(B) group by A order by sum(B) desc limit "&E1&" label sum(B)'Sales'")

LEAVE A REPLY

Please enter your comment!
Please enter your name here