Conditional Formatting with 3 Color Scale in Excel Formula

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.

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

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.

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.

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 which has been given in the below screenshot.

Use IF Function to Apply Conditional Formatting with 3 Color Scale


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.

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.


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.

Md. Abdur Rahim Rasel

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

Leave a reply

ExcelDemy
Logo