Highlighting Cells Containing Specific Text

You can use the conditional formatting feature in Excel to help draw attention to cells that contain specific text in which you are interested. For instance, if you have a range of cells and you want to know which ones contain the letters "shawn," then you can do the following in versions of Excel prior to Excel 2007:

1. Select the range of cells.
2. Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box. (Click here to see a related figure.)
3. In the drop-down Condition list, choose "Formula Is".
4. In the formula box, enter the following formula. (Make sure you replace A1 with the cell address of the cell in the upper-left corner of the range selected in step 1.)

     =NOT(ISERR(SEARCH("Shaw",A1)))

5. Click on Format. Excel displays the Format Cells dialog box. (Click here to see a related figure.)
6. Using the controls in the dialog box, specify a format that you want used for those cells that contain the specified text. For instance, you may want bold text in a red typeface.
7. Click on OK to close the Format Cells dialog box.
8. Click on OK to close the Conditional Formatting dialog box.

If you are using Excel 2007 then you should follow these steps, instead:

1. Select the range of cells.
2. With the Home tab of the ribbon displayed, click the Conditional Formatting option in the Styles group. Excel displays a palette of options related to conditional formatting.
3. Choose Highlight Cells Rules and then choose More Rules from the resulting submenu. Excel displays the New Formatting Rule dialog box.
4. In the Select a Rule Type area at the top of the dialog box, choose Use a Formula to Determine Which Cells to Format. (Click here to see a related figure.)
5. In the Format Values Where This Formula Is True box, enter the following formula. (Make sure you replace A1 with the cell address of the cell in the upper-left corner of the range selected in step 1.)

=NOT(ISERR(SEARCH("Shaw",A1)))

6. Click Format to display the Format Cells dialog box.
7. Using the controls in the dialog box, specify a format that you want used for those cells that contain the specified text. For instance, you may want bold text in a red typeface.
8. Click OK to dismiss the Format Cells dialog box. The formatting you specified in step 7 should now appear in the preview area for the rule.
9. Click OK.

You can make this approach even more general-purpose in nature by specifying a cell that contains what you want to search for. For instance, if you type "Shaw" in cell F7, then you could replace the formula in step 4 or step 5 with the following:

=NOT(ISERR(SEARCH($F$7,A1)))

Now, you can search for something different just by changing the characters in cell F7.