Use Conditional Formatting to Highlight the Minimum and Maximum Values

Stop searching for the minimum and maximum values in your range and let Excel do the work for you by using conditional formatting

This will take you all of two minutes to read and understand

Download the accompanying file(s) for this article.

When presented with a large dataset you may need to know what the minimum and maximum values are. This article describes how to find those values using a formula, but what if you don’t want to create an additional cell to look at? The answer is simple use conditional formatting

In the accompanying workbook there is a list of 39 values. As described in this article you can use a formula to find the MIN and MAX values, but let’s be a little smarter and use conditional formatting

  1. Highlight the data range
  2. On the Home tab select Conditional Formatting -> Top/Bottom Rules –> Top 10 Items
    Click on the image to expand it
    Click on the image to expand it
    1. Change the 10 to 1 and select your formatting option

      Click on the image to expand it
      Click on the image to expand it
  3. Click OK
  4. The highest value in the list will now be highlighted in your chosen format


To find the minimum value follow the same steps as above but select ‘Bottom 10 Items’ from the list and change the 10 to 1

Click on the image to expand it
Click on the image to expand it

The beauty with using this method of conditional formatting is that you can easily change formatting from showing the highest value to showing the highest 5 values or 20 values.

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Excel Basics


Leave a comment

Your e-mail address will not be published. Required fields are marked *