This article will explain how to select the 5 top or bottom values in Excel by formula. You can use 2 techniques.

1. TAKE function
2. CHOOSEROWS and SEQUENCE functions together

Presentation of the data

This document illustrates how to select the top 5 or bottom 5 values in Excel by formula.

Sort the data with the SORT function

Whatever the technique you will choose, the very first step is to sort your data. Now, with Excel 365, you can perform a sort with the SORT function. This way, your sort will be dynamic (the refresh will be automatic). This function is elementary to write.

• Select the whole data to sort (here A2:G16)
• Then, you indicate the column number that will be the sort key (here, the 7th column)
• And finally, the order of the sort (here -1 for descending order)

But at this step, we return all the data of the source of our data

The TAKE function

• This function is available only with Excel 365 or Excel 2021.

This is the simplest way to return the top 5 values in Excel by formula

1. Write the function =TAKE(
2. Select your array of data (here the result of the SORT function)
3. Write the number of values you want to return ( 5 )

Now, if you want the bottom 5, replace the second argument with -5.

The CHOOSEROWS function

The other way is to use the CHOOSEROWS function, also only available with Excel 365.

1. The first argument is our source of data (the result of the SORT function)
2. Then, you write the row number you want to return

=CHOOSEROWS(SORT(A2:G16,7,-1),1,2,3,4,5)

But it's not really convenient to write each row number we want to return

Use SEQUENCE to return the top rows

The SEQUENCE function is very useful to create a series of numbers automatically. So here, we will replace the argument with all the row numbers by the SEQUENCE function.

The use of SEQUENCE is very helpful because you can easily modify the number of rows to return. For instance here, we return the top 7 rows

If you want to return the bottom 5 rows with the CHOOSEROWS function, you must change the sort order of the SORT function. The SEQUENCE argument remains the same.

=CHOOSEROWS(SORT(A2:G16,7,1),SEQUENCE(5))