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.

excel conditional formatting 3 color scale formula


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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

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

Use IF Function to Apply Conditional Formatting with 3 Color Scale

Read More: How to Use 4 Color Scale Conditional Formatting in Excel


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.

excel conditional formatting 3 color scale formula

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

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


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.


Download Practice Workbook

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


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.


Related Articles

<< Go Back to Color Scales | Conditional Formatting | 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.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo