Function Problems

How to Limit SORTBY Results to the Top Five Rows in Excel

Olivia MillerOlivia Miller Nov 10, 2026 869 views

Question details

The user wants to sort a dataset and extract only the top five rows from the sorted results using spreadsheet functions.

How to Limit SORTBY Results to the Top Five Rows
Product
Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Working with a large array of data where only the highest or lowest values need to be displayed after sorting.
Observed behavior
The goal is to seamlessly sort the data array and truncate the output to a specific number of rows without manual filtering.
Before you start

Ensure your spreadsheet software supports dynamic array functions like SORTBY and TAKE, which are available in Microsoft 365 and newer versions of WPS Office.

Solution 1Recommended

Use the TAKE and SORTBY Functions Together

Wrap your SORTBY formula inside the TAKE function to automatically extract a specified number of rows from the sorted array.

The SORTBY function sorts a range or array based on the values in a corresponding range or array. By nesting this inside the TAKE function, you can specify exactly how many rows from the top (or bottom) of that sorted list you want to return.

1
Select the target cell

Click on the empty cell where you want the top five results to begin displaying.

2
Input the nested formula

Type the formula: =TAKE(SORTBY(A2:D100, D2:D100, -1), 5). In this example, A2:D100 is your data array, D2:D100 is the column to sort by, -1 sorts in descending order, and 5 tells the TAKE function to return the first 5 rows.

3
Execute the formula

Press Enter. The formula will automatically spill the sorted top 5 rows into the adjacent cells.

Use the TAKE and SORTBY Functions Together
Adjusting the number of rows: You can change the '5' in the formula to any number to return the top 10, top 20, or any other specific number of rows you need.
Efficient Data Analysis

Use WPS Spreadsheet to Handle Dynamic Arrays Easily

WPS Office fully supports modern dynamic array functions like SORTBY and TAKE, allowing you to manipulate and analyze large datasets seamlessly. It offers a familiar interface and excellent compatibility with Excel formulas.

  1. 1. Open your data in WPS: Launch WPS Spreadsheet and open the workbook containing your dataset.
  2. 2. Apply the TAKE and SORTBY formula: In a blank cell, enter your combined =TAKE(SORTBY(...)) formula exactly as you would in Excel.
  3. 3. Press Enter to view results: Hit Enter to let WPS Spreadsheet instantly calculate and spill your top N sorted rows.
Fully compatible with Microsoft Excel formulas and dynamic arrays.Easily extract top or bottom records using modern functions like TAKE and SORTBY.Lightweight software with fast processing for large datasets.Free to use with a familiar, user-friendly interface.
microsoft office alternative - wps office

Frequently Asked Questions

Can I get the bottom 5 rows instead of the top 5?

Yes. To get the bottom rows, use a negative number in the TAKE function. For example, =TAKE(SORTBY(A2:D100, D2:D100, -1), -5) will return the last five rows of your sorted array.

Why is my SORTBY and TAKE formula returning a #NAME? error?

The #NAME? error usually occurs if your version of Excel or spreadsheet software does not support dynamic array functions. Ensure you are using Microsoft 365, Excel for the Web, or the latest version of WPS Office.

How do I sort by multiple columns before taking the top 5?

You can add more arrays and sort orders to the SORTBY function. For instance, =TAKE(SORTBY(A2:D100, D2:D100, -1, C2:C100, 1), 5) sorts by column D descending, then by column C ascending, before returning the top 5 rows.

Does this formula automatically update if my data changes?

Yes, dynamic array formulas automatically recalculate. If the values in your source data (e.g., A2:D100) change, the top five results will dynamically update to reflect the new data without needing a refresh.