While working with a large worksheet in **Microsoft Excel**, we need to highlight cells using the **3-color scale** in **Excel**. You can use various ways to highlight cells based on their value in **Excel**. We will apply **Conditional Formatting** with 3-color scale commands to **highlight cells**. This will save you a lot of time and energy. Today, in this article, we’ll learn **two **quick and suitable ways to apply the **conditional formatting 3 color scale** formula in **Excel **effectively with appropriate illustrations.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 2 Effective Ways to Apply Conditional Formatting with 3 Color Scale in Excel Formula

Let’s assume we have an **Excel **large worksheet that contains the information about the several students of the **Armani School**. From our dataset, we will apply Conditional Formatting with **3 color scale** commands as well as **the IF function** to **highlight cells**, and so on. Here’s an overview of the dataset for today’s task.

### 1. Use IF Function to Apply Conditional Formatting with 3 Color Scale

Undoubtedly, using **the IF function** in conditional formatting is an easy and time-saving task to highlight cells. We will apply **the IF function** in our dataset highlight cells. Let’s follow the instructions below to learn!

**Step 1:**

- First of all, select cells
**D6**to**F15**from our dataset. After that, from your**Home**tab, go to,

**Home → styles → Conditional Formatting → Manage Rule**

- After that, the
**Conditional Formatting Rules Manager**dialog box will pop up. Hence, click on**New Rule**.

- In the
**New Formatting Rule**dialog box, select**Use a formula to determine which cells to format**. Then, type the following formula :

`=D6=IF(D6<=40,D6,0)`

- This will highlight the cells whose value is
**less than or equal to 40**. Then click on**Format**.

- From the
**Format Cells**dialog box, select the**Fill**Choose any fill colors. We will select**Red**color. Then click**OK**.

- Now, you have set your formula and fill color. Now, click on
**OK**.

- Hence, mark the
**Stop IF True**This is important. It will make sure your formula will work only for rows independently. Then click on**New Rule**to add more formulas.

**Read More: How to Use 4 Color Scale Conditional Formatting in Excel (2 Examples)**

**Step 2:**

- Now, again, click on
**New Rule**. In the**New Formatting Rule**dialog box, select**Use a formula to determine which cells to format**. Then, type the following formula :

`=D6=IF(D6>=80,D6,0)`

- This will highlight the cells whose value is
**greater than or equal to 80**. Then click on**Format**.

- From the
**Format Cells**dialog box, select the**Fill**Choose any fill colors. We will select a**Green**color. Then click**OK**.

- Now, you have set your formula and fill color. Now, click on
**OK**.

- Hence, mark the
**Stop IF True**This is important. It will make sure your formula will work only for rows independently. Then click on**New Rule**to add more formulas.

**Step 3:**

- Now, again, click on
**New Rule**. As a result, the**New Formatting Rule**dialog box will appear in front of you. From the**New Formatting Rule**dialog box, select**Format only cells that contain**. After that, select**Cell Value**. Hence, select**less than or equal to**. Further, type**79**under the**Format only cells with**Then click on**Format**.

- From the
**Format Cells**dialog box, select the**Fill**Choose any fill colors. We will select the**Yellow**color. Then click**OK**.

- Now, you have set your formula and fill color. Now, click on
**OK**.

- Hence, mark the
**Stop IF True**This is important. It will make sure your formula will work only for rows independently. Then click on**New Rule**to add more formulas.

- After completing the above process, you will be able to use the
**IF function**to highlight the cell which has been given in the below screenshot.

### 2. Apply 3 Color Scale Command in Conditional Formatting

In this portion, we will use **conditional formatting** with **3 color scale** commands. For our dataset, we can easily do that. We will highlight those cells whose value is **greater than 40** in **Orange** color, cells that contain a value **around 65** in **Yellow **color, and cells that contain a value **equal to or greater than 80** in **Green **color. Let’s follow the instructions below to learn!

**Step 1:**

- First of all, select cells
**D6**to**F15**from our dataset. After that, from your**Home**tab, go to,

**Home → styles → Conditional Formatting → New Rule**

**Read More: Color Scale Per Row with Conditional Formatting in Excel**

**Step 2:**

- To click on the
**New Rule**option, as a result, the**New Formatting Rule**dialog box will appear in front of you. From the**New Formatting Rule**dialog box, firstly, select**Format all cells based on their values**from**Select a Rule Type.**Secondly, select the**3-Color Scale**from the**Format Style**drop-down list. Thirdly, in the**Minimum**portion, first of all, select the**Number**from the**Type**drop-down list. After that, type**40**in the typing box named**Value**. Hence, select**Orange**color from the**Color**drop-down list. - Now, for the
**Midpoint**portion, first of all, select the**Number**from the**Type**drop-down list. After that, type**65**in the typing box named**Value**. Hence, select**Yellow**color from the**Color**drop-down list. - Similarly, for the
**Maximum**portion, first of all, select the**Number**from the**Type**drop-down list. After that, type**80**in the typing box named**Value**. Hence, select the**Green**color from the**Color**drop-down list. - At last, press
**OK**.

- After completing the above process, you will be able to use the
**3-color scale command**to highlight the cell which has been given in the below screenshot.

## Things to Remember

👉 You also can use the AND function instead of the IF function in conditional formatting with 3 color scale. **The AND function** is,

For **Red** color: **=AND(D6<40)**

**Yellow**: **=AND((D6>=40),(D6<80))**

**Green**: **=AND(D6>=80)**

👉 **#N/A!** error arises when the formula or a function in the formula fails to find the referenced data.

👉 **#DIV/0!** error happens when a value is divided by **zero(0)** or the cell reference is blank.

**Read More: How to Use Conditional Formatting with 5 Color Scale in Excel **

## Conclusion

I hope all of the suitable steps mentioned above to **apply conditional formatting ****3 color scale** will now provoke you to apply them in your **Excel **spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.