ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Highlighting Cells Containing Specific Text

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.

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.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


Comments for this tip:

Hira    18 Sep 2015, 02:24
Thank You!!!
Bintang    07 Sep 2015, 05:00
Hi Dave,

is it possible to make formula that search text only at the end of the sentence?
ex: Im gonna search Y in
and then im need to highlight those cell with Y
thanks in advance
Dave S    09 Mar 2015, 06:04
Yes, in macros you can reference cells in different workbooks, you just need to include the workbook and worksheet names in the cell reference. You can check the syntax using Record Macro - the reference to a cell in another workbook will be something like (if the workbooks are in the same folder):


If the workbooks are in different locations, the workbook reference will include the file path.
Leigh-Ann    09 Mar 2015, 01:46
Great. thanks.
Sivahari    07 Mar 2015, 06:08
Dear Sir;

Is there any possibility to highlight specific text in Excel worksheet without highlighting full cell and without using macros?

We need to highlight specific text only. not to cell.

Please provide your response.

Best regards
rk    07 Mar 2015, 05:02
i want to use cells of two or more different sheets of different excel files in a macro. is itposbbile? then how?
Amjad Abuaysheh    07 Mar 2015, 04:58
Awesome. Thanks for your great tips :)

Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 4+5 (To prevent automated submissions and spam.)
          Commenting Terms

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2015 Sharon Parq Associates, Inc.