Formula Errors

How to Apply Excel Conditional Formatting Based on Another Column and Specific Text

Olivia Miller Oct 16, 2026 871 views

Question details

The user wants to format cells in a large Excel range to turn red if the cell contains the text 'X' and the corresponding row in column S has a value greater than zero.

How to Apply Excel Conditional Formatting Based on Another Column and Specific Text

Product: Excel
Device & OS: not provided
Scenario: Applying conditional formatting across a large data range based on multiple criteria involving both the current cell and a different column.
Observed behavior: Cells need to automatically change to a red background when both conditions are met.

Before you start

Ensure your dataset is organized in a clear tabular format without merged cells, and identify the exact range of cells you want to apply the formatting to.

Solution 1Recommended

Use a Custom Formula in Conditional Formatting

Applying a formula-based rule allows you to combine multiple conditions, such as checking for specific text and evaluating a value in another column.

By using the AND function, you can evaluate two conditions simultaneously. Absolute column referencing ($S) ensures that Excel always looks at column S, while relative row referencing allows the formula to adapt to each row in your selected range.

Select the target data range

Highlight the complete range you want to format on your spreadsheet, for example, A2:R100.

Create a new formatting rule

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

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Input the AND formula

Enter the formula =AND(A2="X",$S2>0) in the rule description text box. Ensure that the row number matches the first row of your selected range.

Set the cell format

Click the 'Format' button, navigate to the Fill tab, select the color red, and click OK twice to apply the rule.

Use a Custom Formula in Conditional Formatting

Absolute vs. Relative References: The dollar sign ($) before the 'S' locks the reference to column S. Without it, the formula would incorrectly shift columns as it evaluates across the range.

Format Data with WPS Spreadsheet

Easily Apply Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting rules, including custom formulas and functions. You can seamlessly apply multi-condition formatting to highlight important data without switching to other software.

1. Select Data Range: Open your document in WPS Spreadsheet and highlight the target range (e.g., A2:R100).
2. Access Conditional Formatting: Go to the Home tab, click on 'Conditional Formatting', and choose 'New Rule'.
3. Enter the Custom Formula: Select 'Use a formula to determine which cells to format' and input =AND(A2="X",$S2>0).
4. Apply Red Fill: Click 'Format', select a red background from the Patterns tab, and click OK to finalize.

100% compatible with Microsoft Excel conditional formatting rules and custom formulasIntuitive interface for managing large data ranges and complex datasetsFree to use with comprehensive formula and logical function supportLightweight application that runs smoothly on both Windows and macOS

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to the wrong rows?

This usually happens when the row reference in your formula doesn't match the top row of your selected range. Ensure that if your selected range starts at row 2 (e.g., A2:R100), your formula also references row 2 (e.g., A2 and $S2).

Can I add a third condition to this formatting rule?

Yes, the AND function can evaluate multiple conditions. You can simply add another logical test inside the parentheses separated by a comma, such as =AND(A2="X", $S2>0, $T2="Yes").

How do I remove or edit an existing conditional formatting rule?

Go to the Home tab, click on Conditional Formatting, and select Manage Rules. From there, you can choose 'This Worksheet' to view all rules, select the specific rule you wish to change, and click Edit Rule or Delete Rule.

Excel & Data Problems