Automatically Color Average Values with Conditional Formatting in Excel
Question details
Apply different colors to average values according to their numeric ranges.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains average values that should be highlighted differently when they fall below 2, from 2 to 3, from 3 to 4, or from 4 to 5.
- Observed behavior
- Each average cell should change color automatically when its value enters a defined range.
Identify the cells containing the numeric average values and choose a fill color for each range. The example uses A12:D12, with A12 as the top-left cell.
Create a conditional formatting rule for each average range
Use formula-based rules to assign a different fill color to each range and update colors automatically as values change.
In Excel, select A12:D12, or select your own range of average values.
On the Home tab, select Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(ISNUMBER(A12),A12<2), select Format, choose a fill color, and click OK.
Repeat Home > Conditional Formatting > New Rule for each formula, choosing a different fill color for each: =AND(A12>=2,A12<3), =AND(A12>=3,A12<4), and =AND(A12>=4,A12<=5).
Open Home > Conditional Formatting > Manage Rules and confirm that each rule applies to =$A$12:$D$12. If you selected a different range, use its top-left cell in the formulas and set Applies to to that range.

Color average ranges automatically in WPS Spreadsheets
Use formula-based conditional formatting in WPS Spreadsheets to apply distinct colors to average values and update them as the values change.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the average cells, such as A12:D12.
- 2. Create a formula rule: On the Home tab, open Conditional Formatting > New Rule and choose the option to use a formula. Enter =AND(ISNUMBER(A12),A12<2), then choose a fill color.
- 3. Add the other ranges: Create separate rules for =AND(A12>=2,A12<3), =AND(A12>=3,A12<4), and =AND(A12>=4,A12<=5), assigning a different fill color to each.
- 4. Review the rules: Open Conditional Formatting > Manage Rules and check that each rule applies to A12:D12. Use the top-left cell of your selected range in the formulas if your range differs.

Frequently Asked Questions
Will the colors change when an average formula recalculates?
Yes. Conditional formatting evaluates the resulting cell value, so the fill updates when the average changes and the worksheet recalculates.
Why do the formulas use A12 instead of $A$12?
A12 is a relative reference, so Excel evaluates each cell in the selected range against its own value. Use the top-left cell of your Applies to range as the reference.
How can I change a range's color later?
In Excel, go to Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule. Open Format to change its fill color.
What happens to values above 5?
The example rules do not format values above 5. To color them, add another rule through Home > Conditional Formatting > New Rule, using a formula such as =A12>5.




