 Home » Function » Statistics » Select the 5 top-bottom values in Excel

# Select the 5 top-bottom values in Excel

Last Updated on 11/05/2023 by Frédéric LE GUEN

This article will explain how to select the 5 top or bottom values in Excel by formula.

## Presentation of the data

To illustrate how to select the 5 top-bottom values in Excel, we will use this document. Here, we have the number of items sold by our dealers daily.

## Step 1: Sort the data with the function

With the new function SORT, you can sort automatically your data. This function is very simple 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 decreasing)

=SORT(A2:G16,7,-1)

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

## Step 2: Add the CHOOSEROWS function

To extract only a certain number of rows, we need to include the previous formula in the CHOOSEROWS (available only 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

## Step 3: 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.

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

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

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

## Add new values for Friday

Now, we copy the sales of Friday for each dealer. And without changing the formula, we have the following result

## Bottom 5 rows

If you want to return the bottom 5 rows to select the worst dealers, you just have to change the sort order 😉

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

# Select the 5 top-bottom values in Excel

Last Updated on 11/05/2023 by Frédéric LE GUEN

This article will explain how to select the 5 top or bottom values in Excel by formula.

## Presentation of the data

To illustrate how to select the 5 top-bottom values in Excel, we will use this document. Here, we have the number of items sold by our dealers daily.

## Step 1: Sort the data with the function

With the new function SORT, you can sort automatically your data. This function is very simple 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 decreasing)

=SORT(A2:G16,7,-1)

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

## Step 2: Add the CHOOSEROWS function

To extract only a certain number of rows, we need to include the previous formula in the CHOOSEROWS (available only 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

## Step 3: 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.

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

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

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

## Add new values for Friday

Now, we copy the sales of Friday for each dealer. And without changing the formula, we have the following result

## Bottom 5 rows

If you want to return the bottom 5 rows to select the worst dealers, you just have to change the sort order 😉

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