To conditionally format text in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter the text to find, choose a format, and select OK. For more specific tests—such as exact matches, case-sensitive text, or formatting a whole row—use a formula-based conditional formatting rule.
What conditional formatting does to text
Conditional formatting changes how a cell looks when its contents meet a condition. A rule can change the font color, fill color, border, or other available formatting without changing the underlying text. For example, a task cell containing “Blocked” can turn red while still storing the word Blocked. If its text changes to “Done,” the rule can stop applying and another rule can take over.
This is useful for scanning lists, status reports, inventory, and customer feedback. It can draw attention to keywords such as “urgent,” identify approved or rejected entries, or flag cells that contain a particular phrase. The formatting updates when the cell value changes, so it is more practical than manually coloring cells that may need updating later.
Conditional formatting is different from Find and Replace, which searches for or changes cell contents, and from ordinary font formatting, which stays in place until someone changes it. A rule is a display instruction: it does not rewrite a value, add a note, or alter the result of a calculation.
Apply a built-in text rule
For a straightforward keyword search, use Excel’s built-in rule rather than writing a formula:
- Select the cells you want the rule to check, such as A2:A100. Selecting the range first helps Excel set the rule’s coverage correctly.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
- Enter the word or phrase. Choose a preset appearance, or select Custom Format to set options such as font, fill, and border.
- Select OK. Excel applies the chosen appearance to cells in the selected range that meet the text condition.
The dialog is designed for a quick text test, not for every possible matching rule. Depending on the text rule options shown, you can test whether text contains, does not contain, begins with, or ends with the entered characters. To check whether a cell equals a whole value, compare values across columns, or make a case-sensitive test, a formula rule gives you more control.
Choose the right matching method
Consider what “matches” should mean before setting up a rule. A contains test can find a word embedded in a longer entry: searching for “late” can match “Late shipment.” A begins-with test is useful for consistent prefixes, such as ticket IDs that start with “ERR.” An ends-with test can help identify entries with a shared suffix. These tests can also match text within longer strings, so think about whether that could create unwanted highlights.
- Contains: Use for a keyword or phrase anywhere in the cell.
- Begins with or ends with: Use when the position of the text matters.
- Exact match: Use a formula when the entire cell must equal a particular value.
- Case-sensitive match: Use a formula with FIND when uppercase and lowercase letters must be treated differently.
Excel’s SEARCH function, often used in a formula rule, is not case-sensitive. FIND is case-sensitive. This distinction matters for labels such as “OK” and “ok” if they represent different statuses. For ordinary labels where capitalization is inconsistent, a case-insensitive check is usually more forgiving.
Use a formula for exact or customized text rules
Formula rules are useful when a built-in rule cannot express the condition. To create one, select the target range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, choose Format, and confirm the rule. In the formula, refer to the top-left cell of the selected range; Excel adjusts relative references for the other cells.
With the status labels in A2:A100 and A2 as the top-left cell of the selected range, this formula tests for an exact match to Paid: =A2="Paid". It highlights a cell containing only Paid, not a longer entry such as Paid in full. The example assumes the text uses the same spelling and spacing; ordinary equality comparisons are not case-sensitive.
With comments in A2:A100 and A2 at the top left of the selected range, this formula checks whether the cell contains the word late, regardless of its position: =ISNUMBER(SEARCH("late",A2)). SEARCH returns the position where it finds the text, or an error if it cannot find it. ISNUMBER turns that result into TRUE when the text is present and FALSE when it is not. Because SEARCH is case-insensitive, this catches “late,” “Late,” and “LATE.”
With priority labels in A2:A100 and A2 at the top left of the selected range, this formula checks for uppercase URGENT using a case-sensitive test: =ISNUMBER(FIND("URGENT",A2)). FIND returns a position only when the capitalization matches. If the cell says Urgent instead, the formula does not meet the condition.
To format a whole row based on a text value in one column, select the rows across the desired columns and lock the test column with dollar signs. For example, with statuses in column C and data in A2:F100, select A2:F100 and create a formula rule using =$C2="Overdue". The dollar sign keeps the test in column C as Excel checks each row, while the row number adjusts. The rule formats the full selected row area when that row’s status is Overdue.
Build useful examples without changing cell values
A simple project tracker might have task names in column A and status labels in column B. A built-in Text that Contains rule on B2:B50 can highlight “Blocked” in red. A separate rule can highlight “Done” in green. The status cells remain text; color is only a visual cue. If users type several variations—such as “Complete,” “Completed,” and “Done”—decide whether to create separate rules or standardize the allowed labels with a drop-down list.
For a feedback log, a contains rule on the comment column can flag “refund” or “damaged” for review. A keyword rule can match incidental mentions, however. A comment such as “The packaging was not damaged” still contains damaged. Conditional formatting checks the text pattern; it does not understand context, negation, or sentiment. Review highlighted records rather than treating a keyword match as a complete conclusion.
For a row-based report, a formula rule can color the full record based on one status cell. This makes it easier to scan details across several columns, but keep the selected range and formula references aligned. If the range begins at row 2, the formula should normally refer to row 2. A mismatched starting row is a common reason the wrong records receive formatting.
Manage rule order and range coverage
When multiple rules apply to the same cells, their order can affect the final appearance. Open Home > Conditional Formatting > Manage Rules to review the rules, change their order, edit them, or adjust the range in Applies to. If two rules set the same property—for example, both set the font color—the higher-priority applicable rule can determine what you see. The Stop If True option can prevent lower-priority rules from being evaluated after a rule is true, where that option is available for the rule.
Check Applies to when a rule works in some rows but not others. A rule created while one cell was selected may cover only that cell; a rule created after selecting a range may cover the intended area. Also check that the active cell when you created a formula rule was the top-left cell of the selected range. Relative references are interpreted from that starting point.
If a workbook contains several rules, avoid adding overlapping rules without a reason. Similar rules can make future edits confusing, and a formatting result may be hard to explain if different rules set competing colors. Use Manage Rules to inspect the full list and remove obsolete rules when a report’s layout changes.
Common problems and how to fix them
- Nothing is highlighted: Check for leading or trailing spaces, spelling differences, and whether the rule’s Applies to range includes the cells. A formula rule may also point at the wrong column or row.
- More cells match than expected: A contains rule finds the text inside longer entries. Use an exact-match formula if the whole cell must equal the target.
- Capitalization seems ignored: The built-in text search and SEARCH are not case-sensitive. Use FIND in a formula rule for a case-sensitive check.
- Only part of a row is colored: Expand Applies to to cover the full row area and use an absolute column reference, such as $C2, to test the status column consistently.
- Formatting appears inconsistent: Review rule order and competing formats in Manage Rules. Also check whether the cells contain formulas whose displayed results differ from the text you expect.
Some cells that look alike may not contain identical text. A copied value can include an extra space, and a formula may return text that differs from a manually typed label. If necessary, inspect the cell contents in the formula bar and standardize the source values. Conditional formatting is only as dependable as the values and matching criteria it receives.
Excel on Mac and Excel for the web
The instructions above use Excel for Microsoft 365 on Windows. Excel for Mac also provides conditional formatting from the Home tab, including text-based rules and formula-based rules; dialog labels or window layout can vary slightly by release. Excel for the web supports conditional formatting too, though some rule-management options and dialogs may be presented differently from the desktop application. If a workbook has complex rules, check them in the version where you plan to maintain the workbook.
Conditional formatting is part of Excel, so you do not need to buy a separate add-in for these standard text rules. The main practical cost is maintenance: large, complicated rule sets can be harder to understand and may make a workbook less straightforward to update. Keeping the criteria and Applies to ranges clear helps make a shared workbook easier to maintain.
Frequently Asked Questions
Can Excel highlight a cell if it contains specific text?
Yes. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter the text, choose a format, and select OK.
Can conditional formatting match an entire cell only?
Yes. Create a formula rule that compares the cell with the required text, such as =A2="Paid" when A2 is the first cell in the selected range. A contains rule can also match longer text that includes the same word.
Is conditional formatting text matching case-sensitive?
The built-in text rule and SEARCH are case-insensitive. For a case-sensitive formula rule, use FIND inside ISNUMBER and base the reference on the top-left cell of the selected range.
Can I highlight an entire row when one cell contains text?
Yes. Select the full area to format, create a formula rule that refers to the status column, and lock that column with a dollar sign. For example, if statuses are in column C and the selected range begins on row 2, use =$C2="Overdue".
Does conditional formatting change the text in the cell?
No. It changes the cell’s appearance when a rule is true. To replace or edit the text itself, use a separate editing method such as typing a new value or using Find and Replace.
Related
- Can You Split One Cell in Excel? What Actually Works
- Turn Off Flash Fill in Excel (and Keep Manual Fill)
- Can You Password Protect One Sheet in Excel? Here’s How
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis Regression: Run and Interpret ItTo run a linear regression in Excel for Microsoft 365 on Windows, arrange the outcome values in one…
- How to Set Up Auto Forwarding in OutlookTo automatically forward incoming Outlook email, open Settings, go to Mail > Forwarding, enable forwarding, enter the destination…
- Find and Replace Highlight Color in Microsoft WordIn Word for Microsoft 365 on Windows, use Home > Find, then search for the Highlight format and…
- COUNTA in Excel: Count Nonblank Cells for Data AnalysisCOUNTA in Excel counts cells that are not empty, making it useful for checking how many entries a…
- How to Get Out of Track Changes Mode in Microsoft WordTo get out of Track Changes mode in Word, open the Review tab and turn off Track Changes.…
- Find and Replace in Selected Excel Cells: How It WorksYes. In Excel for Microsoft 365 on Windows, select the cells you want to change, press Ctrl+H, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.