Here’s an overview of the small function being used in a sample dataset.

**Introduction to the SMALL Function**

**Function Objective:**

**The function is used to find the k-th**** smallest value in a data set.**

**Syntax:**

**=SMALL(array, k)**

**Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

array |
Required |
Range of cells or an array containing numerical data only. |

k |
Required |
An integer that defines the position of the smallest value in the selected array, for example- 3^{rd} smallest. |

**Return Parameter:**

**k-th**** numerical value from the selected array.**

**Using the SMALL Function in Excel: ****4 Common Examples**

**Example 1 – Using the SMALL Function to Find the Smallest Values in Excel**

Column **B** has some numerical values in decimals. In column** D**, we’ll find the smallest 3 values.

- In cell
**D7**, the formula to find the smallest value will be:

`=SMALL(B5:B14,1)`

- By replacing the value of
**k**in the second argument of the function, you’ll be able to get the 2nd and 3rd smallest values in cells**D8**and**D9,**respectively.

**Example 2 – Use the SMALL Function to Extract the Earliest Times or Dates in Excel**

Column** B** has a number of timestamps, and by using the **SMALL** function in column** D**, you can easily pull out the earliest times.

If the column **B** has a range of cells containing dates, you can use the **SMALL** function to find the oldest dates like the following screenshot.

**Example 3 – SMALL Function to Find the Fastest Competitors in an Event**

In the following picture, you see the results of several contestants. We’ll find out the lowest three-time differences from column** E**.

- In the output cell,
**C13**, the**SMALL**function with an array to get the three fastest time spans will be:

`=SMALL(E5:E11,{1,2,3})`

- After pressing
**Enter**, you’ll get the lowest three-time differences in an array at once.

**Example 4 – SMALL Function to Pull the Lowest Number of Days Between Two Dates**

A table contains a number of project details containing the start dates, end dates, and the number of days taken for the projects. We’ll determine the three lowest time spans for the projects in cell** C13**.

- The formula with the
**SMALL**function in the output cell**C13**should be:

`=SMALL(E5:E11,{1,2,3})`

** Things to Keep in Mind**

- The
**SMALL**function returns a**#NUM**error if the value in the second argument or**‘k’**exceeds the number of values in the selected array or range of cells. - The function will return a
**#VALUE**error if you input a non-numeric character for the second argument**(k)**. - If you need to find the smallest value only,
**the MIN function**is more suitable. - If you input the serial numbers of the smallest values in an array as the second argument, the function will also return a horizontal array containing the related outputs with the extracted data from the selected array.
- To find the largest numbers or values from an array, use the
**LARGE****function**. - If you’re using an array function in older Excel versions, use
**Ctrl + Shift + Enter**instead of**Enter.**

**Download the Practice Workbook**

**<< Go Back to Excel Functions | Learn Excel**