How to Apply Conditional Formatting with 3 Color Scale in an Excel Formula – 2 Methods

The dataset is aÂ large Excel worksheet that contains information about several students.

Method 1 – Using the IF Function to Apply Conditional Formatting with a 3 Color Scale

Using the IF function:

Step 1:

• Select D6:F15.
• In the Home tab, go to

Home â†’ styles â†’ Conditional Formatting â†’ Manage Rule

• The Conditional Formatting Rules Manager dialog box opens.
• Click New Rule.

• In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
• Enter the following formula :
`=D6=IF(D6<=40,D6,0)`
• It will highlight cells whose value is less than or equal to 40.
• Click Format.

• In the Format Cells dialog box, select Fill.
• Choose a color, here Red.
• Click OK.

• Click OK.

• Check Stop IF True (to make sure your formula will work for rows only)
• ClickÂ  New Rule to add more formulas.

Step 2:

• Click New Rule.
• In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
• Enter the following formula :
`=D6=IF(D6>=80,D6,0)`
• It will highlight the cells whose value is greater than or equal to 80.
• Click Format.

• In the Format Cells dialog box, select Fill
• Choose a color, hereÂ  Green.
• Click OK.

• Click OK.

• Check Stop IF True (to make sure your formula will work for rows only).
• Click New Rule to add more formulas.

Step 3:

• Click New Rule. The New Formatting Rule dialog box will open
• In the New Formatting Rule dialog box, select Format only cells that contain.
• Select Cell Value.
• Choose less than or equal to.
• Enter 79 under Format only cells with.
• Click Format.

• In the Format Cells dialog box, select Fill.
• Choose a color, here Yellow.
• Click OK.

• Click OK.

• Check Stop IF True (to make sure your formula will workÂ  for rows only)
• Click New Rule to add more formulas.

• This is the output.

Method 2 – Applying a 3 Color Scale Command in Conditional Formatting

Step 1:

• Select D6:F15.
• In the Home tab, go to

Home â†’ styles â†’ Conditional Formatting â†’ New Rule

Step 2:

• ClickÂ  New Rule to see the New Formatting Rule dialog box.
• In Select a Rule Type, select Format all cells based on their values.
• InÂ Format Style, select 3-Color Scale.
• In Minimum, select Number from the Type drop-down list. Enter 40 inÂ  Value. Select Orange from the Color drop-down list.
• In Midpoint, select Number from the Type drop-down list. Enter 65 in Value. Select YellowÂ from the Color drop-down list.
• In Maximum, select Number from the Type drop-down list. Enter 80 in Value. Select GreenÂ from the Color drop-down list.
• Click OK.

• This is the output.

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 is displayed when a value is divided by zero(0) or the cell reference is blank.

Related Articles

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF