by Svetlana Cheusheva, updated on

*In this short tutorial, we'll talk about Excel SMALL function, how it works and how to use it to find the Nth smallest number, date, or time.*

Need to find a few lowest numbers in a worksheet? This is quite easy to do with the Excel Sort feature. Do not want to waste time on re-sorting your data with every change? The SMALL function will help you quickly find the lowest value, second smallest, third smallest, and so on.

SMALL is a statistical function that returns the n-th smallest value in a data set.

The syntax of the SMALL function includes two arguments, both of which are required.

SMALL(array, k)

Where:

**Array**- an array or a range of cells from which to extract the smallest value.**K**- an integer that indicates the position from the lowest value to return, i.e. k-th smallest.

The function is available in all versions of Excel for Office 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and earlier.

Tip. To find k-th lowest value with criteria, use Excel SMALL IF formula.

A SMALL formula in its basic form is very easy to build - you just specify the range and the position from the smallest item to return.

In the list of numbers in B2:B10, supposing you want to extract the 3^{rd} smallest value. The formula is as simple as:

`=SMALL(B2:B10, 3)`

To make it easier for you to check the result, column B is sorted in ascending order:

The following usage notes will help you better understand the behavior of the SMALL function and avoid confusion when building your own formulas.

- Any
**blank cells**,**text**values, and**logical**values TRUE and FALSE in the*array*argument are ignored. - If
*array*contains one or more**errors**, an error is returned. - In case there are
**duplicates**in*array*, your formula may result in "ties". For example, if two cells contain the number 1, and the SMALL function is configured to return the smallest and the 2^{nd}smallest value, you will get 1 in both cases. - Assuming n is the number of values in
*array*, SMALL(array,1) will return the lowest value, and SMALL(array,n) will pick the highest value.

And now, let's look at some more examples of the Excel SMALL function that go beyond its basic usage.

As you already know, the SMALL function is designed to compute the n-th lowest value. This example shows how to do this most effectively.

In the table below, suppose you wish to find the bottom 3 values. For this, type the numbers 1, 2 and 3 in separate cells (D3, D4 and D5 in our case). Then, enter the following formula in E3 and drag it down through E5:

`=SMALL($B$2:$B$10, D3)`

In E3, the formula extracts the smallest value using the number in D3 for the *k* argument. The key thing is to supply proper cell references due to which the formula copies correctly in other cells: absolute for *array* and relative for *k*.

Do not want to bother typing the ranks manually? Use the ROWS function with an **expanding range** reference to provide the *k* value. For this, we make an absolute reference for the first cell (or only lock the row coordinate like B$2) and relative reference for the last cell:

`=SMALL($B$2:$B$10, ROWS(B$2:B2))`

As the result, the range reference expands as the formula is copied down the column. In D2, ROWS(B$2:B2) produces 1 for *k*, and the formula returns the lowest cost. In D3, ROWS(B$2:B3) yields 2, and we get the 2^{nd} lowest cost, and so on.

Just copy the formula through 5 cells, and you'll get bottom 5 values:

Want to find a total of the smallest n values in a dataset? If you've already extracted the values like shown in the previous example, the easiest solution would be a SUM formula like:

`=SUM(E3:E5)`

Or you can make an independent formula by using the SMALL function together with SUMPRODUCT:

SUMPRODUCT(SMALL(*array*, {1, …, *n*}))

To get the sum of the bottom 3 values in our set of data, the formula takes this shape:

`=SUMPRODUCT(SMALL(B2:B10, {1,2,3}))`

The SUM function will produce the same result:

`=SUM(SMALL(B2:B10, {1,2,3}))`

Note. If you use **cell references** rather than array constant for *k*, you need to press Ctrl + Shift + Enter to make it an array formula. In Excel 365 that supports dynamic arrays, SUM SMALL works as a regular formula in either case.

**How this formula works:**

In a regular formula, SMALL returns a single k-th smallest value in a range. In this case, we supply an array constant like {1,2,3} for the k argument, forcing it to return an array of the smallest 3 values:

`{29240, 43610, 58860}`

The SUMPRODUCT or SUM function adds up the numbers in the array and outputs the total. That's it!

In situation when you wish to retrieve some data associated with the smallest value, use the classic INDEX MATCH combination with SMALL for the lookup value:

INDEX(*return_array*, MATCH(SMALL(*lookup_array*, *n*), *lookup_array*, 0))

Where:

**Return_array**is a range from which to extract associated data.**Lookup_array**is a range where to search for the lowest n-th value.**N**is the position of the smallest value of interest.

For example, to get the name of the project that has the lowest cost, the formula in E3 is:

`=INDEX($A$2:$A$10, MATCH(SMALL($B$2:$B$10, D3), $B$2:$B$10, 0))`

Where A2:A10 are the project names, B2:B10 are the costs and D3 is the rank from smallest.

Copy the formula to the below cells (E4 and E5), and you will get the names of the 3 cheapest projects:

Notes:

- This solution works fine for a dataset that has no duplicates. However, two or more duplicate values in a numeric column may create "ties" in ranking, which will lead to wrong results. In this case, please use a bit more sophisticated formula to break ties.
- In Excel 365, this task can be accomplished with the help of the new dynamic array functions. Apart from being much simpler, this approach automatically solves the problem of ties. For full details, please see How to filter bottom N values in Excel.

I believe everyone knows how to put numbers in order with Excel Sort feature. But do you know how to perform sorting with a formula? The users of Excel 365 can do it an easy way with the new SORT function. In Excel 2019, 2016 and earlier versions, SORT does not work, alas. But have a little faith, and SMALL will come to the rescue :)

Like in the first example, we use the ROWS function with an expanding range reference to increment *k* by 1 in every row where the formula is copied:

`=SMALL($A$2:$A$10, ROWS(A$2:A2))`

Enter the formula in the first cell, and then drag it down to as many cells as there are values in the original data set (C2:C10 in this example):

Tip. To sort **descending**, use the LARGE function instead of SMALL.

Because dates and times are also numeric values (in the internal Excel system, dates are stored as sequential numbers and times as decimal fractions), the SMALL function can handle them as well without any extra effort on your side.

As you can see in the screenshots below, a basic formula that we used for numbers works beautifully for dates and times as well:

`=SMALL($B$2:$B$10, D2)`

SMALL formula to find the earliest 3 dates:

SMALL formula to get the shortest 3 times:

The next example shows how the SMALL function can help you accomplish a more specific task relating to dates.

In a list of dates, supposing you want to find the nearest date before a specified date. This can be done by using the SMALL function in combination with COUNTIF.

With the list of dates in B2:B10 and the target date in E1, the following formula will return a prior date closest to the target date:

`=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1))`

To extract a date that is two dates before the date in E1, i.e. a previous but one date, the formula is:

`=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1)`

To find a past date **closest to today**, use the TODAY function for COUNTIF's criteria:

`=SMALL(B2:B10, COUNTIF(B2:B10, "<"&TODAY()))`

Tip. To prevent errors in situation when a date matching your criteria is not found, you can wrap the IFERROR function around your formula, like this:

`=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1), "Not Found")`

**How these formulas work:**

The general idea is to count the number of dates smaller than the target date with COUNTIF. And this count is exactly what the SMALL function needs for the *k* argument.

To better grasp the concept, let's look at it from another angle:

If 1-Aug-2020 (the target date in E1) appeared in our dataset, it would be the 7^{th} biggest date on the list. Consequently, there are six dates smaller than it. Meaning, the 6^{th} smallest date is the previous date closest the target date.

So, first we calculate how many dates are smaller than the date in E1 (the result is 6):

`COUNTIF(B2:B10, "<"&E1)`

And then, plug the count into the 2^{nd} argument of SMALL:

`=SMALL(B2:B10, 6)`

To get the previous but one date (which is the 5^{th} smallest date in our case), we subtract 1 from COUNTIF's result.

To highlight the smallest n values in your table with Excel conditional formatting, you can use either a built-in Top/Bottom option or set up your own rule based on a SMALL formula. The first method is faster and easier to apply, while the second provides more control and flexibility. The below steps will walk you through creating a custom rule:

- Select the range in which you want to highlight bottom values. In our case, the numbers are in B2:B10, so we select it. If you'd like to highlight entire rows, then select A2:B10.
- On the
*Home*tab, in the*Styles*group, click*Conditional formatting*>**New Rule**. - In the
*New Formatting Rule*dialog box, choose*Use a formula to determine which cells to format.* - In the
*Format values where this formula is true*box, enter a formula like this one:`=B2<=SMALL($B$2:$B$10, 3)`

Where B2 is the leftmost cell of the numeric range to be checked, $B$2:$B$10 is the whole range, and 3 is the

*n*bottom values to highlight.In your formula, please mind the reference types: the leftmost cell is a relative reference (B2) while the range is the absolute reference ($B$2:$B$10).

- Click the
*Format*button and choose any format you like. - Click OK twice to close both dialog windows.

Done! The bottom 3 values in column B are highlighted:

For more information, please see Excel conditional formatting based on formula.

As you've just seen from our examples, using the SMALL function in Excel is quite easy, and you are unlikely to have any difficulties with it. If your formula does not work, most likely that will be a #NUM! error, which may occur because of the following reasons:

*Array*is empty or does not contain a single numeric value.- The
*k*value is less than zero (a silly typo can cost you hours of troubleshooting!) or exceeds the number of values in the array.

That's how to use a SMALL formula in Excel to find and highlight bottom numbers in a set of data. If you know any other scenarios where the function comes in handy, you are most welcome to share in comments. I thank you for reading and hope to see you on our blog next week!

Excel SMALL formula examples (.xlsx file)

Table of contents