Formatting Issues

How to Set Up 4-Color Conditional Formatting Thresholds in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to apply four distinct conditional formatting colors to daily totals (such as low, target, warning, and high) based on multiple numerical thresholds.

Product: Excel
Device & OS: not provided
Scenario: Tracking daily nutrition totals or similar metrics that require multi-tier color coding based on customizable threshold values.
Observed behavior: The cell background color should automatically change to one of four designated colors depending on which numerical threshold range the daily total falls into.

Before you start

Before starting, ensure you have the exact numerical boundaries for your four thresholds (e.g., low, target, warning, high) and clearly identify the specific cells you want to format.

Solution 1Recommended

Create Separate Formula-Based Conditional Formatting Rules

The most precise way to handle four specific thresholds is to create separate conditional formatting rules using logical Excel formulas.

By utilizing custom formulas for your conditional formatting rules, you can handle multiple strict threshold ranges. The AND function is especially useful for capturing values that fall strictly between two numbers.

Select the Target Cells

Highlight the cells containing your daily totals (for example, cell C20).

Open the New Rule Dialog

Navigate to the Home tab, click on Conditional Formatting, and select 'New Rule'.

Set the Low Threshold Rule

Select 'Use a formula to determine which cells to format'. Enter the formula =C20<1900, click Format to choose your desired 'low' color, and click OK.

Set the Target and Warning Rules

Create two more rules using the same process. For the target range, use =AND(C20>=1900,C20<2300) and pick a target color. For the warning range, use =AND(C20>=2300,C20<=2500) and pick a warning color.

Set the High Threshold Rule

Create a final rule using the formula =C20>2500, apply a 'high' alert color like red or pink, and confirm your rules.

Check Rule Priorities: After adding your formulas, open the Conditional Formatting Rules Manager to ensure the boundaries do not overlap and the rules evaluate in your intended order.

Solution 2

Use Lookup Tables for Dynamic Thresholds

If your threshold limits vary based on specific categories like meal times, you can reference a lookup table in your formatting rules.

Advanced Spreadsheet Formatting

Apply Multiple Conditional Formatting Rules Easily in WPS Office

WPS Office provides an intuitive Conditional Formatting Rules Manager that fully supports complex logical formulas like AND/OR, making it incredibly simple to set up multi-color thresholds for any dataset.

1. Open Your Data: Launch WPS Spreadsheet and open the document containing your daily totals.
2. Access Conditional Formatting: Highlight the target cells, navigate to the Home tab, and select 'Conditional Formatting' > 'New Rule'.
3. Input the Formulas: Choose the formula option, enter your threshold logic (e.g., =C20<1900), and pick your custom fill colors.
4. Manage Formatting Rules: Use the Rules Manager to review all four rules at once and ensure your color priorities are correct.

Fully compatible with Microsoft Excel conditional formatting rules and formulas.Intuitive Rules Manager makes it easy to adjust formatting priority, colors, and ranges.Lightweight and completely free to use for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my conditional formatting colors overlapping or not showing correctly?

This usually happens due to conflicting boundary conditions or incorrect rule priorities. Open the Conditional Formatting Rules Manager and ensure your rules are evaluated in the correct order, checking the 'Stop If True' boxes if necessary.

Can I apply these 4-color threshold rules to an entire column?

Yes. Select the entire column (e.g., Column C) before creating the rule. Ensure your formula references the top cell of your selection without absolute row locking (e.g., =C1<1900 instead of =$C$1<1900).

Is it possible to base conditional formatting on text instead of numbers?

Yes. You can choose 'Format only cells that contain' from the New Rule menu, select 'Specific Text', and type the specific word or phrase you want to trigger the color change.

Document & File Issues