How to Highlight Lowest Value in Excel (11 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Fortunately, 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 11 easy methods, you will be able to highlight the lowest value effectively in Excel.


How to Highlight Lowest Value in Excel: 11 Suitable Ways

For explaining the methods I have used the following sample dataset. Here, it includes 3 columns that consist of the Marks of some Students in Physics. So, let’s get started with the explanation of these methods.

highlight lowest value in excel dataset


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

Firstly, 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. Hence, go through the steps below.

Steps:

  • Select the data range.
  • Then, go to the Home tab>> Editing dropdown>> Sort & Filter dropdown>> Custom Sort option.

Using Sort & Filter Option to Highlight Lowest Value in Excel

  • Sort Dialog Box will appear.
  • Now, select the Marks on Sort by and choose Smallest to Largest for Order.
  • Press OK.

Using Sort & Filter Option in Excel

  • Next, you will get the values sorted in the smallest to largest order.
  • Thereafter, fill the D5 cell since it contains the lowest value after the sort operation, with any color if you wish to highlight the smallest value.
  • Finally, you will get the highlighted lowest value.

Using Sort & Filter Option in Excel

Read More: How to Highlight Highest Value in Excel


Method-2: Using SMALL Function

Here, I have used the SMALL function to get the lowest value in the Marks column. Hence, go through the steps below.

Steps:

  • Select Cell F5.
  • Type the following formula.
=SMALL(D5:D10,1)

Using SMALL function to highlight lowest value in excel

  • Here, D5:D10 is an array and 1 is the kth value which returns the kth smallest value in a range.
  • Afterward, you will get the lowest value among the marks of the students.

Using SMALL function in Excel


Method-3: Finding Bottom 3 Values with SMALL Function

However, 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. Hence, go through the steps below.

Steps:

  • Select Cell G5.
  • Type the following formula.
=SMALL($D$5:$D$10,F6)

Finding Bottom 3 Values with SMALL Function

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

Using SMALL function in Excel

  • Drag down the Fill Handle tool.
  • In this way, you will get the lowest 3 values among the marks of the students.

Using SMALL function

Read More: How to Highlight a Column in Excel


Method-4: Combine INDEX and MATCH Functions

Furthermore, you can find the bottom 3 values using the INDEX function and the MATCH function. Hence, go through the steps below.

Steps:

  • Select Cell G5.
  • Type the following formula.
=INDEX(B5:B10,MATCH(SMALL($D$5:$D$10,F6),$D$5:$D$10,0))

Combine INDEX and MATCH Functions to highlight lowest value in excel

  • 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.
  • Press ENTER.
  • Drag down the Fill Handle tool.

Combine INDEX and MATCH Functions

  • Then, you will get the names of the 3 students who have got the bottom 3 marks.
  • Now, Copy and Paste the marks according to the students in the Marks column.
  • After that, you will have the lowest 3 values here.

Combine INDEX and MATCH Functions in Excel


Method-5: Combine SMALL and ROWS Functions to Sort Smallest to Largest Values

Additionally, 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. Hence, go through the steps below.

Steps:

  • Select Cell E5.
  • Type the following formula.
=SMALL($D$5:$D$10,ROWS( D$5:D5))

Combine SMALL and ROWS Functions to Sort Smallest to Largest Values in Excel

  • Here, $D$5:$D$10 is an array, and ROWS( D$5:D5) will give the kth value in each row.
  • 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.
  • Finally, you will get the highlighted lowest value.

Combine SMALL and ROWS Functions


Method-6: Using Conditional Formatting

Additionally, you can highlight the lowest value using Conditional Formatting. Hence, go through the steps below.

Steps:

  • Firstly, select the dataset excluding the header.
  • Secondly, go to Home tab>> Conditional Formatting dropdown>> Manage Rules option.

Using Conditional Formatting to highlight lowest value in excel

  • The Conditional Formatting Rules Manager dialog box will appear.
  • Select the New Rule option.

Using Conditional Formatting in Excel

  • Then, the New Formatting Rule dialog box will appear.
  • Next, select the Format only top or bottom ranked values option.
  • Afterward, select Bottom and type 1 to get the lowest value only in the indicated area.
  • Click on Format.

Using Conditional Formatting in Excel

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

Using Conditional Formatting in Excel

  • Click OK.

Using Conditional Formatting in Excel

  • Then, again Press OK.
  • Afterward, the lowest value in the Marks column will be highlighted.

Using Conditional Formatting in Excel

Read More: How to Highlight Text in Excel


Method-7: Highlight Smallest Value with Criteria

For instance, you have the following data table where you have two marks for each student for two subjects. However, 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. Hence, go through the steps below.

Steps:

  • 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.
  • After that, 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))

Using Conditional Formatting to Highlight Smallest Value with Criteria in Excel

  • Here, within the AND function, there are two criteria, and when these two are fulfilled the cell will be highlighted.
  • 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 of 1 for k and gives the lowest value.
  • Follow the steps of Method 6.
  • Afterward, you will get the lowest mark for Michael highlighted.

Using Conditional Formatting in Excel


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. Hence, go through the steps below. For the purpose of demonstration, I have changed the sample dataset a bit.

Highlight Lowest Value in Each Row in Excel

Steps:

  • Follow Step-01 of Method-7.
  • Type the following formula in the indicated box.
=C6=MIN($C6:$F6)

Using Conditional Formatting in Excel

  • Here, the MIN function will return the smallest value in each row.
  • Lastly, you will get the lowest values in each row.

Using Conditional Formatting in Excel


Method-9: Highlight Lowest Value in Each Column

Thereafter, 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. Hence, go through the steps below.

Steps:

  • Follow Step-01 of Method-7.
  • Type the following formula in the indicated box.
=C6=MIN(C$6:C$8)

Highlight Lowest Value in Each Column in Excel

  • Here, the MIN function will return the smallest value in each column.
  • Afterward, you will get the highlighted lowest values in each column.

Highlight Lowest Value in Each Column in Excel


Method-10: Using SMALL Function for Dates

In general, if you want to know the smallest date that means the date of the project which has been completed first then you have to use the SMALL function. Hence, go through the steps below. For the purpose of demonstration, I have selected the following sample dataset.

Using SMALL Function for Dates to highlight lowest value in excel

Steps:

  • Select Cell E5.
  • Type the following formula. Here, C5:C9 is the range of dates.
=SMALL(C5:C9,1)

Using SMALL Function for Dates

  • Press ENTER.
  • Then, you will get the date of the first completed project.

Using SMALL Function for Dates


Method-11: Finding Previous Date Closest to a Specified Date

Again, if you want to know which project has been completed before the due date then you have to use the SMALL function and the COUNTIF function. Hence, go through the steps below.

Steps:

  • Select Cell E5.
  • Type the following formula.
=SMALL($C$5:$C$9,COUNTIF($C$5:$C$9,"<"&$E$4))

Finding Previous Date Closest to a Specified Date in Excel

  • Here, $C$5:$C$9 is the range of dates and COUNTIF($C$5:$C$9,"<"&$E$4) will provide the value for k.
  • Lastly, you will get the date before the due date.

Using SMALL Function for Dates


Download Practice Workbook

You can download the following workbook to practice 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.


Related Articles


<< Go Back to Highlight in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo