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:
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.
Leave your own comment:
Comments for this tip:
Frank 07 Jan 2017, 15:12
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.
Kevin 19 Nov 2016, 19:32
Thanks for the tips to the writer and those with added ones in the comments!
Willy Vanhaelen 11 Sep 2016, 06:43
- 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
Joe 08 Sep 2016, 15:13
basically I'm asking how do I write the "and" without it counting as text itself
Joe 08 Sep 2016, 15:11
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?
Reks 12 May 2016, 14:06
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.
Steve 12 May 2016, 06:41
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
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
Rich 11 May 2016, 12:03
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.
Sulabh 25 Apr 2016, 08:09
Steve 23 Apr 2016, 06:03
You need to create 2 rules using "use formula to determine which cells to format" then use;
obviously change the cell address to the first cell in your range to be checked
sulabh 22 Apr 2016, 08:18
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.
Jody 15 Apr 2016, 02:38
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.
Jody 15 Apr 2016, 02:28
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.
David 12 Mar 2016, 03:17
Hira 18 Sep 2015, 02:24
Bintang 07 Sep 2015, 05:00
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
Sivahari 07 Mar 2015, 06:08
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.
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 :)