Identify the Smallest Values in an Excel List

The goal of this tutorial is to provide step-by-step instructions on how to use various methods to extract or identify the smallest values in a list. We will cover three exercises that include the SMALL function, the SEQUENCE function, and conditional formatting.

Video

Walkthrough

Let’s take each of these methods one at a time. We’ll start by getting warmed up with the SMALL function.

Exercise 1: Using the SMALL function

Consider the following list:

We’d like to identify the smallest value in the list. When the list is small, or if we can sort the list, this is relatively easy to do manually by scanning or sorting. But, if the list is long, or you want to retain the original data order, or want to pull the smallest value into another cell to then use in a subsequent formula or analysis, the SMALL function will really come in handy.

We write the following formula into a cell:

=SMALL(B11:B25,1)

• B11:B25 is the range to evaluate
• 1 is the position, where 1 represents the smallest value, 2 the second smallest value, and so on

We hit Enter and bam:

The formula returns the smallest value found in the range. Excellent.

But, what if we wanted to return the 3 smallest values? Or the 5 smallest values? How can we do that? That leads us to the next exercise.

Exercise 2: Specifying the Number of Items with SEQUENCE

We start with the same basic data:

We’ll use the SMALL function again, but this time, we’ll nest the SEQUENCE function as the second argument so that the formula will return multiple values. For example, to return the smallest 3 values we could use the following formula:

=SMALL(B11:B25,SEQUENCE(3))

• B11:B25 is the range to evaluate
• SEQUENCE(3) returns an array of values from 1 to 3 (ie, 1, 2, 3)

We write the formula in D11 and hit Enter:

It returns the smallest 3 values from the list. Excellent.

If we wanted to return the smallest 5 we would use SEQUENCE(5) instead.

Now, what if we wanted to actually highlight the smallest values within the list, rather than extract them into another range. Well, we can simply apply conditional formatting. Let’s check that out in the next exercise.

Exercise 3: Using Conditional Formatting to Identify the Smallest Items

Same basic list:

This time, instead of pulling the smallest values out via formula, we want to format the smallest values.

For this, we’ll use conditional formatting.

We begin by selecting the list range and going to Home > Conditional Formatting > Top/Bottom Rules > Bottom 10 Items.

Change the default number 10 to the desired number, such as 3, and pick the desired format.

When we apply the conditional formatting, bam:

Yay … we did it!

Conclusion

In conclusion, extracting the smallest values is made easy by utilizing the SMALL and SEQUENCE functions. We can leverage conditional formatting to highlight the smallest values in the list.

If you have any suggestions, improvements, alternatives, or questions, please post a comment … thanks!

Sample file

FAQ

Q: Can I use the SMALL function with non-numeric values?

A: The SMALL function is designed to work with numeric values. If you have non-numeric values, consider converting them to numeric equivalents or using other functions such as INDEX and MATCH.

Q: Can I customize the format applied by conditional formatting?

A: Absolutely! When setting up the conditional formatting rule, you can choose various formatting styles, including font color, cell fill, and borders.

Q: What should I do if I want to find the largest items instead of the smallest?

A: To find the largest items, you can use the LARGE function instead of the SMALL function. The steps remain the same, except you replace “SMALL” with “LARGE” in the formulas.

Q: Can I apply conditional formatting to multiple ranges?

A: Yes, you can apply conditional formatting to multiple ranges by selecting all the desired ranges before creating the conditional formatting rule.