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