Editor choice

How to Find Lowest 3 Values in Excel (5 Easy Methods)

In this tutorial, I am going to show you 5 Easy Methods on how to find the lowest 3 values in Excel. Often, we come across a situation where we need to find the lowest values in a dataset. In this article, we’ll use the SMALL, ROW, and AGGREGATE functions as well as Conditional Formatting to find the lowest 3 values in Excel.

We have a dataset of bought products and we want to find out the lowest three entries of our Total Cost range.

Let’s say the dataset comprising Product, and Total Cost looks like the image below.

How to Find Lowest 3 Values in Excel

How to Find Lowest 3 Values in Excel: 5 Easy Ways

Method 1: Using SMALL Function

The SMALL function in Excel has a text like =SMALL(array,k). In the formula, the array defines the range and k defines the positions. As we want to fetch the lowest 3 values, we have to insert the formula in individual cells changing k’s value up to 3.

Step 1: First, click on any cell E5. Insert the formula:


Step 2:  Then, press ENTER. The First lowest value will appear.

How to Find Lowest 3 Values in Excel Using Small Function

Repeat Steps 1-2 for cells E6 and E7 putting the k’s values 2 and 3 respectively. Then we’ll get a picture similar to the picture below.

Small Function End Result

Here repeating the steps we get the lowest 3 values $58.41, $82.84, and $95.58 in ascending order.

Read more: How to Find Lowest Value with Criteria in Excel

Method 2: Utilizing SMALL Function Auto Rank

What if we want to apply the SMALL function but don’t want to insert the formula every time? We can use a reference cell to declare the position (k) in the Formula.

Step 1: To begin with, insert 1, 2, and 3 in any cell (E5, E6, E7) in a column.

Step 2: Next, in the adjacent cell (F5), type:


Again don’t forget to lock the range otherwise you will end up with miscalculated data.

Step 3: After that press ENTER. The First lowest value will appear.

How to Find Lowest 3 Values in Excel Automatically

Step 4: Finally drag the Fill Handle up to the last cell (F7), all 3 lowest values will appear.

Small Function Auto Rank End Result

Now, you can see that we have the same lowest 3 values as we have with Method 1 but with less effort.

Read More: How to Find Minimum Value in Excel

Method 3: Combining SMALL and ROW Functions

With Methods 1 and 2, we still have to declare positions somehow. In that case, we entirely don’t want to declare or insert any position in the formula. We can do it by combining the ROW function with the SMALL function. Here, the ROW function fixes the position with a range consisting of a Fixed Reference and a Changing Reference.

Step 1: Firstly, click on a cell (E5). Paste the formula:

=SMALL($C$5:$C$10, ROWS(C$5:C5))

Step 2: Next, hit ENTER. The utmost lowest value will appear.

How to Find Lowest 3 Values in Excel Using Small and Row Functions

Step 3: Finally, drag the Fill Handle, and the rest of the values will show up.

Small and Row Function End Result

Keep in mind that by this method we can find not only 3 lowest values but also n numbers of lowest values. As you can also see the values we get are similar to prior results.

Read More: How to Find Minimum Value with VLOOKUP in Excel

Method 4: Implementing Conditional Formatting

We can indicate the lowest values by conditional formatting with the range in a dataset.

Step 1: First select cells C5 to C10 and then go to Home Tab >> Conditional Formatting (in Style Section). Select New Rule.

How to Find Lowest 3 Values in Excel Using Conditional Formatting

Step 2: New Formatting Rule window will pop up. Select “Use a formula to determine what cell to format” in the Select a rule box.

Step 3: Insert this formula in Edit the Rule Description box.

=C5<=SMALL($C$5:$C$10, 3)

Entering Formatting Rule

Step 4: Click on Format below the Edit the Rule Description box & Choose Fill Colour (Yellow).

Step 5: Click OK.

Choosing Fill Color

The consequences of these steps result in an image similar to the image below

Conditional Formatting End Result

We can see Conditional Formatting colors with the 3 lowest values.

Read More: How to Find Minimum Value Based on Multiple Criteria in Excel

Method 5: Applying AGGREGATE Function with SMALL Function

The AGGREGATE function returns the AGGREGATE of a data range. The AGGREGATE text is AGGREGATE(function_num, options, array,[k]).

Here, function_num has 19 embedded functions and the SMALL function is one of them (15. Options offer various command types, we choose 4 (Ignoring Nothing). Array declares a range and k positions.

Step 1: Insert the formula in any cell (F5):


Step 2: Click OK. Then the outcome depicts the following image

Using Aggregate and Small Function

Step 3: Repeat Steps 1 and 2  replacing the position number(k) with 2 and 3. Then we’ll get something like the image below

Aggregate Function End Result

After all this, we can see that similar results are popping up with every method.

Read More: How to Use Combined MIN and IF Function in Excel

Download Practice Workbook

You can download the practice workbook from here.


To find out the lowest 3 values we use SMALL, ROW, and AGGREGATE Functions as well as Conditional Formatting. From the article, you can understand that plain SMALL and AGGREGATE functions find the lowest values one at a time depending on positions. But combining SMALL and ROW functions does it automatically with a fixed reference in its formula. Hope you find the discussed methods lucid and steps easy to follow. Comment, if you get to learn new things and have something to add.

Related Articles

<< Go Back to Excel MIN Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF