Extracting the Largest and Smallest Values in a Dataset

Retrieving the maximum and minimum values is done relatively easily in Excel using the MIN() and MAX() functions, but what if you wanted to extract the five largest or smallest values?

Two lesser-known functions can help you achieve this: SMALL and LARGE. Both functions work the same way and have the same arguments: (array, k)

In the first argument (array), you select the data you want to pull your value from, and the second argument (k) is how large or small it is with respect to the data (e.g. a value of 5 would give you the fifth largest or smallest value, depending on whether you are using the LARGE or SMALL function).

You could recreate the MAX and MIN results by placing a value of 1 in the second argument, for example:

=SMALL(A:A,1) would return the smallest (minimum) value in column A
=LARGE(A:A,1) would return the largest (maximum) value in column A

However, the real value in using the SMALL and LARGE functions is being able to pull the next smallest, or largest number.

You can use the ROW() function to help you do this.

If you needed to pull the 10 largest or smallest values from a range of data, rather than manually changing the k values manually, what you can do is use the ROW() function, and use ROW(A1) in the first argument and then drag the formula down.

The relative references will change the value to A2, A3, A4, etc. Now whether you use A or B or C as the column doesn’t matter since the formula is only going to calculate the row number.

The formulas might look something like this, assuming you data is in column A:

The value for ROW(A1) is 1, and for ROW(A2) it is 2, so you can see how just by dragging this formula down you can easily increment your formula and pull the next largest or smallest number.

The above example would get you the three smallest values in the data set, whereas the following would return you the three largest: