Excel: Conditional Formatting Based on Multiple Text Values


To apply conditional formatting to cells in Excel that contain one of several specific text, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.

The following example shows how to use this option in practice.

Example: Apply Conditional Formatting Based on Multiple Text Values

Suppose we have the following dataset in Excel that shows the position and points scored by various basketball players:

Now suppose we would like to apply conditional formatting to each cell in the Position column that contains one of the following specific texts:

  • Starting
  • Forward
  • Center

To do so, we can create a new column that contains these text values.

We can then highlight these values in the range D2:D4 and type specific_text in the Name box in the top left corner:

Next, we can highlight the cells in the range A2:A11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:

In the new window that appears, click Use a formula to determine which cells to format, then type =SUM(COUNTIF(A2,”*”&specific_text&”*”)) in the box, then click the Format button and choose a fill color to use.

Excel conditional formatting based on multiple text values

Once we press OK, all of the cells in the range A2:A11 that include either Starting, Forward, or Center somewhere in the position name will be highlighted:

If we change the text values in column D, the formula will automatically update to highlight the cells that contain the new specific text values.

For example, suppose we instead look for the text values Point, Shooting, and Power:

All of the cells in the range A2:A11 that include either Point, Shooting, or Power somewhere in the position name are now highlighted.

Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.

Additional Resources

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

Excel: Apply Conditional Formatting to Overdue Dates
Excel: Apply Conditional Formatting if Cell is Between Two Dates
Excel: Apply Conditional Formatting Based on Multiple Conditions

2 Replies to “Excel: Conditional Formatting Based on Multiple Text Values”

    1. Hi Chris…Please elaborate on what you are experiencing so that we may better assist you.

Leave a Reply

Your email address will not be published. Required fields are marked *