Select the 5 top-bottom values in Excel

Select the 5 top-bottom values in Excel
Last Updated on 11/05/2023
Reading time: 2 minutes

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.

Amount of unit sold by the dealers

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)

Sort of the data with the SORT function

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)

The 5 top rows of our data

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))

5 top rows with 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

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

top 7 rows

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

New result for the top 5 after adding the data of Friday

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))

Bottom 5 rows of our dealers

Leave a Reply

Your email address will not be published. Required fields are marked *

Select the 5 top-bottom values in Excel

Reading time: 2 minutes
Last Updated on 11/05/2023

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.

Amount of unit sold by the dealers

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)

Sort of the data with the SORT function

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)

The 5 top rows of our data

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))

5 top rows with 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

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

top 7 rows

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

New result for the top 5 after adding the data of Friday

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))

Bottom 5 rows of our dealers

Leave a Reply

Your email address will not be published. Required fields are marked *