Formatting Issues

How to Change Cell Color for Values Above or Below a Threshold in Excel

John Wilson Oct 16, 2026 870 views

Question details

The user wants to automatically format a range of cells based on whether their values exceed a maximum threshold or fall below a minimum threshold.

How to Change Cell Color for Values Above or Below a Threshold in Excel

Product: Excel
Device & OS: not provided
Scenario: Highlighting specific data points that fall outside an acceptable numeric range for better data analysis.
Observed behavior: Cells D2:D10 need to dynamically change color if their numeric value is greater than 11 or less than 7.

Before you start

Ensure you select the exact range of cells you wish to format (e.g., D2:D10) before applying the rules so the formatting is not accidentally applied to headers or blank cells.

Solution 1Recommended

Use an OR Formula in Conditional Formatting

Create a custom formatting rule using the OR function to check for both the upper and lower thresholds simultaneously.

By using a custom formula, you can apply a single formatting rule that triggers if either condition is met. This keeps your conditional formatting rules clean and easy to manage.

Select the target cells

Highlight the range of cells you want to format. In this case, select cells D2 through D10.

Open Conditional Formatting

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

Enter the custom formula

Choose 'Use a formula to determine which cells to format'. In the formula box, type: =OR(D2>11, D2<7). Make sure the cell reference (D2) matches the top-left cell of your selected range and does not contain absolute reference dollar signs ($) for the rows.

Apply a color format

Click the 'Format' button, go to the 'Fill' tab, and choose your preferred highlight color. Click 'OK' on the Format Cells dialog, and then 'OK' again on the New Formatting Rule dialog to apply.

Use an OR Formula in Conditional Formatting

Formula Accuracy: Using relative references like D2 allows Excel to automatically adjust the formula for D3, D4, and the rest of your selected range.

Solution 2

Use Built-in Highlight Cells Rules

Apply two separate built-in rules to highlight the cells without manually typing formulas.

Efficient Data Analysis

Easily Highlight Data Thresholds in WPS Spreadsheet

WPS Office provides a highly intuitive Conditional Formatting tool that functions exactly like Excel. It allows you to quickly highlight outliers, track metrics, and format data seamlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook. Highlight the cells you wish to evaluate (e.g., D2:D10).
2. Access Conditional Formatting: Click the 'Home' tab and select 'Conditional Formatting' from the toolbar.
3. Create a new formula rule: Click 'New Rule', select 'Use a formula to determine which cells to format', and input =OR(D2>11, D2<7).
4. Format and apply: Click 'Format' to pick a background color, then click 'OK' to instantly highlight your threshold values.

100% compatible with Microsoft Excel conditional formatting and formulasUser-friendly interface for managing complex formatting rulesFree and lightweight alternative for everyday spreadsheet tasksSupports advanced data visualization and color scales

microsoft office alternative - wps office

Frequently Asked Questions

How can I change the color of the entire row based on a cell's value?

To highlight the entire row, select your entire dataset instead of just one column before clicking 'New Rule'. In your conditional formatting formula, use an absolute column reference by adding a dollar sign before the column letter, such as =OR($D2>11, $D2<7).

Can I use different colors for values above and below the threshold?

Yes. Instead of using a single OR formula, create two separate rules. Create one rule with the formula =D2>11 and set the format to one color (e.g., green). Then create a second rule with the formula =D2<7 and set it to a different color (e.g., red).

Why isn't my conditional formatting formula working correctly?

Make sure your formula references the top-left cell of the exact range you selected. If your selection starts at D2, the formula must reference D2. Also, ensure there are no absolute row references (like $D$2) preventing the rule from evaluating each row dynamically. Finally, check that your data is stored as numbers and not text.

How do I remove conditional formatting rules?

Select the cells you want to clear, go to the 'Home' tab, click 'Conditional Formatting', hover over 'Clear Rules', and choose either 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet'.

Document & File Issues