How to Use Excel’s Small and Large Function

Find the smallest or largest value using these Excel functions

Analyzing a large 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.

Note: These instructions are valid for most versions of Excel, including 2016, 2019, and 365.

Using the Excel SMALL Function

The SMALL function in Excel will return k-th smallest value (where k- represents the position of the value, i.e. 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 user-defined point (first, second, fourteenth…) that the function is searching for among that array of data.

Here’s how to get it done:

–> 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, they , (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)

Important: 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 k-th largest value (where k- represents the position of the value, i.e. 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 user-defined 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 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.

Tip: 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