Excel SMALL Function: Syntax, Examples & Tips

array:
The range of cells or array from which you want to find the k-th smallest value.

k:
The position (from the smallest) in the array of the value you want to return. This must be a positive integer.

What is a SMALL Formula in Excel?

The SMALL function in Excel is a useful tool that allows you to find the nth smallest value in a range of data. It evaluates a set of numbers and returns the value that corresponds to the specified position (n) in the sorted order of the data.

In simpler terms, the SMALL function helps you identify specific values from a dataset based on their rank, making it easier to analyze and interpret data.

A Practical Example

Imagine you are analyzing the scores of students in a class, and you have the following data in an Excel spreadsheet:

Student Scores Table:

You want to find the second smallest score in the class.

SMALL Formula

To calculate the second smallest score, you would use the SMALL formula as follows:

=SMALL(B2:B6, 2)

Breakdown of the Formula:

• B2:B6: This is the range of scores from which you want to find the nth smallest value.
• 2: This is the position (n) that indicates you want the second smallest value.

Result of the Formula

When you apply the formula, it returns the second smallest score from the dataset. The output would be:

Here, the second smallest score is identified as 85, which belongs to Alice.

Why Use SMALL?

The SMALL function is particularly useful when you need to analyze data by identifying specific values based on their rank. It allows you to quickly find the lowest scores, expenses, or any other metrics that require ranking, making it easier to derive insights from your data.

Key Takeaways:

• SMALL: Finds the nth smallest value in a specified range.
• Efficient Data Analysis: Helps you quickly identify specific values based on their rank.
• Common Use Cases: Ideal for academic performance analysis, financial data assessment, and any scenario where ranking is important.

Understanding how to use the SMALL function can significantly enhance your data analysis capabilities in Excel, enabling you to extract meaningful insights from your datasets.

Happy calculating!