Often, we come across a situation where we need to find the lowest values in a dataset. In this article, we’ll use 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, unit price, quantity, and total cost looks like the image below
Dataset for Download
5 Easy Ways to Find Lowest 3 Values in Excel
Method 1: Using SMALL Function
The plain SMALL formula 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: Click on any cell C3. Insert the formula
Step 2: Press ENTER. The First lowest value will appear.
Repeat Steps 1-2 for cells C4 and C5 putting the k’s value 2 and 3 respectively. Then we’ll get a picture similar to the picture below.
Here repeating the steps we get the lowest 3 values $37.80, $57.97, and $58.41 in ascending order.
Read more: How to Find Value In Range in Excel
Method 2: Using SMALL Function Automatically
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: Insert 1, 2, and 3 in any cell (C2, C3, C4) in a column.
Step 2: In the adjacent cell (D2), type
Step 3: Press ENTER. The First lowest value will appear.
Step 4: Drag the Fill Handle up to the last cell (C4), all 3 lowest values will appear.
Now, you can see that we have the same lowest 3 values as we have with Method 1 but with less effort.
Method 3: Using SMALL & ROW Function
With Method 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: Click on a cell (G4). Paste the formula
Step 2: Hit ENTER. The utmost lowest value will appear.
Step 3: Drag the Fill Handle, the rest of the values will show up.
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.
- How to Find Last Cell with Value in Column in Excel
- Find the Last Cell With Value in Row in Excel (6 Methods)
- Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)
- How to Find Multiple Values in Excel (8 Quick Methods)
Method 4: Using Conditional Formatting
We can indicate the lowest values by conditional formatting with the range in a dataset.
Step 1: Go to Home Tab >> Conditional Formatting (in Style Section). Select New Rule.
Step 2: New Formatting Rule window will pop up. Select “Use a formula to determine what cell to format” in the Select rule box.
Step 3: Insert the formula
Step 4: Click on Format below the Edit the Rule Description box & Choose Fill Colour (Blue).
Step 5: Click OK.
The consequences of these steps result in an image similar to the image below
We can see Conditional Formatting colors with the 3 lowest values.
Method 5: Using AGGREGATE Function with SMALL Option
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 (H4)
Step 2: Click OK. Then the outcome depicts the following image
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
After all this, we can see that similar results are popping up with every method.
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.