every time I type a specific word in a specific cell using Excel 2013 it is replaced with different word. This happens with EVERY NEW document opened.
EX: in cell B1; the word "Title" is typed and then hit enter. The word automatically changes to "Executive Director". I have checked the backstage option in AutoCorrect and this is not listed.
Loading
ExcelTips (Menu Interface)
This Site
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
Author Bio
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.
Learn more about Allen...
Subscribe
Helpful Links
ExcelTips FAQ
ExcelTips Resources
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
Highlighting Cells Containing Specific Text
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later 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:
- Select the range of cells.
- Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
- In the drop-down Condition list, choose "Formula Is". (See Figure 1.)
- 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.)
- Click on Format. Excel displays the Format Cells dialog box. (See Figure 2.)
- 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.
- Click on OK to close the Format Cells dialog box.
- Click on OK to close the Conditional Formatting dialog box.
Figure 1. The Conditional Formatting dialog box.
=NOT(ISERR(SEARCH("shawn",A1)))
Figure 2. The Format Cells dialog box.
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 "shawn" in cell F7, then you could replace the formula in step 4 with the following:
=NOT(ISERR(SEARCH($F$7,A1)))
Now, you can search for something different just by changing the characters in cell F7.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2671) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Highlighting Cells Containing Specific Text.
Related Tips:
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Leave your own comment:
Comments for this tip:
Phyllis Hawkins 07 Apr 2016, 17:22
Leah 05 Nov 2015, 16:25
Hello,
How do I highlight columns C-F if A contains "Site Identified"? Not if A equals "Site Identified", I am aware of how to do that. Thank you!
Leah
How do I highlight columns C-F if A contains "Site Identified"? Not if A equals "Site Identified", I am aware of how to do that. Thank you!
Leah
pol 29 Jul 2015, 18:01
Instead of highlighting, is it possible to ask for a password when a certain word is typed? Example I don't like the word "apple" in my file so whenever I type apple, a prompt will come up asking for an override password. Thanks for the help :)
Shail 20 Jun 2015, 06:15
I want to automatic display "Insert rate" in blank cells backgroun, can it is possible
swap 06 Jun 2015, 03:49
i want to highlight the cell e.g A9
and specified text look up area is B9 to Z9
is their any rule to do it.
and specified text look up area is B9 to Z9
is their any rule to do it.
DavidMD 12 May 2015, 17:53
I have two sheets in a workbook.Sheet1 has a list of potential numbers in column B. Sheet2 is a list of numbers used listed in column B, a description in column C, a city in column D, Size in E, Color in F and Priority in G. I used VLOOKUP to populate Sheet1 B with the dscription from column B matching number in Sheet2.... =IFERROR(VLOOKUP(B5,'Sheet2'!B:C,2,0),"").
I would like to high-light the descriptions of the high priority items red, and lower priority yellow. How can I Highlight a field... if Sheet1 B2 = 1234 and Sheet2 number 1234 column F = High Priority, highligh Sheet1 B2 Red?
I would like to high-light the descriptions of the high priority items red, and lower priority yellow. How can I Highlight a field... if Sheet1 B2 = 1234 and Sheet2 number 1234 column F = High Priority, highligh Sheet1 B2 Red?
Willy Vanhaelen 26 Mar 2015, 08:36
@Saoud
The formula to use is: =Left(A1,1)="7"
This works with numeric data as well as text.
The formula to use is: =Left(A1,1)="7"
This works with numeric data as well as text.
Saoud 26 Mar 2015, 04:46
Hi there,
I want to apply conditional formatting for cells (in column A) where the first number is 7. Can you please advise how to do that?
I want to apply conditional formatting for cells (in column A) where the first number is 7. Can you please advise how to do that?
Michael (Micky) Avidan 21 Mar 2015, 06:08
Btw, I warmly recommend that you check the new feature of CF as per partially/specific text - new feature in "Excel 2007" and above:
http://excelribbon.tips.net/T006235_Highlighting_Cells_Containing_Specific_Text.html
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
http://excelribbon.tips.net/T006235_Highlighting_Cells_Containing_Specific_Text.html
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Michael (Micky) Avidan 21 Mar 2015, 06:00
@TO WHOM IT MAY CONCERN.
1) To my opinion, Allen's formula is the most suitable because it is not Case sensitive (all strings can be typed in Upper/Lower characters).
2) However, his suggested CF formula can be considerable shorter because the ISERROR check is not needed because any mismatching will result as: #VALUE! which will NOT trigger the CF.
Any match (regardless its position within the text) will end up with a positive number. Any positive number will be interpreted, by the CF rule, as TRUE.
This is the CF formula I have in mind: =FIND(F7,A7)
I hope I made myself clear.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
1) To my opinion, Allen's formula is the most suitable because it is not Case sensitive (all strings can be typed in Upper/Lower characters).
2) However, his suggested CF formula can be considerable shorter because the ISERROR check is not needed because any mismatching will result as: #VALUE! which will NOT trigger the CF.
Any match (regardless its position within the text) will end up with a positive number. Any positive number will be interpreted, by the CF rule, as TRUE.
This is the CF formula I have in mind: =FIND(F7,A7)
I hope I made myself clear.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Willy Vanhaelen 20 Mar 2015, 06:22
@Dave
@Barbara
If your data is in column A this formula should do:
=FIND("your text",LOWER(A1))
Note that "your text" should be lower case.
@Barbara
If your data is in column A this formula should do:
=FIND("your text",LOWER(A1))
Note that "your text" should be lower case.
dave 19 Mar 2015, 07:37
Hi< I was wanting to do the same as Barbara posted on 21st jan; that is highlight cells that contain a text string that will also contain other text
Willy Vanhaelen 17 Mar 2015, 06:45
@David
Supposing your data are in column B, this formula should do:
=OR(B1="y",B1="n",B1="o")
Supposing your data are in column B, this formula should do:
=OR(B1="y",B1="n",B1="o")
david 16 Mar 2015, 15:17
Im looking for a way to highlight cells if the contain a y or n or o.
thank you
thank you
Willy Vanhaelen 08 Feb 2015, 05:52
@Deb
See to it that there are no dollar sings ($)in the formula.
So it should be: =AND(B1<>"Yes",B1<>"Disabled")
See to it that there are no dollar sings ($)in the formula.
So it should be: =AND(B1<>"Yes",B1<>"Disabled")
Deb 07 Feb 2015, 12:02
@Willy:
Thanks for your help
I used it, but its working properly.
Even if there are any other values in the column or blanks, its all getting formatted based on what value I am having on the first cell (B1).
If I am changing the values in the B1 cell, all the cells in the column are also effected.
Does it has something related to chaging the cell value or cell selection range? Can you please help?
Thanks for your help
I used it, but its working properly.
Even if there are any other values in the column or blanks, its all getting formatted based on what value I am having on the first cell (B1).
If I am changing the values in the B1 cell, all the cells in the column are also effected.
Does it has something related to chaging the cell value or cell selection range? Can you please help?
Willy Vanhaelen 02 Feb 2015, 05:32
@Deb
If those data are in column B, highlight that column.
The formula to enter in the conditional format dialog is:
=AND(B1<>"Yes",B1<>"Disabled")
If those data are in column B, highlight that column.
The formula to enter in the conditional format dialog is:
=AND(B1<>"Yes",B1<>"Disabled")
Deb 01 Feb 2015, 06:05
I have data in a column for 1000 cells. There is 6 types of data.They are - Yes, No, Disabled, Enabled, -, 0. There may be blank cells also in between. Those cells not containing Yes or Disabled, should be highlighted with some colour. I am not able to find a suitable formula to use for conditional formatting these cells in the column. Can anyone please help?
B Gomez 30 Jan 2015, 16:04
It was very helpful! Thank you very much :)
Barbara 21 Jan 2015, 15:52
Hi...I am trying to use Cond Formatting for cells that contain a specific word...possibly embedded within other text. Is this possible?
Steve 16 Dec 2013, 19:04
Hey,
Thanks for this. Using 2003 things take a little more 'thought' and I was trying to do the above via COUNTIF but couldn't get it to be specific enough on the search so it was highlighting every cell.
This worked first time.
Thanks
Thanks for this. Using 2003 things take a little more 'thought' and I was trying to do the above via COUNTIF but couldn't get it to be specific enough on the search so it was highlighting every cell.
This worked first time.
Thanks
Barry Fitzpatrick 07 Feb 2013, 06:12
Buddy,
I don't know if you've resolved this yet.
Select the range A3:A2000 then put the formula:
=NOT(ISNA(MATCH(A3,$P$3:$P$200,0)))
as the Conditional Formatting "Formula Is" (Excel 2003) or "Use a formula to determine which cells to format" in Excel 2010, then setting the desired format to highlight the matching values.
This works by seeing if each cell in the range A3 to A2000 matches a cell value in P3 to P200 using the MATCH function. If there is a match the function return a value corresponding to the position of the matched value in the P3:P200 range; if no match it return #N/A. The ISNA function looks for this which would give a TRUE if the MATCH function returns #N/A, the NOT turns this into a FALSE so that the conditional format is invoked by matching value.
I don't know if you've resolved this yet.
Select the range A3:A2000 then put the formula:
=NOT(ISNA(MATCH(A3,$P$3:$P$200,0)))
as the Conditional Formatting "Formula Is" (Excel 2003) or "Use a formula to determine which cells to format" in Excel 2010, then setting the desired format to highlight the matching values.
This works by seeing if each cell in the range A3 to A2000 matches a cell value in P3 to P200 using the MATCH function. If there is a match the function return a value corresponding to the position of the matched value in the P3:P200 range; if no match it return #N/A. The ISNA function looks for this which would give a TRUE if the MATCH function returns #N/A, the NOT turns this into a FALSE so that the conditional format is invoked by matching value.
Barbara 06 Feb 2013, 18:36
Wow! Great way of thinking and very easy to apply.
Becky 22 May 2012, 20:54
Fantastic tip. Exactly what I needed and very easy to follow. Thanks!
buddy bandol 13 Feb 2012, 01:53
hi please help me to solve on how will i use highlighting for ex. i have data on a cell a3 up a2000 i want that data will be highlighted if i put same data on a cell p3 upto p200. is it posible?