How to Use SMALL Function in Excel (4 Common Examples)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, the SMALL function is used to extract the nth lowest or smallest value from a range of cells containing numerical data only. In this article, you’ll get to learn how you can use this SMALL function effectively in Excel with appropriate illustrations.

small function overview in excel

The above screenshot is an overview of the article, representing a few applications of the SMALL function in Excel. You’ll learn more about the methods to use the SMALL function properly in the following sections of this article.


Introduction to the SMALL Function

small function syntax

  • Function Objective:

The function is used to find the k-th (generally what we know as nth) 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- 3rd smallest.
  • Return Parameter:

k-th (or nth) numerical value from the selected array.


Using SMALL Function in Excel: 4 Common Examples

1. Using SMALL Function to Find the Smallest Values in Excel

In the picture below, column B has some numerical values in decimals. In column D, we’ll find the smallest 3 values by using the SMALL function. We have to input the range of cells B5:B14 in the array argument and the serial number of the smallest values in the ‘k’ argument.

So, in cell D7, the related formula to find the 1st smallest value will be:

=SMALL(B5:B14,1)

After pressing Enter, you’ll get the first smallest value from column B.

Similarly, 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.

small function to find smallest 3 in excel


2. Use of SMALL Function to Extract the Earliest Times or Dates in Excel

With the SMALL function, you can extract the earliest times from a range of cells or an array containing timestamps. In the picture below, column B has a number of timestamps, and by using the SMALL function in column D, you can easily pull out the earliest times.

small function to find earliest times in excel

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

small function to find earliest times in excel


3. SMALL Function to Find the Fastest Competitors in an Event

Now, in a practical scenario, the SMALL function is more useful when you have to find the champion and runners-up in a contest based on the lowest time taken to finish 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.

small function to find the fastest 3 in excel

In the output cell, C13, the SMALL function with an array inside 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.

small function to find the fastest 3 in excel


4. SMALL Function to Pull Out the Lowest Number of Days Between Two Dates

We can use the SMALL function to get the lowest number of days between two dates. In the picture below, a table is present with a number of project details containing the start dates, end dates, and the number of days taken for the projects. We’ll find out the lowest three-time spans for the projects in cell C13.

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

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

After pressing Enter, the formula will return an array containing the lowest three-time spans right away.

small function to find the lowest time spans in excel


💡 Things to Keep in Mind

🔺 SMALL function returns a #NUM error if the value in the second argument or ‘k’ exceeds the number of total data 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 1st, lowest, or smallest value only, the MIN function is more suitable to use.

🔺 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, then use the LARGE function.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Concluding Words

I hope all of the simple methods mentioned above to use the SMALL function will now prompt you to apply them in your Excel spreadsheets with more productivity. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo