SMALL Function in Excel: Find Nth Smallest Values in Your Dataset

Published: January 27, 2025 - 3 min read

Jordan Mappang

The SMALL function in Excel helps you identify specific smallest values in your dataset, like the second-smallest or fifth-smallest number. Whether you’re analyzing sales performance, tracking inventory levels, or evaluating test scores, this function provides a systematic way to find these values. Let’s explore how to use the SMALL function effectively in Excel 2025.

Find the Second-Smallest Value in Your Dataset

The most basic application of the SMALL function involves finding a specific smallest value in your range. Here’s how to find the second-smallest value:

  1. Open your Excel spreadsheet and select the destination cell
  2. Type the following formula:

=SMALL(array,k)

  1. Replace “array” with your data range
  2. Enter “2” as the k value
  3. Press Enter to calculate

Example:

Data Range

Formula

Result

15, 8, 12, 4, 9

=SMALL(A1:A5,2)

8

💡 Pro Tip: Always ensure your data range doesn’t include headers or text values to avoid errors.

How to Use SMALL Function for Multiple Rankings

To find multiple smallest values simultaneously:

  1. Create a helper column with sequential numbers (1,2,3…)
  2. Enter the SMALL formula in your first destination cell
  3. Drag the formula down to generate multiple rankings

Example setup:

Rank

Data

Formula

Result

1

15,8,12,4,9

=SMALL($B$1:$B$5,A1)

4

2

 

=SMALL($B$1:$B$5,A2)

8

3

 

=SMALL($B$1:$B$5,A3)

9

Combine SMALL with Other Excel Functions

Enhance the SMALL function’s capabilities by combining it with other functions:

Using SMALL with IF

=SMALL(IF(range>0,range),k)

This formula finds the kth smallest value while excluding zeros.

Dynamic K Values with ROW

=SMALL(range,ROW(A1))

This creates an automatically incrementing k value when copied down.

INDEX-MATCH Combination

=INDEX(names,MATCH(SMALL(values,k),values,0))

This retrieves the name associated with the kth smallest value.

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Real-World Applications of SMALL Function

  1. Inventory Management
  • Track lowest stock levels
  • Identify products near reorder points
  • Monitor minimum par levels
  1. Sales Analysis
  • Find lowest performing products
  • Identify bottom sales territories
  • Analyze minimum transaction values
  1. Academic Performance
  • Calculate lowest test scores
  • Determine bottom percentile rankings
  • Track minimum passing grades

SMALL Function Syntax and Parameters

Understanding the function’s components:

Parameter

Required

Description

Array

Yes

The range containing your numbers

K

Yes

The position from smallest (1=smallest)

Limitations:

  • K must be positive
  • K cannot exceed array size
  • Array must contain numeric values

Common SMALL Function Examples

Finding Bottom 3 Scores

=SMALL(A1:A10,1) for lowest

=SMALL(A1:A10,2) for second lowest

=SMALL(A1:A10,3) for third lowest

Excluding Zero Values

=SMALL(IF(A1:A10<>0,A1:A10),1)

Working with Decimals

The SMALL function handles decimals automatically, maintaining full precision in calculations.

Next Steps

Now that you understand how to use the SMALL function effectively, start analyzing your own datasets to find specific smallest values. Remember to adjust the k value based on your needs.

Ready to supercharge your Excel analysis? Connect your spreadsheets to live data sources with Coefficient. Get started now and transform your Excel workflows.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Jordan Mappang
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies