Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Highlighting Cells Containing Specific Text.

Highlighting Cells Containing Specific Text

by Allen Wyatt
(last updated March 7, 2015)

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:

  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 New Rule. Excel displays the New Formatting Rule dialog box.
  4. In the Select a Rule Type area at the top of the dialog box, choose Format Only Cells that Contain.
  5. Using the left-most drop-down list in the criteria area, select Specific Text. (See Figure 1.)
  6. Figure 1. The New Formatting Rule dialog box.

  7. Make sure the center drop-down list is Containing.
  8. In the right-most box enter "shawn" (without the quote marks).
  9. Click Format to display the Format Cells dialog box. (See Figure 2.)
  10. Figure 2. The Format Cells dialog box.

  11. 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.
  12. Click OK to dismiss the Format Cells dialog box. The formatting you specified in step 9 should now appear in the preview area for the rule.
  13. Click OK.

There is a gotcha to be aware of when looking for specific text: The condtional formatting rule will consider the cell a match if it contains the text you specify in step 7 anywhere within the cell. So, for instance, if you use the text "shawn" in step 7, and the cell contains "My name is Shawn," then for the purpose of this type of format, this is a match.

The reason this works this way is because you specified "Containing" in step 6. If you want some other type of match, then you'll need to pick some different setting in step 6.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6235) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Highlighting Cells Containing Specific Text.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Calculating a Geometric Standard Deviation

One of the areas in which Excel provides worksheet functions is in the arena of statistical analysis. You may want to ...

Discover More

Quickly Changing Document Windows

When working with multiple documents at the same time, you often have a need to move from one document to another. Here's ...

Discover More

Adding Text to an AutoShape

You can add text to all sorts of drawing shapes, not just text boxes. Here's how easy it is.

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (RIBBON)

Highlighting Values that are 10x a Baseline Value

Sometimes the hardest part of getting your conditional formatting rules to work properly is figuring out the proper way to ...

Discover More

Conditionally Formatting Cells Containing Dates

Need to know if a particular cell contains a date value? Excel doesn't have a worksheet function to determine this ...

Discover More

Diagonal Borders in a Conditional Format

Conditional formatting is a great tool for changing how your data looks based on the data itself. Excel won't allow you to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share