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.
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:
- 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:
- 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!
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!
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.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.