Microsoft Excel logo on a green background

Use the SMALL function to find the smallest number and the LARGE function to find the largest number. Use an Excel function of the form =SMALL(range,position) or =LARGE(range,position). For example, =SMALL(B2:E13,1) will find the first smallest number in the range of cells B2 through E13.

When you have a spreadsheet full of data, finding the number you need can be tedious. However, if you’re looking for the lowest product sales amount or highest test score, Excel’s SMALL and LARGE functions can help.

By using these two Excel functions, you are not limited to only finding the smallest or largest number in a range of cells. You can also spot the second smallest, third smallest, or fifth largest. To quickly find the number you want, here’s how to use the SMALL and LARGE functions in Excel.

Use the SMALL function

The syntax for the function is SMALL(range, position) where both arguments are required. You will enter the range or array for the file range discussion. For the position argument, enter one for the first smallest number, two for the second smallest number, three for the third, and so on.

For example, we will find the smallest number in the range of cells B2 to E13 using this formula:

=SMALL(B2:E13,1)

SMALL function for the first smallest number

As another example, we will find the second smallest number in the same range of cells. Here is the formula:

=SMALL(B2:E13,2)

SMALL function for the second smallest number

Use the LARGE function

The BIG function works the same way as the SMALL function, giving only the larger number. The syntax is LARGE(range, position) with both arguments required and representing the same data as the SMALL function.

To find the largest number in our range of cells B2 to E13, we’ll use this formula:

=LARGE(B2:E13,1)

BIG function for the first largest number

To find the third largest number in the same range of cells, we can use this formula:

=LARGE(B2:E13,3)

BIG function for the third largest number

One limitation to note

It is important to note one specific limitation when using these features. If you have duplicate numbers in your data, your result is skewed when you find different positions. Here’s an example.

RELATED: How to highlight duplicates in Microsoft Excel

Below, we’ve looked for the largest number in our range of cells B2 to E13. The result is 1,800, which is correct.

BIG function for the first largest number

But, if we search for the second largest number in the same range of cells, our result is 1,800 as well. This is because 1,800 appears twice, making it both the largest and second largest number.

LARGE function for the first largest number resulting in a duplicate

Consider this limitation when searching for various locations in your range of cells.

If you want to quickly find the five lowest sales totals in your product sheet or the three highest monthly invoices in your budget, keep the SMALL and LARGE features in mind.

For more information, learn how to use INDEX and MATCH to find specific values ​​or how to find a range in Excel.

Leave a Reply

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