Formatting Issues

How to Apply Excel Conditional Formatting for Three Percentage Ranges in One Cell

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants a single Excel cell to change its background color depending on which of three specific percentage ranges its value falls into (green for 36.99% or less, orange for 37% to 50.99%, and red for 51% or higher).

Product: Excel
Device & OS: not provided
Scenario: Setting strict color-coded visual indicators for different percentage thresholds in a single cell.
Observed behavior: Standard 3-color scales blend the colors inappropriately for a single cell instead of providing distinct, solid colors for each strictly defined range.

Before you start

Ensure that your target cell actually contains a numeric percentage value rather than text. If the cell is formatted as text, the numerical rules will not evaluate correctly.

Solution 1Recommended

Apply Three Separate Formula-Based Rules

Use individual formula rules instead of the default 3-color scale. A color scale automatically calculates gradients based on a range of multiple cells, whereas individual formulas ensure strict, solid color cutoffs for a single cell.

By setting up three distinct logical formulas, you force Excel to evaluate the exact percentage and apply the matching solid fill color without any blending.

Select the target cell

Click on the single cell where you want to apply the formatting (for example, cell A1).

Create the first rule (Green)

Go to the Home tab, click Conditional Formatting, and select New Rule. Choose 'Use a formula to determine which cells to format'. Enter the formula =A1<=36.99%, click the Format button to choose a green fill color, and click OK.

Create the second rule (Orange)

Add another New Rule for the same cell. Use the formula =AND(A1>=37%,A1<=50.99%). Click Format, select an orange fill color, and click OK.

Create the third rule (Red)

Add a third New Rule using the formula =A1>=51%. Choose a red fill color in the Format menu and click OK.

Managing Rules: You can always edit or check your thresholds by selecting the cell and navigating to Conditional Formatting > Manage Rules.

Solution 2

Use Conditional Formatting Icon Sets

If you do not strictly require cell background colors, Icon Sets provide an excellent built-in alternative for visualizing thresholds in a single cell without color blending.

WPS Spreadsheet Solution

Easily Manage Conditional Formatting Rules in WPS Office

WPS Spreadsheet provides a highly intuitive Conditional Formatting manager, making it simple to create, edit, and prioritize multiple custom formulas for specific percentage ranges. It perfectly executes standard Excel formatting rules.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your target percentage cell.
2. Access Conditional Formatting: Select the cell, navigate to the Home tab, and click Conditional Formatting > New Rule.
3. Input formulas: Select the formula option, enter your percentage limits (e.g., =A1<=36.99%), and set the required fill colors just as you would in standard Excel.

Intuitive Rule Manager for managing multiple cell conditionsSeamless compatibility with Microsoft Excel (.xlsx) formulasLightweight, fast, and completely free to useSupports complex AND/OR logical formulas seamlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use the built-in 3-Color Scale for a single cell?

The 3-Color Scale feature calculates color gradients by comparing the highest, lowest, and midpoint values across a range of multiple cells. When applied to a single cell, it lacks comparative data and simply blends colors, failing to provide strict solid color cutoffs based on static rules.

Do I have to type the '%' sign in my conditional formatting formula?

Yes, if your cell is formatted as a percentage, typing the '%' sign in the formula (like =A1<=36.99%) ensures Excel evaluates it correctly. Alternatively, you can use the exact decimal equivalent, such as =A1<=0.3699.

What if my cell value is exactly 36.995%?

The formulas provided (=A1<=36.99% and =A1>=37%) leave a microscopic gap between 36.99% and 37%. If you have higher precision decimals, you should adjust the rule to something like =A1<37% for the first rule and =A1>=37% for the second to ensure no values fall through the cracks.

Why is my conditional formatting rule not triggering properly?

This commonly happens if the cell contains a text string that just looks like a percentage. Select the cell, change its format to 'Number' or 'Percentage' in the Home tab, and ensure there are no hidden spaces or characters.

Document & File Issues