Yes. In Excel for Microsoft 365 on Windows, a formula-based conditional formatting rule can highlight cells that contain specified text. For example, with descriptions in A2:A100 and that same range selected, use =ISNUMBER(SEARCH("urgent",A2)) in a formula rule to highlight cells containing “urgent,” whether or not the capitalization matches.
What “contains” means in conditional formatting
A contains-text rule looks for a word or character sequence anywhere inside a cell’s value. If a cell says “Urgent: call the supplier,” a search for “urgent” finds it; the search does not require the cell to equal that word. This is useful for notes, product descriptions, email addresses, status labels, and other text that varies in length.
Conditional formatting changes how a cell looks, not the value stored in it. It can apply a fill color, font color, border, or another available format to cells that meet a condition. The formula is evaluated as TRUE or FALSE for each relevant cell. When it returns TRUE, Excel applies the chosen format; when it returns FALSE, the rule does not apply.
Excel also has a built-in Text that Contains rule for straightforward searches. A formula rule is more adaptable: it can refer to a search term in another cell, distinguish uppercase and lowercase, or use a match in one column to format an entire row.
Create a formula-based contains rule
For a basic rule, select the cells to format before creating the rule. The reference in the formula should correspond to the first cell in that selected range. If the descriptions are in A2:A100, select A2:A100 and enter this formula:
With descriptions in A2:A100 and A2:A100 selected, use =ISNUMBER(SEARCH("urgent",A2)).
- On the Home tab, choose Conditional Formatting > New Rule.
- In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
- Enter the formula, then choose Format to select a fill, font, border, or other formatting.
- Choose OK to close the formatting dialog, then choose OK again to create the rule.
Excel will normally show the selected cells in the rule’s Applies to range. Check that it covers the intended cells, particularly if the selection was irregular or the workbook already had conditional formatting. You can inspect or edit rules through Home > Conditional Formatting > Manage Rules. In Excel for Mac, the commands are also on the Home tab under Conditional Formatting, though dialog layouts can differ. Excel for the web has conditional formatting controls as well, but some rule-management options and dialogs differ from the desktop app.
How SEARCH and ISNUMBER work
SEARCH looks for one text string inside another. If it finds the string, it returns the position where the match begins; if it cannot find it, it returns an error. Wrapping SEARCH in ISNUMBER turns those outcomes into a simple test: a found position is a number and produces TRUE, while a not-found error produces FALSE. That TRUE-or-FALSE result is what the conditional formatting rule needs.
SEARCH is not case-sensitive. A search for “urgent” matches “URGENT,” “Urgent,” and “urgent.” It searches for a partial match, so “urgent” also matches “not urgent” and “urgently.” If that is not the intended meaning, use a more specific search term or a different rule. SEARCH also understands wildcard characters: an asterisk represents any number of characters, and a question mark represents one character. To search for an actual asterisk, question mark, or tilde, prefix it with a tilde in the search text.
The example uses a fixed word, but a cell reference makes the rule adjustable without editing the formula. With the search term in F1, descriptions in A2:A100, and A2:A100 selected, use =ISNUMBER(SEARCH($F$1,A2)). Changing F1 changes what the rule looks for. If F1 might be blank, guard against an empty search matching every cell: with the same layout, use =AND($F$1<>"",ISNUMBER(SEARCH($F$1,A2))).
Format an entire row based on one cell
You can use a match in one column to highlight other cells on the same row. For example, imagine task descriptions are in column C and the table occupies A2:F100. Select A2:F100, then create a formula rule that checks column C. With task descriptions in C2:C100 and A2:F100 selected, use =ISNUMBER(SEARCH("late",$C2)).
The dollar sign before C locks the column reference, so every cell in a row tests column C. The row number is not locked: it changes as Excel evaluates successive rows, checking C2 for the first row, C3 for the next, and so on. Selecting the full table range and using a suitable fill makes the whole row appear highlighted when the description contains “late.” This technique is often useful for status columns, overdue notes, or flags embedded in comments.
For a condition based on a search box instead, with the search term in F1, descriptions in C2:C100, and A2:F100 selected, use =AND($F$1<>"",ISNUMBER(SEARCH($F$1,$C2))). The absolute reference to F1 keeps the search term fixed while the row reference in C2 adjusts for each row. A common mistake is to lock the row as well, such as using $C$2; that would make every row check the same cell.
Choose between SEARCH, FIND, and the built-in rule
The best approach depends on how precise or flexible the match needs to be:
- Text that Contains: Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains for a quick, fixed text search. It is convenient when you do not need a formula or a search term controlled by another cell.
- SEARCH: Use a formula with SEARCH for a case-insensitive partial match, especially when the match text comes from a cell or the format should apply to a related range.
- FIND: Use FIND instead of SEARCH when capitalization must match. With descriptions in A2:A100 and A2:A100 selected, a case-sensitive rule for “Urgent” is
=ISNUMBER(FIND("Urgent",A2)). This finds “Urgent” but not “urgent.”
Both SEARCH and FIND return a position for a match and an error when there is no match, so ISNUMBER provides the same TRUE-or-FALSE conversion for either function. The built-in rule is not inferior; it is simply less suited to conditions that depend on other cells or need more control over how the range is evaluated.
References, ranges, and rule management
Relative and absolute references determine which cells Excel checks. In a formula rule, Excel evaluates the formula relative to the top-left cell of the Applies to range. If A2 is the first cell in the range, a reference to A2 can shift to A3, A4, and so on as Excel evaluates the rest. That is why the example for A2:A100 refers to A2 rather than A1.
Use dollar signs deliberately. A reference like $A2 keeps the column fixed while allowing the row to change; A$2 keeps the row fixed while allowing the column to change; and $A$2 keeps both fixed. For a row-wide rule based on column C, $C2 is generally the important pattern: the tested column stays C, but the tested row follows the formatting range.
If multiple rules format the same cells, their order and settings can affect what you see. Open Home > Conditional Formatting > Manage Rules, choose the relevant worksheet in the rules list if needed, and review the Applies to ranges and rule order. Excel provides a Stop If True option for applicable rules; when enabled, it can prevent lower-priority rules from being evaluated for cells where that rule is true. Keep rules focused and remove obsolete ones if the displayed result is confusing.
Common problems and how to fix them
- The wrong cells are highlighted: Check both the Applies to range and the formula’s starting reference. If the range begins at B2 but the formula is written as if A2 were the first cell, the rule may evaluate against the wrong cells.
- Every row reacts to one cell: Check whether the formula accidentally uses a fully absolute reference such as $C$2. For a row-by-row test, the row number usually needs to remain relative.
- No cells are highlighted: Confirm the spelling of the search term and check whether SEARCH or FIND is appropriate. Also verify that the rule is attached to the intended range and that the selected format is visible against the worksheet background.
- Every cell is highlighted when the search box is empty: An empty string can be found at the start of text. Add a nonblank check for the search cell, as in the AND example above.
- A formula is rejected or behaves unexpectedly: In the formula-rule box, begin with an equals sign and use a formula that returns TRUE or FALSE for the first cell in the applied range. If you copied a formula from a localized Excel installation or another spreadsheet program, check the function names and separators used by your Excel setup.
Conditional formatting updates as cell values or referenced search terms change. However, it does not rewrite the text or permanently mark a match. If a workbook is printed, viewed in another spreadsheet program, or opened with formatting rules altered or unsupported, its displayed appearance may differ; the underlying cell values remain the data to rely on.
Frequently Asked Questions
Can Excel conditional formatting find text anywhere in a cell?
Yes. A formula using ISNUMBER with SEARCH tests for a substring anywhere in the cell. A match does not have to equal the cell’s entire contents.
Is Excel’s Text that Contains rule case-sensitive?
The built-in Text that Contains rule is generally used as a case-insensitive text match. For a case-sensitive condition, create a formula rule using FIND rather than SEARCH.
Can I highlight a row when one cell contains a word?
Yes. Select the full table range and write a formula that refers to the word-check column, locking the column with a dollar sign while leaving the row relative. Make sure the formula references the first row of the selected range.
Can the word to search for come from another cell?
Yes. Refer to the cell containing the search term in the formula. Use an absolute reference for that search cell if the conditional formatting range covers multiple rows or columns, and add a blank check if an empty search should not match everything.
Does conditional formatting change the cell’s text?
No. It changes the cell’s appearance while the rule is true. To change, extract, or remove text, use a separate editing method or a worksheet formula; conditional formatting itself does not alter the stored value.
Related
- Cash Flow Tracker Excel Template: Build and Use It
- Cash Flow Statement Template Excel: Build and Use
- Cash Flow Projection Template Excel: Free Monthly Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Monthly Timesheet TemplateYes—you can make a monthly timesheet template in Google Sheets by setting up columns for date, employee, start…
- How to Remove Double Spaces in Excel Without Losing DataTo remove repeated spaces in Excel, select the cells and use Home > Find & Select > Replace…
- How to Flip the Order of Cells in Excel Without Re-typingTo flip cells in Excel, add a helper column containing 1, 2, 3, and so on, select the…
- How to Merge Cells in Excel Without Losing Your DataTo merge cells in Excel without losing their contents, first combine the values you need into the top-left…
- How to Add Animated Text to PowerPoint PresentationsTo add animated text in PowerPoint, select a text box or highlight text, open Animations > Add Animation,…
- PowerPoint Presenter View: Speak While Seeing Your NotesTo present in PowerPoint while keeping your notes in view, turn on Presenter View and show the slide…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.