Formatting Issues

Automatically Color Average Values with Conditional Formatting in Excel

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Apply different colors to average values according to their numeric ranges.

Automatically Color Average Values with Conditional Formatting in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the average cells

In Excel, select A12:D12, or select your own range of average values.

2
Create the first rule

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.

3
Add the remaining rules

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

4
Check the applied range

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.

Create a conditional formatting rule for each average range
Range boundaries: In this example, 2 is in the 2–3 range, 3 is in the 3–4 range, and both 4 and 5 are in the 4–5 range. Values above 5 have no rule.
Format averages in WPS Spreadsheets

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the average cells, such as A12:D12.
  2. 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. 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. 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.
Automatically highlight average values by rangeOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceCreate and review conditional formatting rules in one place
microsoft office alternative - wps office

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.