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

The sample dataset is a large Excel worksheet containing information about several students.

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

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 :

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

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

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

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.

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

Download Practice Workbook

Download this practice workbook to exercise.

Related Articles

• Excel Conditional Formatting Color Scale Based on Another Cell
• How to Use Excel Color Scale Based on Text
• How to Use Conditional Formatting with 5 Color Scale in Excel

<< Go Back to Conditional Formatting | Learn Excel