# Conditional Formatting with 3 Color Scale in Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

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.

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

Letâ€™s assume we have aÂ large Excel worksheet that contains information about the several students. 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 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.

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 that 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

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF