Formatting Issues

How to Apply Excel Conditional Formatting Based on Text in Another Column

Maira Mehtab Sep 22, 2026 869 views

Question details

Highlight cells in a specific column when their text matches or appears anywhere in another reference column, regardless of the row position.

Product: Excel
Device & OS: not provided
Scenario: Comparing two lists or columns to identify and highlight matching text entries across different rows automatically.
Observed behavior: The target cells need to change their fill color automatically based on a successful text match in the reference column.

Before you start

Ensure your data ranges are clearly defined and note the starting cell of your target column, as the conditional formatting formula relies on accurate relative and absolute cell references.

Solution 1Recommended

Use a Custom Formula for Conditional Formatting

Create a new formatting rule using the SEARCH and ISNUMBER functions to scan the reference column for matching text.

By combining the OR, ISNUMBER, and SEARCH functions, you can instruct the spreadsheet to look through an entire reference column for the text present in your current cell. If a match is found anywhere in that array, the formula returns TRUE and triggers the highlight.

Select the Target Range

Highlight the cells you want to format, such as G2:G100. Ensure the first cell (e.g., G2) is the active cell during this selection.

Open Conditional Formatting

Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', select 'New Rule', and then choose 'Use a formula to determine which cells to format'.

Enter the Custom Formula

In the formula input box, type =OR(ISNUMBER(SEARCH($K$2:$K$100,G2))). Adjust the $K$2:$K$100 range to match your actual reference column, and change G2 to your active target cell.

Set the Highlight Color

Click the 'Format' button, navigate to the 'Fill' tab, and select a highlight color such as green. Click 'OK' to save and apply the formatting rule.

Absolute vs. Relative References: It is crucial to use absolute references (with dollar signs, like $K$2:$K$100) for the search range so it remains fixed, while leaving the target cell (G2) as a relative reference so it updates for each row.

Efficient Spreadsheet Management

Use WPS Spreadsheet for Advanced Conditional Formatting

WPS Office provides a highly compatible spreadsheet tool that seamlessly handles advanced conditional formatting, including complex array formulas like SEARCH and ISNUMBER, exactly like Microsoft Excel.

1. Open your File in WPS Spreadsheet: Launch WPS Office and open your dataset containing the columns you want to compare.
2. Access Conditional Formatting: Highlight your target column, navigate to the Home tab, and click 'Conditional Formatting' > 'New Rule'.
3. Apply the Formula: Select 'Use a formula to determine which cells to format', input your =OR(ISNUMBER(SEARCH(...))) formula, pick a highlight color, and click OK.

Fully compatible with Microsoft Excel (.xlsx) file formats.Supports advanced conditional formatting and custom logic rules.Free and lightweight alternative for powerful data analysis.Familiar user interface with zero learning curve.

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong cells?

This usually happens if the active cell when creating the rule doesn't match the relative reference in your formula. If you selected G2:G100, ensure your formula references G2 and that G2 was the primary active cell during selection.

Can I use this method to find exact matches only instead of partial text?

Yes. If you want an exact match instead of a partial text search, replace the SEARCH function with the MATCH function. You would use a formula like =ISNUMBER(MATCH(G2,$K$2:$K$100,0)).

Does this conditional formatting formula work if the reference column is on a different worksheet?

Yes, you can reference another sheet in your conditional formatting formula. Simply include the sheet name in the reference range, for example: =OR(ISNUMBER(SEARCH(Sheet2!$K$2:$K$100,G2))).

Document & File Issues