Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated July 22, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


31

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, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Extra Spaces after AutoText Substitutions

AutoText is a great tool for inserting standard information in your documents. It is also possible, however, to get ...

Discover More

Locking Icons on the Desktop

Your desktop is supposed to be place where you can put your most-often-used program and file icons. You can spend hours ...

Discover More

Word 2010 Graphics (Table of Contents)

One way to enhance your documents is with Word's powerful graphics capabilities. Discover how to best utilize graphics ...

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 After-Hours Times

The Conditional Formatting capabilities of Excel are powerful. This tip shows how you can use a simple approach to ...

Discover More

Coloring Identical Company Names

Want to know where duplicates are in a list of names? There are a couple of ways you can go about identifying the ...

Discover More

Highlighting Greater Than Average Dry Durations

If you need to find whether the duration between two dates is greater than the average of all durations, you'll find the ...

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

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2020-08-12 09:32:32

Peter Atherton

FormatWords Update.
I forgot to to check for duplicate entries of a keyword, so I ran it on a processed food allergy list (humungous entries for wheat) If there are If theere are likely to be duplicates in a cell this will tak care of it. Also an error check if User clicks Cancel

Sub KeysWordsBold()
Dim c As Range, r As Variant, cell As Range
Dim str As String, s As String, pos As Long
Dim wdCount As Integer, j As Long

'when user presses Cancel exit from macro
On Error GoTo EndIt
Set r = Application.InputBox("Select Area", "Keyword Selection", Type:=8)

'Loop through the cells with text
For Each c In Selection
str = LCase(c)
'Loop through the Keywords
For Each cell In r
s = LCase(cell)
wdCount = 0
pos = 1
'is keyword duplicated in Selection?
wdCount = (Len(str) - Len(WorksheetFunction.Substitute(LCase(c), LCase(cell), ""))) / Len(cell)
If wdCount > 1 Then
For j = 1 To wdCount
pos = InStr(pos, str, s)
c.Characters(pos, Len(cell)).Font.Bold = True
pos = pos + 1
Next j
ElseIf wdCount = 1 Then
pos = InStr(pos, str, s)
c.Characters(pos, Len(cell)).Font.Bold = True
If s Like "*palm*" Then
c.Characters(pos, Len(cell)).Font.Color = vbRed
End If
End If
Next cell
Next c
EndIt:

End Sub

The palm thing is just between rainforrests and me


2020-08-11 20:04:10

Peter Atherton

Sylvia, I forgot to mention that you can also select the range as normal, that is what the type:=8 is for.


2020-08-11 19:56:17

Peter Atherton

Sylvia Moritz, You can have several lists and choose which one to check the text with.

Firstly select the range to format, then run the macro (I assignd it to a form button in the VB editor but you can use a text box) Then you are promptd for a range of keywords. If they are named you can type the name, e.g. probate, law etc. The sit back


Sub KeysWordsBold()
Dim c As Range, r As Range, cell As Range
Dim str As String, s As String, pos As Long

Set r = Application.InputBox("Select Area", "Keyword Selection", Type:=8)

'Loop through the cells with text
For Each c In Selection
str = LCase(c)
For Each cell In r
s = LCase(cell)
pos = 0
pos = InStr(1, str, s)
If pos > 0 Then
c.Characters(pos, Len(cell)).Font.Bold = True
End If
Next cell
Next c

End Sub


2020-08-11 09:53:30

Sylvia Moritz

@Peter Atherton Thanks for the the great tip. I will test it out.

Using the macro would help if there was one set of keywords. I work in a law firm and there's never a single set of key words.

Thanks,

Sylvia


2020-08-11 06:20:56

Peter Atherton

Sylvia Moritz
You will have to use a macro for this as Excel uses the Characters function if you record this while formatting a word within a cell.
Here is a recorded macro with rubbish removed that shows the principle.

Sub Macro1()
ActiveCell.FormulaR1C1 = "Hello World"

With ActiveCell.Characters(Start:=7, Length:=5).Font
.FontStyle = "Bold"
End With

End Sub

If you decide to go this way Make a list of the key words and then search foor each keyword using Instr to get the position to get Start and use the length of Keyword for the second variable.

HTH


2020-08-10 15:01:16

Sylvia Moritz

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.


2019-02-12 12:27:07

Satish

I want to fill colour of a cell when match within other row.
I want to fill colour of a cell if any cell filled with colour in that row.


2017-02-14 01:04:37

kamil khan

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


2017-02-10 10:43:16

Kristen Bean

I'm making a schedule, it's a detailed schedule containing several different jobs per person. I want to differentiate jobs with different colors, but in the same cell; how would I do that?


2017-01-25 01:20:06

Rajiv

This really helped! Super easy to understand! Please update your directions for newer excel versions


2017-01-07 15:12:57

Frank

I am a teacher and have set up a homework tracking sheet. In certain boxes I will either have the homework assignment, if its a break, or if there is a test. For the homework assignment, it can either have something like "pg 23 #2 - 10" "11.4 notes" or "11.6 worksheet" is there a way to, in one rule, let it find the key words pg, notes, or worksheet and highlight it the one color? I know I can do it in 3 different rules but I am trying to consolidate.


2016-11-19 19:32:08

Kevin

Thanks for the tips to the writer and those with added ones in the comments!


2016-09-11 06:43:21

Willy Vanhaelen

@Joe

- add a third rule and select "Use a formula to determine which cells to format"
- enter the following formula: =AND(FIND("violation letter",LOWER(A1)),FIND("repair",LOWER(A1)))
- change A1 if you use another column
- make sure it the first rule


2016-09-08 15:13:37

Joe

basically I'm asking how do I write the "and" without it counting as text itself


2016-09-08 15:11:22

Joe

Using this method, if I or instance want the text "violation letter" to turn the block blue, and if I want the word "repair" to turn the block yellow. I can make it happen using 2 separate rules. How would I write a third rule that turns the block green if it contains the words "violation letter and "repair" with each text having the possibility of being placed any where in the sentence?


2016-05-12 14:06:51

Reks

As someone asked below, i need to highlight the searched word withing a cell. Right now when i do the search and find option it just highlights the cell which has those words, but i want the actual word within the cell to get highlighted. Like it works in word doc, you search for word and every time that word appears in the doc it gets highlighted and it even gives the number of found words.... many thanks in advance for ur help.


2016-05-12 06:41:46

Steve

Rich,
As far as I'm aware you can't use arrays or ranges in conditional formatting, but you can work around it.
If you place your "trigger" numbers in a column you can use the following formula in the conditional formatting rule box
=OR(A1=$F$1,A1=$F$2,A1=$F$3)
The "trigger" numbers are in Col F in this example - then if you need to change your "triggers" you only need to change the cells in Col F.
You can include a couple of blanks on the bottom of the list (if you include them in the formula).
Hope this is helps


2016-05-11 12:03:06

Rich

pls, I need you to help me. I am trying to format cells based on certain values. The cells contain values 1 to 90. I want to give a unique color to cells containing the ff. values: 31, 47, 55, 59, 61, 62, 79 and 87. I know I can do them one after the other but I need a way to them all at once.
Thanks for your help.


2016-04-25 08:09:08

Sulabh

Thanks Steve


2016-04-23 06:03:04

Steve

Sulabh,
You need to create 2 rules using "use formula to determine which cells to format" then use;

=B7="Matching"
=B7="Non Matching"

obviously change the cell address to the first cell in your range to be checked

Steve


2016-04-22 08:18:33

sulabh

I was working on same case where I had to highlight cell color based on 2 texts -"Matching" and "Not Matching". I created 2 separate rules for both cases.Now if even the cell values in "Not Matching", I am getting cell color defined for "Matching". Please help.


2016-04-15 02:38:38

Jody

I've actually realised that you can do this simply with a find and replace search.

You can format your replacement text also. This has done the job for me.


2016-04-15 02:28:49

Jody

Hi.... I am interested in knowing if there was a response to Sivahari's question regarding highlighting/formatting the found text within the cell and not the entire cell. I need to do this if possible also.

Thanking you for your assistance.


2016-03-12 03:17:44

David

Cheers!


2015-09-18 02:24:37

Hira

Thank You!!!


2015-09-07 05:00:30

Bintang

Hi Dave,

is it possible to make formula that search text only at the end of the sentence?
ex: Im gonna search Y in
003.0425.01.Y
and then im need to highlight those cell with Y
thanks in advance


2015-03-09 06:04:49

Dave S

rk
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):

[Book1]Sheet1!$B$3

If the workbooks are in different locations, the workbook reference will include the file path.


2015-03-09 01:46:48

Leigh-Ann

Great. thanks.


2015-03-07 06:08:10

Sivahari

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


2015-03-07 05:02:52

rk

i want to use cells of two or more different sheets of different excel files in a macro. is itposbbile? then how?


2015-03-07 04:58:27

Amjad Abuaysheh

Awesome. Thanks for your great tips :)


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.

Newest Tips
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.