Abstract
Transcript
Another useful conditional formatting option is the ability to highlight cells that contain certain text. If you regularly need to search a big list for different values, this conditional formatting option can be a great way to build a custom highlighting feature in your worksheet.
Let's take a look.
Here we have a list of names in a table. Let's use this table to explore how to use conditional formatting to highlight text values.
First let's select all values in the table. In the conditional formatting menu, in the Highlight Cells Rules category, you'll find an item called "Text that contains". When we choose this option, Excel will display a small window that allows you to enter the value for the text and set the formatting to apply.
The default value comes from the active cell in the selection, in this case Aaron.
Let's change the value to Ann, and then set a custom format consisting of a light yellow fill.
When we click OK, the format is applied and we see all the cells that contain "ann" highlighted in yellow.
Let's use Manage Rules to edit this rule, and see what sort of options we have.
Like many conditional formatting presets, this one creates a more general rule with a number of configurable options. We can change the rule to test for specific values, dates, blanks, and errors.
And for this specific rule, we have four options - containing, not containing, begins with, and ends with. Let's try beginning with option.
Now the conditional format only highlights cells that begin with Ann.
In a future lesson, we'll look at how to move the text your looking for out of the conditional rule and onto the worksheet. That way, you can change the text whenever you like without editing the rule.
