How to Limit SORTBY Results to the Top Five Rows in Excel
Question details
The user wants to sort a dataset and extract only the top five rows from the sorted results using spreadsheet functions.

- 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.
Ensure your spreadsheet software supports dynamic array functions like SORTBY and TAKE, which are available in Microsoft 365 and newer versions of WPS Office.
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.
Click on the empty cell where you want the top five results to begin displaying.
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.
Press Enter. The formula will automatically spill the sorted top 5 rows into the adjacent cells.

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. Open your data in WPS: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Apply the TAKE and SORTBY formula: In a blank cell, enter your combined =TAKE(SORTBY(...)) formula exactly as you would in Excel.
- 3. Press Enter to view results: Hit Enter to let WPS Spreadsheet instantly calculate and spill your top N sorted rows.

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.




