Formula Errors

How to Automatically Highlight Excel Values Outside Brand Specifications

Aamir Naveed AkramAamir Naveed Akram Nov 11, 2026 868 views

Question details

The user needs to create conditional formatting rules in Excel that dynamically check test results against lower and upper specification limits that vary depending on the product brand and parameter.

How to Automatically Highlight Excel Values Outside Brand Specifications
Product
Excel
Device & OS
not provided
Scenario
Monitoring quality control data or test results where each brand has distinct acceptable minimum and maximum limits.
Observed behavior
Requires a solution to automatically look up the correct limits for a specific brand and apply a highlight if the actual value falls outside these dynamic bounds.
Before you start

Ensure your spreadsheet contains a well-structured reference table listing each brand, its specific testing parameters, and the respective minimum and maximum acceptable values.

Solution 1Recommended

Use Conditional Formatting with XLOOKUP or VLOOKUP

By nesting a lookup function within a conditional formatting formula, you can dynamically retrieve varying upper and lower limits for specific brands and highlight out-of-range cells instantly.

The most efficient method is to combine the OR function with XLOOKUP (or VLOOKUP/INDEX MATCH). The OR function checks two conditions: whether the test result is smaller than the brand's lower limit, or larger than the upper limit. If either condition is true, the format is applied.

1
Format your limits reference table

Make sure your specifications table includes columns for the Brand name, the Parameter, the Lower Limit, and the Upper Limit. For example, place this on a sheet named 'Limits'.

2
Select the test result data

Highlight the range of cells containing the actual test results that you want Excel to evaluate and potentially highlight.

3
Create a New Rule

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

4
Enter the custom formula

Choose 'Use a formula to determine which cells to format'. Enter a formula like =OR(B2<XLOOKUP(A2,Limits!A:A,Limits!C:C), B2>XLOOKUP(A2,Limits!A:A,Limits!D:D)). Replace A2 with your brand column, B2 with your result column, and adjust the Limits references.

5
Apply a highlight color

Click the 'Format' button, go to the 'Fill' tab, select a color (such as red) to highlight the out-of-specification values, and click 'OK'.

Use Conditional Formatting with XLOOKUP or VLOOKUP
Legacy Function Support: If you are using an older version of Excel that does not support the XLOOKUP function, you can replace it with VLOOKUP or an INDEX/MATCH combination to achieve the same result.
Efficient Data Formatting

Easily Highlight Out-of-Spec Values with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like XLOOKUP, VLOOKUP, and sophisticated conditional formatting rules. You can seamlessly check quality control data against dynamic variables just as you would in other major spreadsheet software.

  1. 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the test results and specification limit tables.
  2. 2. Access Conditional Formatting: Navigate to the Home tab on the ribbon and click on 'Conditional Formatting' > 'New Rule'.
  3. 3. Input your dynamic formula: Select 'Use a formula to determine which cells to format', input your OR and XLOOKUP formula, choose your desired highlight color in the Format menu, and click OK to apply.
100% compatible with Microsoft Excel formulas and conditional formatting rules.Supports modern lookup functions including XLOOKUP, VLOOKUP, and INDEX/MATCH.Handles large data sets and reference tables smoothly without lag.Lightweight, fast, and completely free to use for everyday office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting formula highlighting the wrong rows?

This commonly occurs when absolute references (like $B$2) are used instead of relative references (like B2) for the rows. To ensure the conditional formatting evaluates each row independently, remove the dollar sign before the row number in your custom formula.

Can I highlight the entire row instead of just the specific value cell?

Yes. To highlight the entire row, select your entire data table before setting up the conditional formatting rule. Then, lock the column references in your formula by adding a dollar sign (e.g., use $B2 instead of B2) so that every cell in the row evaluates the test result column.

Does XLOOKUP work across different worksheets?

Absolutely. You can place your dynamic limits reference table on a separate sheet (for example, named 'Limits') to keep your main dashboard clean. In your conditional formatting formula, simply refer to the separate sheet using an exclamation mark, such as Limits!A:A.