The SMALL function is a statistical function which returns the n-th smallest value from the selected dataset. You specify which value you want returned, and it will order the range from smallest to largest then returns the appropriate number.

This is the format of the SMALL function:

=SMALL(VALUE RANGE, N-TH VALUE)

Here is an example of the SMALL function:

=SMALL({9,8,7,5,3,2}, 4) = 7

SMALL function returning the 3rd smallest value from the range

This function is valuable when you are determining ranks, percentile values, or tracking bottom 3, 5, 10, etc… values from a list of results. While simple in nature, it is underrated as a method of evaluating data outputs.

An example of a functional implementation of this would be tracking the performances of salespeople within your company. You want to highlight the bottom ten individuals and return their names so you can work with them to identify what could be the cause of their below average performance, and provide mentoring. You would use the SMALL function to return the tenth lowest value from your list, highlighting the salespeople whose sales metrics were below that amount. Using something like conditional formatting for this makes it a simple process which will adapt as your data is updated.

=SMALL(SALES DATA SET, 10) = tenth lowest sales amount