Excel conditional formatting for multiple words

Question

I am trying to highlight or color words using conditional formatting in Excel.

For example, in a single column in may have over 100 rows ranging in words such as apple, berry, water, blue, red and shoe.

I am trying to highlight specific words in the cell within the column. Using that example I would like to highlight apple, berry and red.

Rather than doing multiple conditional formats, I am trying to do this in a single session. How would I go about doing this?

Thank you.

SO is for showing examples of programming code and asking for help with bugs. I don't know excel conditional formatting very well, but please add your example of how you would do that so far. — Tim
Conditional formatting in Excel does not highlight individual words. It's the whole cell or nothing. Also, read the help to learn how to ask a question here. — teylyn

Extragorey Extragorey · Accepted Answer · 2016-12-05T03:30:49

As teylyn pointed out, you can't highlight individual words within cells using conditional formatting. However, you can highlight the whole cell containing those words.

You'd use the custom formula option for the conditional formatting, and then choose the formatting you want to apply to those cells.

An example conditional formatting function to do this would be:

=OR(ISNUMBER(FIND("apple", A1)), ISNUMBER(FIND("berry", A1)), ISNUMBER(FIND("red", A1)))

where A1 refers to the active cell where you're entering the formula. This would highlight all cells containing any of the words apple, berry, or red.

Excel conditional formatting for multiple words

1 Answers