When you are working with large quantities of data in Excel you'll often want to know which are the largest and smallest values in a range. For example, if you're analyzing student or sports results you'll need to know the fastest or slowest times or the best (and worst) score.
Excel has functions you can use to automatically extract these values from a range. In this article, I'll show you some ways to quickly and easily rank and extract data from an Excel range.
»
Excel: Filter and Sort
There are a number of ways to extract the largest and smallest values in a range, one of which is using a filtered list. To do this, select the data area, including the headings, and from the Home tab, choose Sort & Filter > Filter. This creates drop-down filter indicators to the right of each column heading in the list.
Use these filter indicators to filter the data. For example, to locate the highest values in the list click the down-pointing arrow on the column heading for the column you wish to extract data from. From the menu, choose Number Filters > Top 10. Top 10 does more than just find the top ten values — you can use it to locate the bottom values as well. And you don't have to find just 10 — you can look for more or less than this.
You can, for example, locate the three smallest values by selecting Bottom and set the value to 3. Click OK and the list will show the three smallest values. You can then sort these in order by choosing Sort A to Z (or Sort Z to A) from the right click menu for the column to sort on.
When you're done you can clear the filter by choosing Sort & Filter > Clear.
»
Large and Small Functions
To locate the largest value in a range use the LARGE formula. This takes the range and the value that you want to extract. So, for example, to extract the largest value in the range B4 to B18, write this formula:
=LARGE(B4:B18,1)
This extracts the largest value, and to extract the second largest value, replace the 1 with a 2; to extract the third largest value, replace the 1 with a 3 and so on.
The function SMALL does the same thing for the smallest value in a range. So, this formula will find the third smallest value in the range B4:B18:
=SMALL(B4:B18,3)
If you only want the largest value in a range you can use the MAX command. So, for example, this formula returns the largest value in the range B4 to B18:
=MAX(B4:B18)
Use the MIN function in the same way to find the smallest value in the range.
