SMALL Function in Excel

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

SMALL Function in Excel

The Microsoft Excel SMALL function is the function that is responsible for returning the nth smallest value from the supplied set of values. In other words, we can say that the SMALL function in Excel retrieves the “nth smallest” values -like the smallest value, second smallest value, third smallest value, etc. The SMALL function in Excel is a built-in function of Excel and is categorized as a Statistical Function in excel. As a part of the worksheet function in ExcelWorksheet Function In ExcelThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more, the SMALL function can also be entered as a part of another formula in a worksheet cell.

In financial analysis, the SMALL function is useful for finding the smallest value in a supplied set of values in Microsoft Excel. For example, if the entire salesman in a company is given the same target, then with the help of the SMALL function, we can find which person achieved the sales target in the shortest time of the year.

SMALL Formula Excel

Below is a SMALL function Excel formula.

SMALL Formula

Parameters of the SMALL Function in Excel

The SMALL function accepts the following parameters and arguments:

array – This is the range or array from which you want the function to return the nth smallest value.

nth_position – This is an integer that specifies the position from the value, which is the smallest, i.e., the nth position.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use the SMALL Function in Excel?

You can download this SMALL Function Excel Template here – SMALL Function Excel Template

Below are the steps of using the SMALL function in Excel:

  1. First, enter the desired SMALL formula in the required cell. You will get a return value on the argument supplied.

  2. You can manually open the spreadsheet’s SMALL formula Excel dialog box and insert the logical values to attain a return value.

  3. Consider the screenshot below to see the SMALL function option under the statistical function menu.


    SMALL Formula in Excel

  4. Click on the “SMALL” option. The SMALL formula Excel dialog box will open where you can put the argument values to obtain a return value.


Return Value

The return value will be a numeric value, which is the nth smallest value in the array. Please note that if the nth_position is a larger value than the number of values in the array, then the SMALL function will return #NUM! Error. If the supplied array is empty, then the SMALL function will be returning #NUM! Error.

Usage Notes

How to Use SMALL Function in Excel with Examples

Let us look below at some examples of the SMALL function in Excel. These examples will help you explore using the SMALL formula in Excel.

SMALL Function Example

Based on the above SMALL, an Excel spreadsheet, let us consider these examples and see the SMALL function return based on the function’s syntax.

SMALL Function Example 1

Consider the below screenshots of the above SMALL in Excel examples for clear understanding.

SMALL in Excel Example #1

Using the SMALL formula in Excel =SMALL(A1:A5, 1), we get -3.5.

Example - 1

SMALL in Excel Example #2

Apply the SMALL formula =SMALL(A1:A5, 2) to get 4.

Example - 2

SMALL in Excel Example #3

Applying the SMALL in Excel here =SMALL(A1:A5, 3) to get 6.8.

Example - 3

SMALL in Excel Example #4

Apply the SMALL formula here =SMALL(A1:A5, 4) to get 7.

Example - 4

SMALL in Excel Example #5

Here, we apply the SMALL formula in Excel =SMALL(A1:A5, 5) to get 36.

Example - 5

SMALL in Excel Example #6

Here we use the formula to calculate the SMALL in Excel =SMALL({6, 23, 5, 2.3}, 2)

Example - 6

Top Applications of SMALL Function in Excel

We can use the Microsoft Excel SMALL function in Excel for various purposes and applications within the spreadsheet. Some of the common applications of SMALL Excel function spreadsheets are given below:

  • Highlighting the bottom values of the provided data
  • To get the sum of the bottom n values
  • Sorting numbers ascending or descending
  • Extracting multiple matches into several columns
  • Finding the lowest n values
  • Extracting multiple matches into several rows
  • Sum bottom n values with criteria

SMALL Function in Excel – Common Problem

One of the most common problems you can face while using the SMALL function is that it returns the wrong value, or the return value is nothing but an error such as #NUM! Error. This error can occur even if the supplied value of k is between 1 and the supplied array value numbers. Possibly, this can arise when you have included text representations of the number within the supplied array. The SMALL function ignores text values and only recognizes numerical values. If you face this error, convert all the array values into numeric ones.

SMALL Function Errors

If you get any error from the SMALL in Excel, then it can be any one of the following:

#NUM! – This error occurs when the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array. Apart from this, this error can also occur if the supplied array is empty.

#VALUE! – This error occurs when the supplied n is a non-numeric value.

Things to Know about the SMALL Function in Excel

  • The SMALL function in Excel is the function that is responsible for returning the nth smallest value from a given set of values in the spreadsheet.
  • The SMALL in Excel is categorized as a Statistical Function.
  • When sorted, the function returns a numeric value based on its position in a supplied list of values.
  • If the supplied array is empty, then the SMALL in Excel will be returning #NUM! Error.
  • If the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array, then the SMALL in Excel will be returning #NUM! Error.
  • If the supplied value is non-numeric, then Excel’s SMALL function will return #VALUE! Error.

Recommended Articles

This article has been a guide to SMALL Function in Excel. Here, we discuss the SMALL formula in Excel and how to use SMALL in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *