Applying a New Rule using Conditional Formatting

Question

I am trying to apply the grading table below to the data in the table above, but I cannot find where to customize.

Answer 1

Use a custom conditional formatting rule based on a formula and apply it to the score range.

Assume the grading table (score targets) is in fixed cells, for example:

• Score 1 threshold in $B$24
• Score 2 threshold in $C$24
• Score 3 threshold in $D$24
• Score 4 threshold in $E$24
• Score 5 threshold in $F$24 and the data to format is in row 5, columns B:F.

For each color band, create a separate rule:

1. Select the data cells to format (for example, B5:F18).
2. On Home > Styles > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format (or Rule Type: Formula in Excel for the web).
4. Enter a formula that tests the value in the first cell of the selection against the grading table, such as:
   • Red (score 1): =B5<$B$24
   • Orange (score 2): =AND(B5>=$B$24,B5<$C$24)
   • Yellow (score 3): =AND(B5>=$C$24,B5<$D$24)
   • Light green (score 4): =AND(B5>=$D$24,B5<$E$24)
   • Dark green (score 5): =B5>=$E$24
5. Click Format, choose the fill color that corresponds to that score band, and click OK.
6. Repeat steps 2–5 for each band, using the same Applies to range (B5:F18) and adjusting only the formula and color.
7. In Manage Rules, ensure the rules are listed in the correct order (from most specific to least, or with Stop If True where needed).

This uses formulas so the data table above always picks up the thresholds from the grading table below; changing the grading table automatically updates the colors.

---

References:

• Use conditional formatting to highlight information in Excel

Answer 2

Hi @Cesar Martinez

It sounds like you're trying to use the values in the Score Targets table as the thresholds for Conditional Formatting on the data above.

If so, Excel's built-in Color Scale rules do not support a multi-level grading table directly. Color Scales can only use a minimum, midpoint, and maximum value. To apply the exact score bands shown in your table (0–6), you would need to create multiple Conditional Formatting rules or use a helper column that calculates the score and then format based on that score.

To see how the current formatting is configured and where it can be customized:

1. Select one of the cells in the formatted range.
2. Go to Home > Conditional Formatting > Manage Rules.
3. In Show formatting rules for, select This sheet.
4. Review the rules that apply to the selected column.
5. Select a rule and click Edit Rule to view or modify its thresholds, formula, or color settings.

If the rule type is a Color Scale, you'll only be able to customize the Minimum, Midpoint, and Maximum values. If you're trying to match the exact Score Targets table (0–6), you'll need multiple formula-based rules for each metric column because each metric appears to have its own set of threshold values.

Could you confirm whether you're trying to:

• Create a red-yellow-green gradient based on the target values, or
• Apply the exact score bands from the grading table (0–6)?

Please know that while our initial response might not resolve the issue right away, your input is incredibly valuable. With a bit more detail, we’ll work together to find the best solution for you.         

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.