Formatting Issues

How to Use Excel Conditional Formatting for Values Within a Range

Rana Garcia Oct 1, 2026 869 views

Question details

The user needs to highlight cells dynamically based on whether their values fall between a defined lower and upper limit, or outside of that range.

How to Use Excel Conditional Formatting for Values Within a Range

Product: Excel
Device & OS: not provided
Scenario: Setting up data validation visual cues by coloring values green if they meet lower and upper bound criteria, and red if they exceed or fall below them.
Observed behavior: Requires two distinct conditional formatting rules utilizing custom formulas to evaluate multiple conditions simultaneously.

Before you start

Ensure your dataset is organized and identify the specific columns containing your target values, lower limits, and upper limits before creating the formatting rules.

Solution 1Recommended

Use AND/OR Formulas for Conditional Formatting

Create custom rules using the AND function to highlight values within the range, and the OR function for values outside the range.

Using custom formulas in conditional formatting gives you the flexibility to reference different columns for upper and lower limits on a row-by-row basis. This dynamic approach is much more powerful than the standard built-in rules.

Select the target range

Highlight the cells you want to apply the formatting to (for example, column AU).

Create a New Rule

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

Format values within the range (Green)

Choose 'Use a formula to determine which cells to format'. Enter `=AND(AU2>=BF2,AU2<=BG2)` in the formula box, click the Format button, select a green fill color, and click OK.

Format values outside the range (Red)

Go back to Conditional Formatting > New Rule > Use a formula. Enter `=OR(AU2<BF2,AU2>BG2)`, click Format, select a red fill color, and apply.

Use AND/OR Formulas for Conditional Formatting

Adjusting Cell References: Make sure the row numbers in your formulas (e.g., AU2, BF2, BG2) exactly match the first row of your selected cell range. If your selection starts at row 5, the formula must use row 5 references to align correctly.

Advanced Formatting in WPS

Highlight Data Ranges Easily with WPS Spreadsheet

WPS Spreadsheet provides a robust and user-friendly interface for applying complex conditional formatting rules, allowing you to highlight data dynamically using custom formulas.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your dataset.
2. Access Conditional Formatting: Select the cells you want to format, go to the Home tab, and click on Conditional Formatting > New Rule.
3. Apply custom formulas: Select 'Use a formula' and enter your logical functions (like AND or OR) to define your upper and lower limits.
4. Set formatting style: Click the Format button to define your desired background colors (like red or green), then click OK to save.

Fully compatible with Microsoft Excel conditional formatting and complex AND/OR formulas.Intuitive rule manager for easily editing and reordering multiple formatting rules.Free, lightweight alternative that processes large datasets with high performance.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applied to the wrong rows?

This usually happens if the row references in your formula do not match the first row of the range you selected. Ensure relative references (like AU2) align exactly with the start of your highlighted selection.

Can I use fixed absolute references for the upper and lower limits?

Yes. If your upper and lower limits are fixed in specific cells (e.g., $B$1 and $B$2) rather than changing row-by-row, use absolute references with dollar signs in your formula (like `$B$1`) so every cell evaluates against those exact values.

Is there a built-in 'Between' rule that doesn't require formulas?

Yes. You can go to Conditional Formatting > Highlight Cells Rules > Between. However, this native option only applies one formatting style at a time and is best suited for static numbers or single-cell references, making the custom formula method much better for complex, dynamic multi-column limits.

Document & File Issues