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 top N number of items from a list in Google Sheets.
My 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 items in Column C in tonnage.
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 sales report. So I’m going to extract top 5 number of items from this data range.
Formula and Result: We can use Google Sheets Query Function for this purpose.
The above Query formula can return the result that we want. If you know the Query formula, you can easily change the number of items to be returned. If you are not familiar with Query, I can explain you how this formula works.
The above formula sorts column C by descending order. So what happens? It would return the items based on their sales volume as I’ve 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 sales volume in Column C. You can change the limit clause row number to 10 to extract top 10 number of items.
To use this formula with your 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 would be like “Select A,B,C,D”.
This’s the possibly easiest method to extract top N number of items from a data range in Google Sheets. You can also possibly get the same result by using Filter or some other Database functions that I didn’t try.