Excel: How to Apply Conditional Formatting if Cell Contains Text


Often you may want to apply conditional formatting to cells that contain specific text in Excel.

The following example shows exactly how to do so.

Example: Conditional Formatting if Cell Contains Text

Suppose we have the following dataset that shows the names of various basketball teams:

Suppose we would like to highlight each team name equal to “Mavs” in column A.

To do so, highlight the values in the range A2:A14, then click the Conditional Formatting icon on the Home tab, then click New Rule:

In the new window that appears, click Use a formula to determine which cells to format, then type in the following formula into the box:

=SEARCH("Mavs", A2)

Then click the Format button and choose a color to use to fill in the cells that contain “Mavs” in the name. Then click OK:

Excel conditional formatting for cells that contain text

Each team that contains “Mavs” in the name will automatically be highlighted:

Note that you can also highlight cells that contain partial text by using the asterisk (*) wildcard with the SEARCH function.

For example, we could use the following formula to highlight all team names that contain the partial text “avs” in the name:

=SEARCH("*avs*", A2)

Once we click OK, each team that contains “avs” in the name will be highlighted:

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Find the Top 10% of Values in an Excel Column
How to Find the Top 10 Values in a List in Excel
How to Find Unique Values from Multiple Columns in Excel

Leave a Reply

Your email address will not be published.