Do you want to find the top 5 values or the last 5 values from a list in Excel? The TAKE function in Excel 365 makes this task easy when combined with the SORT function. This article explains how to use both functions step by step.
Why TAKE Alone is Not Enough
The TAKE function lets you extract specific rows or columns from a range. Its syntax is quite simple:
- The array (your table with the values)
- The number of rows to extract
- [Optional] The number of columns to extract
=TAKE(array, rows_to_extract, columns_to_extract)
If you use the TAKE function directly without sorting, it will only return the first 5 or last 5 rows of your data range, not necessarily the top 5 values. For instance, imagine you want to return the 5 employee names with the highest sales:
To find the top 5 values, you must first organize your data using the SORT function. Without sorting, the TAKE function will only select rows based on their position, not their value.
Steps to Select the Top 5 Values
The idea here is to create a virtual table ordered by the total column and then extract the top 5 rows:
- Write the SORT function.
- Indicate the column to sort (e.g., the 7th column).
- Order in decreasing order (-1).
- Extract the top 5 rows from this virtual table using the TAKE function.
Select the Bottom 5 Values
You can also select the last 5 rows using the TAKE function by writing a negative value for the second argument:
Key Points to Remember
- Combine SORT and TAKE to manage data dynamically.
- Positive numbers in the TAKE function select rows from the top.
- Negative numbers select rows from the bottom.
