Analyzing a broad set of numbers takes time, but Microsoft Excel makes it a lot easier. The Excel SMALL function and LARGE function can help you target specific numbers and values in your data set. Here's how to use them.
Instructions in this article apply to Excel for Office 365 and Excel 2019, 2016, 2013, and 2010.
Using the Excel SMALL Function
The SMALL function in Excel will return kth smallest value (where k represents the position of the value, e.g., first, second, fifth, etc.) in a data set, that you, as the user, determine. You might want to know the 1st, 3rd, or 5th smallest value. The purpose of this function is to return values with a particular relative standing in a data set.
The SMALL function is written as SMALL(array, k) where array is the range of data you want to examine, and k is the userdefined point (first, second, fourteenth...) that the function is searching for among that array of data.

First, you'll need an array of data. This data can run across a single column/row, or it can be spread across multiple rows and columns. You'll need to define this array in the SMALL function syntax.
If n is the number of data points in array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest value.

Select a cell in your spreadsheet to type the SMALL function. In this case, we are searching for the third smallest number in the data set, so k = 3.
Begin the formula by typing: =SMALL(
At this point, you need to select your array of data. Excel will allow you to highlight your data set. When the correct values are selected, Excel will name the array (in this case it's B2:D9).
Once you've selected your data array, then enter , (comma) to continue the formula.
Next, you need to enter your k value, in this example, we're using 3. Type 3, then close the parentheses of the function. Your formula should read:
=SMALL(B2:D9,3)
Don't forget that all functions and formulas in Excel must start with = (the equal sign) before typing the function and parameters.

Press Enter to calculate the function result.

This returns the value 4, which means that out of this array of data "4" is the 3rd smallest value.
Using the Excel LARGE Function
Conversely, the LARGE function in Excel will return the kth largest value (where k represents the position of the value, e.g., first largest, fifth largest, etc.) that you determine in a data set.
The LARGE function is written as LARGE(array, k) where array is the range of data you want to examine, and k is the userdefined point (first, second, fourteenth...) the function is searching for among the data array.

First, you need an array of data. This data can run across a single column/row, or it can be spread across multiple rows and columns. You'll need to define this array in the LARGE function syntax.
If n is the number of data points in the array, LARGE(array,1) equals the largest value, and LARGE(array,n) equals the largest value.

Select a cell in your spreadsheet to type the LARGE function. In this case, we are searching for the largest number in the data set, so k = 1.
Begin the formula by typing =SMALL(
Then select your array of data. Excel will allow you to highlight your data set. When the correct values are selected, Excel will name the array (in this case, it's B2:D9). You can now type , (comma) to continue the formula.
Next, enter your k value; in this example, we're using 1. Type 1, then close the parentheses of the function. Your formula should read:
=LARGE(B2:D9,1)

Press the Enter key to calculate the function result.

In this case, we searched for the largest number in the array. We can quickly verify just by looking that yes, 5111 is the largest value in the data set.
If your array is very large, you may need to know how many data points you have in the set. Highlight your array then look in the bottom of the Excel screen. Count:XX will tell you how many pieces of data are in your array, where XX will be the number.
Possible Errors in Using SMALL and LARGE Functions in Excel
Excel formulas have to be exactly right to work. If you have an error, here are some things to watch for:
 If array is empty, meaning you haven't selected cells that contain data, SMALL and LARGE functions return the #NUM! error
 If k ≤ 0 or if k exceeds the number of data points within your array then SMALL or LARGE will return the #NUM! error