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.

**Method 1**

### 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.

**Sort & Filter**

#### 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.

**Sort & Filter**

#### 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.

**Method 2**

### 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.

**Conditional Formatting**

#### 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**

**Conditional Formatting**

#### 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.

**=C6=MIN($C$6:$C$17)**

- 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**

**Conditional Formatting**

#### 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**

**Conditional Formatting**

#### 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.

**Method 3**

### 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.

**SMALL Function**

#### 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**

**SMALL Function**

#### 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.

**Method 4**

### 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.

**=SMALL($C$6:$C$17,B20)**

- 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**

**Method 5**

### 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.

**Method 6**

### 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**.

**SMALL Function for Date**

#### 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.

**SMALL Function for Time**

#### 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**