Function Problems

How to Use Excel Conditional Formatting Based on Text in Another Cell

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to format a cell automatically based on whether a different specific cell contains the word "Length", and also wants an IF formula to output alternate text based on that same condition.

Product: Spreadsheet
Device & OS: not provided
Scenario: Applying dynamic cell highlighting based on another cell's contents and returning specific text based on conditional rules.
Observed behavior: The user is looking for the exact formula rules to enter into the conditional formatting dialog and the correct syntax for an IF formula to output "no" or "Select".

Before you start

Ensure that the target cell references in your conditional formatting formulas use the correct relative or absolute locking (such as using $C3 instead of $C$3) so the rule applies correctly across multiple rows.

Solution 1Recommended

Use a Formula Rule for Conditional Formatting

Use a combination of the ISNUMBER and SEARCH functions within the conditional formatting rules to check if another cell contains a specific substring.

By using the SEARCH function, Excel looks for your specific text string inside the target cell. Because SEARCH returns a number if the text is found, wrapping it in ISNUMBER converts the result into a TRUE or FALSE condition, which is exactly what conditional formatting needs to trigger.

Select the target range

Highlight the cell or range of cells that you want the formatting to be applied to.

Open Conditional Formatting rules

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

Choose the formula option

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

Enter the formatting formula

In the formula bar, enter =ISNUMBER(SEARCH("Length",$C3)) (assuming C3 is the target cell you are checking).

Apply formatting style

Click the 'Format' button, choose your desired highlighting style (like a fill color or font change), and click 'OK' twice to apply the rule.

Solution 2

Use an IF Function to Return Alternate Text

If you need to output specific text based on a condition rather than changing cell colors, use a standard IF function directly in the cell.

Advanced Spreadsheet Formatting

Easily Apply Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting rules, including dynamic formulas like ISNUMBER, SEARCH, and IF. You can manage complex data styling seamlessly in a familiar, intuitive interface.

1. Open your document: Launch WPS Spreadsheet and open the worksheet containing your data.
2. Select the data range: Highlight the cells or rows you wish to format dynamically.
3. Create a new rule: Go to Home > Conditional Formatting > New Rule, and choose 'Use a formula to determine which cells to format'.
4. Apply formula and style: Enter your ISNUMBER and SEARCH formula, click 'Format' to choose your styling, and click 'OK'.

Fully compatible with Microsoft Excel conditional formatting rules and functions.Intuitive 'New Rule' dialog makes creating complex data styling easy.Lightweight software that processes large data sets efficiently without lagging.Completely free to use for daily spreadsheet tasks and data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my conditional formatting formula working on the whole column?

This usually happens due to incorrect cell referencing. Ensure you are using mixed references (like $C3) instead of absolute references ($C$3). The dollar sign locks the column, but leaving the row number relative allows the formatting to adapt correctly as it moves down the column.

Can I use conditional formatting to highlight an entire row?

Yes. To highlight an entire row based on a single cell's value, select the entire data range (e.g., A2:F100) and use a formula rule with an absolute column reference, like =$C2="YourText". This ensures every cell in that row checks column C before applying the format.

Is the SEARCH function case-sensitive?

No, the SEARCH function is not case-sensitive. If you need a strictly case-sensitive check (where 'Length' is treated differently from 'length'), use the FIND function instead in your formula: =ISNUMBER(FIND("Length", $C3)).

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 view all applied rules for your selection or the entire worksheet, edit the formulas, or delete rules that are no longer needed.

Excel & Data Problems