Formatting Issues

How to Apply Excel Conditional Formatting for 30% and 60% Thresholds

Maira Mehtab Sep 20, 2026 868 views

Question details

The user wants to format entire rows using color codes based on percentage comparisons, specifically making rows red when Column A is less than 30% of Column B, and yellow when it is less than 60%.

Product: Excel
Device & OS: not provided
Scenario: Comparing data values across two columns to visualize performance metrics or thresholds using conditional formatting rules.
Observed behavior: The goal state is for the row to turn red if the value in Column A is below 30% of the value in Column B, and yellow if it is below 60%.

Before you start

Ensure that the selected data range contains properly formatted numeric values or percentages rather than text strings, as formatting formulas cannot calculate text.

Solution 1Recommended

Use Formula-Based Conditional Formatting Rules

Create custom formula rules to compare Column A and Column B, and set specific rule orders to ensure colors apply correctly without overlapping.

When applying multiple conditional formatting rules to the same range, the order of the rules matters. Excel evaluates rules from top to bottom. Because any value less than 30% is also inherently less than 60%, the stricter rule (less than 30%) must be placed above the less strict rule (less than 60%) to prevent the yellow rule from overriding the red one.

Select your data range

Highlight the entire table or the specific rows you want to format, starting from row 2 (assuming row 1 contains headers).

Create the 30% threshold rule (Red)

Navigate to the Home tab, click 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'. Enter the formula: =$A2<$B2*30% . Click 'Format', set the fill color to Red, and click 'OK'.

Create the 60% threshold rule (Yellow)

Repeat the previous step by clicking 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Enter the formula: =$A2<$B2*60% . Click 'Format', set the fill color to Yellow, and click 'OK'.

Manage rule hierarchy

Go to 'Conditional Formatting' > 'Manage Rules'. Verify that the Red rule (=$A2<$B2*30%) is located above the Yellow rule. If it is not, use the up arrow to move it to the top. Check the 'Stop If True' box next to the Red rule and click 'Apply'.

Understanding Absolute References: The dollar sign ($) in =$A2 locks the column reference. This ensures that every cell in that row is evaluated based on the values in Column A and Column B, allowing the entire row to be highlighted.

WPS Spreadsheet Solution

Apply Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for managing complex conditional formatting rules. You can seamlessly apply formula-based formatting across your datasets just like you would in Microsoft Excel.

1. Highlight your data: Select the rows you want to format in your WPS Spreadsheet document.
2. Access Conditional Formatting: Go to the 'Home' tab, click on 'Conditional Formatting', and select 'New Rule'.
3. Enter the formula: Choose 'Use a formula to format cells', enter your threshold formula (e.g., =$A2<$B2*30%), and select your desired highlight color.
4. Order your rules: Click 'OK', then open 'Manage Rules' to ensure your stricter threshold is placed at the top.

100% compatible with Microsoft Excel conditional formatting formulasLightweight and fast, even when formatting large datasetsFree to use for everyday spreadsheet tasksIntuitive 'Manage Rules' interface for easily reordering threshold rules

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting formula not being accepted?

This usually happens if there is a syntax error in the formula, such as missing the equals sign (=) at the beginning, or including stray spaces. Additionally, if your regional computer settings use commas as decimal separators, you might need to use semicolons (;) instead of commas (,) in more complex functions. Ensure the syntax matches exactly: =$A2<$B2*30%.

Why are all my rows turning yellow instead of red?

This occurs when the rule order is incorrect. Because any number under 30% is also under 60%, placing the '<60%' rule above the '<30%' rule causes Excel to trigger the 60% rule first and stop looking further. You must move the '<30%' red rule to the top in the Conditional Formatting Rules Manager.

How do I make the color apply to the whole row instead of just one cell?

To highlight the entire row, you must use a mixed reference in your formula by placing a dollar sign ($) before the column letter, such as =$A2. This anchors the formula to check that specific column while allowing it to format all the cells across the entire row.

Document & File Issues