Hassan Shuvo
Are you a teacher? Do you need to find the students who scored the lowest in the final exam? Perhaps you are an entrepreneur looking for an easier way to determine the least amount of products you delivered in a month. If you are neither, but you want to find the lowest values in a data range in Excel, you have come to the right place. In this article, we will show you how to find and highlight the lowest value in a set of data in Excel.
📁 Download Excel File
Download the file to practice with us.
Learn to Highlight Lowest Value in Excel with These 6 Unique Methods
In today’s article, I will demonstrate 6 simple ways you can highlight the lowest value in an array in Excel. The methods will include Excel’s built-in features and some functions like MIN, SMALL, INDEX, MATCH, SORT, and FILTER. What’s more, we will see how we can use the SMALL function to find the earliest date and time. As an example, we choose to show the marks obtained by a couple of students in an exam.
1. Using Sort & Filter Feature
For our first method, we choose to show you Excel’s own built-in feature, Sort & Filter. We will explain them individually.
1.1. Using Sort Feature
Using Sort, we can sort our data from smallest to largest or from largest to smallest. Let’s apply the data to our example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the data range that you want to sort. Since we need to find the smallest mark we will sort our data from smallest to largest. So we are selecting C6:C17.
- Then, we will go to the Home tab and from the Editing group, select Sort & Filter. Finally choose Sort Smallest to Largest to get your result accordingly.
- At this time, a window will pop up. Just select Expand the selection.
Thus, you found the lowest value in your dataset.
1.2. Using Filter Feature
Using Filter, we can sort data from smallest to largest in a single click.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your entire data range, as we did for B5:C17.
- After that, go to the Home tab, and from the Editing group, select Sort & Filter. Finally select Filter.
You will see Drop-down buttons beside your headers.
- If you click the Drop-down button beside ‘Obtained Mark’ you will get options to sort your data from smallest to largest. Click Sort Smallest to Largest to sort them.
Your lowest value is in the first row now.
2. Applying Conditional Formatting Feature
One of the most popular features of Excel is the Conditional Formatting feature. You have the option of using the built-in Top/Bottom option or creating your own rule based on your own formula. We will show you both. Also, we will show you how you can use the Conditional Formatting feature to locate and highlight the lowest value in a row or column in Excel.
2.1. Applying Built-in Rule
We can use Conditional Formatting to locate the bottom-ranked value using the top or bottom rule.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range as we selected B6:C17.
- Now go to the Home tab, and from the Styles group select Conditional Formatting. Finally, choose New Rule from the menu.
- A New Formatting Rule window will appear. From here, select Format only top or bottom ranked values. Selecting this will give you the option to locate top or bottom values. Also, it will give you a choice to find the number of top or lowest-ranking values. Since we want to highlight the lowest value, we will select Bottom, and the number will be just 1.
- Next, we need to select a color to highlight the lowest value so will select Format. The Format Cells box will help us in this case. From Fill tab select any color you want. We are selecting red then press OK.
Finally, you can see your lowest value has been highlighted.
📕 Read More: Conditional Formatting Based on Date Range in Excel
2.2. Applying New Rule Based on Formula
We can also use Conditional Formatting to locate the bottom-ranked value using our own formula. In order to find the smallest value in the given data, we created a formula using the MIN function.
The MIN function has the following syntax:
MIN(number1, [number2], …)
number1 – a number, a reference to a numeric value, or a range of numeric values.
number2 – a number, a reference to a numeric value, or a range of numeric values. This is optional.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range, as we selected B6:C17.
- Now select the Home tab, and from the Styles group select Conditional Formatting. Finally, choose New Rule from the menu.
- In order to put a new formula we will select Use a formula to determine which cells to format. This selection will bring forth a box where we will write our formula. Type this formula in the box.
- Again, to highlight the lowest value, select Format. The Format Cells box will help us in this case. From Fill tab select any color you want. We are selecting red then press OK.
Now, your lowest value has been highlighted.
📕 Read More: How to Apply Excel Formula to Change Text Color Based on Value
2.3. Applying New Rule for Multiple Rows
Next, we will use Conditional Formatting to locate the bottom-ranked value in multiple Rows using a formula. Similar to the previous one, we will utilize the MIN function here.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range, as we selected C7:E11.
- Then, go to Home tab, and from the Styles group and select Conditional Formatting. Finally, choose New Rule from the menu.
- In order to put a new formula we will select Use a formula to determine which cells to format. This selection will bring forth a box where we will write our formula. Type this formula in the box.
=C7=MIN($C7:$E7)
- Again, to highlight the lowest values, select Format. The Format Cells box will help us in this case. From Fill tab select any color you want. We are selecting red then press OK.
Now, your lowest values in each Row have been highlighted.
📕 Read More: Conditional Formatting to Highlight Row Based on Date in Excel
2.4. Applying New Rule for Multiple Columns
Finally, we will use Conditional Formatting to locate the bottom-ranked value in multiple Columns using a formula. Again, we are using the MIN function for the rule setting.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range, as we selected C7:E11.
- Then, go to Home tab, and from the Styles group select Conditional Formatting. Finally, choose New Rule from the menu.
- In order to put a new formula we will select Use a formula to determine which cells to format. This selection will bring forth a box where we will write our formula. Type this formula in the box.
=C7=MIN(C$7:C$11)
- Again, to highlight the lowest values, select Format. The Format Cells window will help us in this case. From Fill tab, select any color you want. We are selecting red then press OK.
Now, your lowest values in each Column have been highlighted.
3. Utilizing SMALL Function
If you need to find the lowest value and place it in another cell, you will have to use functions. In this way, the original table remains intact. So in this example, we will use the SMALL function to show you how you can identify the lowest value and also place it somewhere else. But first, let’s learn the syntax of the function.
SMALL(array, k)
The arguments are:
array – a collection of numerical values in an array.
k – position, expressed as an integer, with 1 being the smallest value.
The SMALL function returns the array’s kth smallest value.
3.1. Finding Smallest Value
Let’s use the SMALL function to highlight the lowest value in our array in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your lowest value from the array. We selected C19 in here.
- Next in the Formula Bar type this formula.
=SMALL(C6:C17,1)
- Finally, hit Enter to see the lowest value.
📕 Read More: 5 Easy Ways to Highlight Highest Value in Excel
3.2. Finding Bottom 3 Value
We can also use the SMALL function to find the bottom 3(or any number you want) values in our array.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your lowest value from the array. We selected cell C20 in this case.
- Next in the Formula Bar type this formula.
=SMALL($C$6:$C$17,B20)
- Next, hit Enter to see the lowest value.
- Finally, use the Fill Handle tool to automatically copy the formula.
Hence, you got the last three values.
4. Using INDEX & MATCH Functions
The problem with the previous method is that it only shows the lowest marks. But if you also want the names, you can try using the INDEX and MATCH functions together.
Syntax of the INDEX function:
INDEX(array, row_num, [column_num])
The arguments are as follows.
array – cells in a certain range or an array constant.
row_num – unless column_num is provided, it is necessary. chooses which row in the array to return a value from. Column_num is necessary if row_num is left out.
column_num – chooses the array column that will contain the value to be returned. Row_num is necessary if column_num is left out.
And syntax of the MATCH function:
MATCH(lookup value, lookup array, [match type])
Arguments are:
lookup value – this is a must. The lookup array value you’re trying to match.
lookup array – the number of cells being looked for.
match type – the lookup_value and values in the lookup_array are matched by Excel according to the match type argument. The match types are:
-1 the smallest value larger than or equal to the lookup value
0 first value that is exactly equal to lookup value
1 greatest number that is less than or equal to lookup_value.
Let’s get to the example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your lowest value from the array. We selected cell C20 in this case.
- Next in the Formula Bar type this formula.
- Next, hit Enter to see the lowest value.
- Then, use the Fill Handle tool to automatically copy the formula.
- Again, to get the names of those who got the lowest marks select cell D20 and type this formula.
=INDEX($B$6:$B$17,MATCH(C20,$C$6:$C$17,0))
- Next, hit Enter to see the name of the student who got the lowest value.
- Then, use the Fill Handle tool to automatically copy the formula.
🔨 Formula Breakdown
👉 First, MATCH function looks at the content of C20 as the lookup_value and then, search in the lookup_array $C$6:$C$17 and we provided 0 to get the exact match.
👉 Then, the INDEX function will print the content of Column B according to the lookup_value found by the MATCH function in Column C.
📕 Read More: Conditional Formatting Based on Another Cell Date in Excel
5. Incorporating SORT & FILTER Functions Together
As you can see, we used two formulas in our previous example to get the job done. But what if you can use just one formula to do that? The SORT and FILTER functions used together can highlight the lowest value in Excel. We will also use the SMALL function here.
The syntax of the functions:
SORT(array,[sort_index],[sort_order],[by_col])
A sorted array of an array’s items is what SORT returns. The length of the returned array matches that of the array parameter.
The arguments are
array – the dataset, or collection, to sort. This argument is mandatory.
sort_index – number of rows or columns to sort by
sort_order – a number specifying the preferred sort order, with 1 being the default ascending order and -1 being the descending order.
by_col – a logical value that represents the preferred sort direction; TRUE to sort by column, FALSE to sort by row.
The last three arguments are optional.
FILTER(array,include,[if_empty])
The arguments:
array – the data range to filter
include – a boolean array with the same height or width as the array
If_empty – a value that will be returned if the included array is empty of all values.
Let’s use these functions in our example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell as we selected B20.
- Then, in the Formula Bar type this formula.
=SORT(FILTER($B$6:$C$17,$C$6:$C$17<=SMALL($C$6:$C$17,3)),2,1)
- Finally, hit Enter to see the last 3 names and lowest values.
🔨 Formula Breakdown
👉 First, The SMALL function will get the 3rd lowest value which is 42 in $C$6:$C$17 and the FILTER function will collect those smaller than or equal to 42.
👉 Finally, the SORT function will sort the values in ascending order. Moreover, we specified 2 in the sort index as we want the second column to sort by and 1 in the sort order for ascending order.
6. Employing SMALL Function for Dates and Times
Dates are kept in the internal Excel system as sequential numbers, and times are kept as fractional decimal seconds. Since dates and times can also be represented numerically, the SMALL function can handle them without any additional work on your part. But in order to get the output result as dates and times, you need to format your cell. Click here to see how you can format your cells to show Dates and Times.
6.1. Finding Earliest Date
Let’s use the SMALL function to find the earliest date in an array.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your earliest date from the array. We selected C12 in this case.
- Then, in the Formula Bar type this formula.
=SMALL(C6:C10,1)
- Finally, hit Enter to see the date of 1st submission.
6.2. Finding Earliest Time
Finally, let’s use the SMALL function to find the earliest time in an array.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your earliest time from the array. We selected C12 in this case.
- Then, in the Formula Bar type this formula.
=SMALL(C6:C10,1)
- Finally, hit Enter to see the time of 1st submission.
📄 Important Notes
🖊️ In the case of SMALL function, your formula may result in “ties” if there are duplicates in the array. For instance, if two cells contain the number 1, and the SMALL function is set to return the least and second smallest values, you will obtain 1 in both circumstances.
🖊️ The SMALL function will return an error if the array includes one or more errors.
🖊️ The INDEX-MATCH formula won’t work for duplicate values.
📝 Takeaways from This Article
📌 First, we showed how to use the built-in Excel Sort & Filter feature to highlight the lowest value.
📌 Then we demonstrated the Conditional Formatting feature in different cases.
📌 Then, we explained the small function and its functionality to find the lowest values.
📌 We also showed the use of the INDEX and MATCH functions to achieve the same goal.
📌 Then, to make the process effortless, we explain how to use the SORT and FILTER functions together in this context.
📌 Lastly, we used the SMALL function to find the earliest dates and times.
Conclusion
Finally, you have come to the conclusion of our article. Lastly, I hope you understood the processes and the functions well enough to use them on your own from now on. Moreover, if you have any more questions or if you know any other way we can highlight the lowest possible value in an array in Excel, please leave a comment below to let us know. For more articles like this, visit Excelden.com.
Related Articles
- 4 Ways to Use Conditional Formatting for Dates Overdue in Excel
- Excel VBA: If Font Color Is Red Then Return Specific Output
