Excel Formulas to Highlight 3 Smallest Values with Criteria!!
Tags: excelformula, excelformulas
Published: 2021-03-08 11:45:23

---
title: Excel Formulas to Highlight 3 Smallest Values with Criteria!!
published: true
date: 2021-03-08 11:43:38 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-to-highlight-3-smallest-values-with-criteria/
---

Have you ever tried to highlight the largest or lowest value in each row or column? It will be tedious if you identify the values one by one in each row or column. In this case, the [**Conditional Formatting**](https://geekexcel.com/how-to-color-conditional-formatting-drop-down-list-in-excel/) feature in Excel can do you a favor. Here, we will show simple steps to **highlight the 3 smallest values based on specified criteria in Excel**. Let’s get started!! Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

[![Highlight Smallest 3 values with criteria](https://geekexcel.com/wp-content/uploads/2021/03/Highlight-Smallest-3-values-with-criteria.png)](https://geekexcel.com/excel-formulas-to-highlight-3-smallest-values-with-criteria/highlight-smallest-3-values-with-criteria/#main)<figcaption id="caption-attachment-22337">Highlight Smallest 3 values with criteria</figcaption>
## General Formula:

- You can use the below formula to highlight the smallest 3 values in Excel.

**=[AND](https://geekexcel.com/how-to-use-logical-function-and-in-excel-365/)(A1=criteria,B1<=[SMALL](https://geekexcel.com/how-to-use-excel-small-function-in-office-365-with-examples/)([IF](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/)(criteria,values),3))**

## Syntax Explanations:

- **AND** – This function checks Multiple Conditions and returns **[TRUE](https://geekexcel.com/use-true-function-in-microsoft-excel-365-simple-methods/) **if all the conditions are evaluated as true otherwise returns **[FALSE](https://geekexcel.com/use-false-function-in-microsoft-excel-365-simple-methods/).** Read more on the **[AND function](https://geekexcel.com/how-to-use-logical-function-and-in-excel-365/).**
- **SMALL** – The **[SMALL function](https://geekexcel.com/how-to-use-excel-small-function-in-office-365-with-examples/)** helps to return the N<sup>th</sup> smallest value from the given range of data.
- **IF** – In Excel, the [**IF function**](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/) will return one value for a TRUE result, and another for a FALSE result
- **Criteria** – It represents the criteria value.
- **Values** – It represents the input values.
- **Comma symbol (,)** – It is a separator that helps to separate a list of values.
- **Parenthesis ()** – The main purpose of this symbol is to group the elements.

## Practical Example:

Refer to the below example image.

- First, we will enter the input values in **Column B** and **Column C**.
- Here, we are going to highlight the values based on the criteria given in **Column E**.

![Input Ranges](https://geekexcel.com/wp-content/uploads/2021/03/Input-Ranges-9.png)<figcaption id="caption-attachment-22336">Input Ranges</figcaption>

- Select the input range and go to the **Home** tab from the **Ribbon** and select the **Conditional Formatting** option.

![Select conditional formatting](https://geekexcel.com/wp-content/uploads/2021/03/Select-conditional-formatting.png)<figcaption id="caption-attachment-22332">Select conditional formatting</figcaption>

- It will expand the drop-down list and here you need to select the **“New Rule”**.

![New rule](https://geekexcel.com/wp-content/uploads/2021/03/New-rule.png)<figcaption id="caption-attachment-22329">New rule</figcaption>

- Here, you need to select the **“Use a formula to determine which cells to format”** option.

![Select formula option](https://geekexcel.com/wp-content/uploads/2021/03/Select-formula-option.png)<figcaption id="caption-attachment-22333">Select formula option</figcaption>

- Apply the formula in the selected box and click on the **“Format”** option.

![Apply the formula](https://geekexcel.com/wp-content/uploads/2021/03/Apply-the-formula.png)<figcaption id="caption-attachment-22334">Apply the formula</figcaption>

- It will open the **Format cells** dialog box, and **select the color** you like to fill the tab then click on the **OK** button.

![Select color](https://geekexcel.com/wp-content/uploads/2021/03/Select-color.png)<figcaption id="caption-attachment-22331">Select color</figcaption>

- Now the color will be applied to the format dialog box, click the **“OK”** button.

![Click OK button](https://geekexcel.com/wp-content/uploads/2021/03/Click-OK-button.png)<figcaption id="caption-attachment-22335">Click OK button</figcaption>

- Finally, it will highlight the values and display the result as per the below image.

![Result](https://geekexcel.com/wp-content/uploads/2021/03/Result-12.png)<figcaption id="caption-attachment-22330">Result</figcaption>

## Closure:

From this tutorial, you can get some clarification on how to **highlight the cells that contain the smallest 3 values based on the given criteria in Excel**. Hope you like this article. If you have any  **queries/doubts** , kindly share it in the below comment section. Thanks for visiting **[Geek Excel](https://geekexcel.com/) **and [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

### Read Also:

- **[Excel Formulas to Group the Arbitrary Text Values ~ Simple Tutorial!!](https://geekexcel.com/excel-formulas-to-group-the-arbitrary-text-values/)**
- **[Excel Formulas to Check the Cell Value Based on IF Else Condition!!](https://geekexcel.com/excel-formulas-to-check-the-cell-value-based-on-if-else-condition/)**