If you are looking for some of the easiest ways to highlight the lowest value in Excel, then you will find this article worth it.

While working with Excel it becomes necessary sometimes to find the lowest value in a dataset easily and quickly for calculations. By using the following methods, you will be able to do this job effectively.

**Table of Contents**hide

## Download Workbook

## 11 Ways to Highlight Lowest Value in Excel

For explaining the methods I have used two datasets; one consists of values as **Marks** or in number format and another dataset has date format where the format is **dd/mm/yyyy**. So, letâ€™s get started with the explanation of these methods.

__Method-1__: Using Sort & Filter Option to Highlight Lowest Value

For re-arranging the values from lowest to highest you can use the **Sort & Filter Option**. By doing this you can easily highlight the lowest value in the** Marks column**.

** Step-01**:

âž¤Select the data range

âž¤Go to

**Home**Tab>>

**Editing**Dropdown>>

**Sort & Filter**Dropdown>>

**Custom Sort**Option

**Sort **Dialog Box will appear.

âž¤Select the following options**Sort by ****âžś****Marks**

**Sort On**

**âžś**

**Cell Values**

**Order**

**âžś**

**Smallest to Largest**

âž¤Press

**OK**

Then you will get the values sorted in smallest to largest order.

âž¤Fill the **D5 **cell since it contains the lowest value after the sort operation, with any color of your wish to highlight the smallest value.

** Result**:

After that, you will get the highlighted lowest value.

__Method-2__: Using SMALL function

Here, I have used** the SMALL function** to get the lowest value in the **Marks column**.

** Step-01**:

âž¤Select

**Cell F5**

âž¤Type the following formula

`=SMALL(D5:D10,1)`

Here, **D5:D10 **is an **array **and **1 **is the **kth **value which returns the **kth** smallest value in a range.

** Result**:

Afterward, you will get the lowest value among the marks of the students.

__Method-3__: Finding Bottom 3 Values

If you want to know the bottom 3 values in the** Marks column **then you can use the **SMALL function**. For this purpose, I have added 2 extra columns under **Bottom 3 Values**.

** Step-01**:

âž¤Select

**Cell G5**

âž¤Type the following formula

`=SMALL($D$5:$D$10,F6)`

Here, **$D$5:$D$10 **is an **array **and **F6 **is the **kth **value which returns the **kth** smallest value in a range.

** Step-02**:

âž¤Press

**ENTER**

âž¤Drag down the

**Fill Handle**tool.

** Result**:

In this way, you will get the lowest 3 values among the marks of the students.

__Method-4__: Using INDEX-MATCH function

You can find the bottom 3 values using the **INDEX function **and **the MATCH function**.

** Step-01**:

âž¤Select

**Cell G5**

âž¤Type the following formula

`=INDEX(B5:B10,MATCH(SMALL($D$5:$D$10,F6),$D$5:$D$10,0))`

Here, **B5:B10 **is the range of **Student** names,**$D$5:$D$10 **is the range of values and **F6 **is the **kth **value which returns the **kth** smallest value in a range. **0 **is for an exact match.

** Step-02**:

âž¤Press

**ENTER**

âž¤Drag down the

**Fill Handle**tool.

Then you will get the name of the 3 students who have got the bottom 3 marks.

âž¤ Copy and Paste the marks according to the students in the **Marks column**.

Then you will have the lowest 3 values here.

**Read more:** **Conditional Formatting with INDEX-MATCH in Excel**

__Method-5__: Sorting Smallest to Largest Values Using the formula

I have used **the SMALL function **and **the ROWS function **to sort out the numbers in the smallest to largest order here. For this purpose, I have added a column; **Lowest to Highest Marks**.

** Step-01**:

âž¤Select

**Cell E5**

âž¤Type the following formula

`=SMALL($D$5:$D$10,ROWS( D$5:D5))`

Here, **$D$5:$D$10 **is an **array, **and **ROWS( D$5:D5) **will give the **kth **value in each row.

** Step-02**:

âž¤Press

**ENTER**

âž¤Drag down the

**Fill Handle**tool.

After that, you will get the values in ascending order in the **Lowest to Highest Marks column**.

âž¤If you want to mark the lowest value then fill cell **E5 **with any color of your wish as this cell contains the lowest mark.

** Result**:

Then you will get the highlighted lowest value.

__Method-6__: Using Conditional Formatting

You can highlight the lowest value using **Conditional Formatting**

** Step-01**:

âž¤Select the dataset excluding the header.

âž¤Go to

**Home**Tab>>

**Conditional Formatting**Dropdown>>

**Manage Rules**Option

**Conditional Formatting Rules Manager **Dialog Box will appear.

âž¤Select **New Rule **Option

Then the **New Formatting Rule **Dialog Box will appear.

âž¤Select the **Format only top or bottom ranked values **option.

âž¤Select **Bottom **and type **1 **for getting the lowest value only in the indicated area.

** Step-02**:

âž¤Click on

**Format**

Then the **Format Cells **wizard will pop up.

âž¤Select any color of your wish

âž¤ Press **OK**

âž¤Click **OK**

âž¤Then again Press **OK**.

** Result**:

Afterward, the lowest value in the

**Marks column**will be highlighted.

**Similar Readings:**

**How to Highlight Highest Value in Excel (3 Quick Ways)****Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)****Highlight Row Using Conditional Formatting (9 Methods)****How to Do Conditional Formatting Highlight Row Based On Date**

__Method-7__: Highlight Smallest Value with Criteria

Letâ€™s say you have the following data table where you have two marks for each student for two subjects. If you want to know for a student in which subject he has achieved the lowest marks then you can use the **SMALL function, the AND function, **and **Conditional Formatting**. Here, you will get the lowest mark for a student named **Michael**.

** Step-01**:

âž¤Select the dataset excluding the header.

âž¤Go to

**Home**Tab>>

**Conditional Formatting**Dropdown>>

**New Rule**Option

Then the **New Formatting Rule **Dialog Box will appear.

âž¤ Select the **Use a formula to determine which cells to format **Option

âž¤Write the following formula in the **Format values where this formula is true: **option

`=AND($B5=$F$6,$D5<=SMALL(IF($B$5:$B$12=$F$6,$D$5:$D$12),1))`

Within the **AND function **there are two criteria and when these two will be fulfilled the cell will be highlighted then.

**IF($B$5:$B$12=$F$6,$D$5:$D$12) **will give an array including **TRUE/FALSE **when the value in **F6 **matches in the range **$B$5:$B$12 **or not and the corresponding value in the range **$D$5:$D$12Â **

Then this array goes to the **SMALL function **with a value **1 **for **k **and gives the lowest value.

âž¤Follow **Step-02 **of **Method-6**

** Result**:

Afterward, you will get the lowest mark for Michael highlighted.

__Method-8__: Highlight Lowest Value in Each row

In the following data table, I have marks for different students for 3 subjects in 3 rows. If I want to highlight the lowest value for each subject then I have to use **Conditional Formatting** and **the MIN function**

** Step-01**:

âž¤Follow

**Step-01**of

**Method-7**

âž¤Type the following formula in the indicated box.

`=C6=MIN($C6:$F6)`

**The MIN function** will return the smallest value in each row.

** Result**:

Then you will get the lowest values in each row.

__Method-9__: Highlight Lowest Value in Each column

Assume you have marks for different students for 3 subjects in 3 rows. If you want to highlight the lowest value for each student then you have to use **Conditional Formatting** and **the MIN function**

** Step-01**:

âž¤Follow

**Step-01**of

**Method-7**

âž¤Type the following formula in the indicated box.

`=C6=MIN(C$6:C$8)`

**The MIN function** will return the smallest value in each column.

** Result**:

Afterward, you will get the highlighted lowest values in each column.

__Method-10__: Using SMALL Function for Dates

For knowing the smallest date that means the date of the project which has been completed first then you have to use **the SMALL function.**

** Step-01**:

âž¤Select

**Cell E5**

âž¤Type the following formula

`=SMALL(C5:C9,1)`

**C5:C9 **is the range of dates.

** Step-02**:

âž¤Press

**ENTER**

Then you will get the date of the first completed project.

__Method-11__: Finding Previous Date Closest to a Specified Date

If you want to know which project has been completed near before the due date then you have to use **the SMALL function** and **the COUNTIF function**

** Step-01**:

âž¤Select

**Cell E5**

âž¤Type the following formula

`=SMALL($C$5:$C$9,COUNTIF($C$5:$C$9,"<"&$E$4))`

**$C$5:$C$9 **is the range of dates.

** COUNTIF($C$5:$C$9,"<"&$E$4)** will provide the value for

**k**

** Result**:

Then you will get the date before the due date

If you want to get the nearest 2nd date before the due date then use the following formula

`=SMALL($C$5:$C$9,COUNTIF($C$5:$C$9,"<"&$E$4)-1)`

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below for each method in each sheet on the right side. Please do it by yourself.

## Conclusion

In this article, I tried to cover the easiest ways to highlight the lowest value in Excel effectively. Hope you will find it useful.If you have any suggestions or questions feel free to share them with us.