How can I apply conditional formatting in Excel to highlight cells that contain partial text?

By /

Conditional formatting is a feature in Microsoft Excel that allows users to automatically format cells based on certain criteria. This can be useful when dealing with large datasets, as it highlights specific cells that meet a certain condition, making it easier to identify important information. In order to highlight cells that contain partial text, the user can apply conditional formatting by selecting the desired cells, choosing the “Conditional Formatting” option from the “Home” tab, and creating a new rule that checks for specific text within the cells. This rule can be customized to highlight cells with partial text in a specific color, making it easier to spot relevant information in a spreadsheet. Overall, applying conditional formatting in Excel is a simple and effective way to highlight cells that contain partial text, saving time and improving the visual organization of data.

Excel: Apply Conditional Formatting if Cell Contains Partial Text

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

Fortunately this is easy to do by using the New Rule option within the Conditional Formatting feature in Excel:

The following example shows exactly how to do so.

Example: Apply Conditional Formatting if Cell Contains Partial Text in Excel

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

Suppose we would like to highlight each team that contains “avs” somewhere in the name.

To do so, highlight the values in the range A2:A13, 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("avs", A2)

Then click the Format button and choose a color to use:

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

Note that the SEARCH function is case-insensitive.

If you would like to perform a case-sensitive search instead, you should use FIND(“avs”, A2) instead since the FIND function is case-sensitive.

Related:

Additional Resources

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

x

Related terms:

  1. Excel: Apply Conditional Formatting if Cell Contains Partial Text
  2. How to Apply Conditional Formatting to Blank Cells in Excel?
  3. How can I use conditional formatting in Google Sheets to highlight cells that are greater than or equal to a certain value?
  4. What is Excel: Apply Conditional Formatting if Cell Contains Text
  5. How do I apply conditional formatting to cells using VBA?
  6. How to apply conditional formatting if two cells are not equal
  7. How do I apply conditional formatting to a text column in Power BI?
  8. How can I apply conditional formatting to a text column in Power BI?
  9. Excel: Apply Conditional Formatting to Second Highest Value
  10. How to Apply Conditional Formatting to Dates within 6 Months in Excel