How to Use Excel's Small and Large Function

Find the smallest or largest value using these Excel functions

What to Know

  • SMALL function: Type =SMALL( in a cell, choose an array, add a comma, enter the k value, close the parentheses, and press Enter.
  • LARGE function: Select a cell, type =LARGE(, highlight an array, type a comma, add the k value, close the parentheses, and press Enter.

When you want to analyze a broad set of numbers, use the Excel SMALL function and LARGE function to target specific numbers and values in a data set. Here's how to use these functions in Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.

Use the Excel SMALL Function

The SMALL function in Excel returns k-th smallest value (where k- represents the position of the value, for example, first, second, or fifth) in a data set that you determine. You might want to know the first, third, or fifth 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 (for example, first, second, or fourteenth) that the function is searching for among that array of data.

  1. Choose an array of data. This data can run across a single column or row, or it can be spread across multiple rows and columns. You'll define this array in the SMALL function syntax.

    If n is the number of data points in an array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest value.

  2. Select a cell in the spreadsheet to enter the SMALL function. The example used in this tutorial searches for the third smallest number in the data set, so k = 3.

  3. Enter =SMALL( to begin the formula.

  4. Select the array of data. Excel allows you to highlight the data set. When the correct values are selected, Excel names the array (in this case, it's B2:D9).

  5. After you select the data array, enter a comma ( , ) to continue the formula.

  6. Enter the k value. This example uses 3. Type 3, then close the parentheses of the function. The formula should read:

    =SMALL(B2:D9,3)

    Functions and formulas in Excel must start with the equal sign ( = ) before typing the function and parameters.

    An illustration of the SMALL function in Excel.
  7. Press Enter to calculate the function result.

    Illustration of the result of the SMALL function in Excel.
  8. This returns the value 4, which means that out of this array of data, 4 is the third smallest value.

Use the Excel LARGE Function

Conversely, the LARGE function in Excel returns the k-th largest value (where k- represents the position of the value, for example, first largest or fifth largest) 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 (for example, first, second, or fourteenth) the function is searching for among the data array.

  1. Choose an array of data. This data can run across a single column or 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.

  2. Select a cell in the spreadsheet to type the LARGE function. In example searches for the largest number in the data set, so k = 1.

  3. Begin the formula by typing =LARGE(

  4. Select the array of data. Excel allows you to highlight the data set. When the correct values are selected, Excel names the array (in this case, it's B2:D9). Type a comma ( , ) to continue the formula.

  5. Enter the k value. This example uses 1. Type 1, then close the parentheses of the function. The formula should read:

    =LARGE(B2:D9,1)

  6. Press the Enter key to calculate the function result.

    The result of the LARGE function in Excel.
  7. This example searched for the largest number in the array, which is 5111.

If an array is large, you may need to know how many data points are in the set. Highlight the array then look at the bottom of the Excel screen. Count:XX indicates how many pieces of data are in the array, where XX is the number.

Possible Errors in SMALL and LARGE Functions in Excel

Excel formulas have to be exactly right to work. If you encounter an error, here are some things to watch for:

  • If the array is empty, meaning you didn't select 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 an array, SMALL and LARGE return the #NUM! error.
Was this page helpful?