How to Highlight Lowest Value in Excel (11 Ways)

Highlight in each column

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.

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.

dataset

dataset

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.

Sort & Filter

Step-01:
➤Select the data range
➤Go to Home Tab>>Editing Dropdown>>Sort & Filter Dropdown>>Custom Sort Option

Sort & Filter

Sort Dialog Box will appear.

Sort & Filter

➤Select the following options
Sort by Marks
Sort On Cell Values
OrderSmallest to Largest
➤Press OK

Sort & Filter

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

Sort & Filter

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

Sort & Filter

Method-2: Using SMALL function

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

SMALL function

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.

SMALL function

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

SMALL function

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.

SMALL function

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.

SMALL function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

highlight lowest value in Excel

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

Bottom 3 values

Method-4: Using INDEX-MATCH function

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

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

INDEX-MATCH function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

INDEX-MATCH function

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

INDEX-MATCH function

➤ Copy and Paste the marks according to the students in the Marks column.
Then you will have the lowest 3 values here.

INDEX-MATCH function

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.

sorting with formula

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.

sorting with formula

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

sorting with formula

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

sorting with formula

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

sorting with formula

Method-6: Using Conditional Formatting

You can highlight the lowest value using Conditional Formatting

conditional formatting

Step-01:
➤Select the dataset excluding the header.
➤Go to Home Tab>>Conditional Formatting Dropdown>>Manage Rules Option

conditional formatting

Conditional Formatting Rules Manager Dialog Box will appear.
➤Select New Rule Option

conditional formatting

Then the New Formatting Rule Dialog Box will appear.
➤Select the Format only top or bottom ranked values option.

conditional formatting

➤Select Bottom and type 1 for getting the lowest value only in the indicated area.

conditional formatting

Step-02:
➤Click on Format

conditional formatting

Then the Format Cells wizard will pop up.
➤Select any color of your wish
➤ Press OK

conditional formatting

➤Click OK

conditional formatting

➤Then again Press OK.

conditional formatting

Result:
Afterward, the lowest value in the Marks column will be highlighted.

conditional formatting


Similar Readings:


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.

highlight with criteria

Step-01:
➤Select the dataset excluding the header.
➤Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option

highlight with criteria

Then the New Formatting Rule Dialog Box will appear.
➤ Select the Use a formula to determine which cells to format Option

highlight with criteria

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

highlight with criteria

➤Follow Step-02 of Method-6

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

highlight with criteria

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

Highlight in each row

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.

Highlight in each row

Result:
Then you will get the lowest values in each row.

Highlight 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

Highlight in each column

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.

Highlight in each column

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

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

Smallest date

Step-01:
➤Select Cell E5
➤Type the following formula

=SMALL(C5:C9,1)

C5:C9 is the range of dates.

Smallest Date

Step-02:
➤Press ENTER
Then you will get the date of the first completed project.

Smallest Date

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

before specified date

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

before specified date

Result:
Then you will get the date before the due date

before specified 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)

before specified date

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.

practice

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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo