SMALL Function Examples in Excel, VBA, & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 9, 2023
Download Example Workbook

Download the example workbook

This tutorial demonstrates how to use the SMALL Function in Excel to calculate the nth smallest value.

Small Main

How to use the SMALL Function

The SMALL Function returns the k-th smallest number from a data range.

To use the SMALL Function, use a formula like this:

=SMALL($B$2:$B$9,3)

Example 01

Here we are searching for the 3rd smallest number (or the second runner up) in the range B2:B9.

Out of Range Error

If you enter a number k, greater than the number of items in the range, SMALL will return #NUM! error

Example 02

Notice here we are searching for the 9th smallest number in a range of only 8 numbers. So SMALL returns #NUM!.

Non-numeric Data

The SMALL Function will completely ignore non-numeric data, acting just as if the non-numeric data did not exist.

Example 03

SMALL If

We can use the SMALL Function with criteria as well to perform a “small if”. For example, let’s say we want to find out who is the fastest runner over the age of 25.

We will use this array formula:

{=SMALL( IF(B2:B9 >= 25, C2:C9), 1)}

Example 04

Note: When building array functions, you must press CTRL + SHIFT + ENTER instead of just ENTER after creating your formula.

You’ll notice how the curly brackets appear. You can not just manually type in the curly brackets; you must use CTRL + SHIFT + ENTER.

To learn more about how the SMALL “IF” formula works, read our tutorial on SMALL If and LARGE IF.

Sort with the SMALL Function

One very useful, but not well known, use case for the SMALL Function is to sort data in ascending (smallest to largest) order. To sort data, we combine the SMALL Function with the ROW Function.

=SMALL($A$2:$A$9, ROW()-1)

Example 05

This function works by using the ROW Function to define the k-value (row 2 for the smallest, row 3 second smallest, etc.). It starts with k =1 (1st smallest) and goes up to k = 8 (8th smallest number) thus sorting them from smallest to largest. To learn more read our tutorial on Sorting with the SMALL and LARGE Functions.

Note: The above formula uses absolute references (the $ signs) to lock cell references when copying formulas. If you aren’t familiar with this, please read our Excel References Guide.

SMALL function in Google Sheets

The SMALL function works exactly the same in Google Sheets as in Excel.

Small Google

SMALL Examples in VBA

You can also use the SMALL function in VBA. Type:

application.worksheetfunction.small(array,k)

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions