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)
- The first argument is our source of data (the result of the SORT function)
- 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))
