Weekly Computer Tip # 329
28 January 2010

find and highlight all occurrences of a word or number (Excel 2003-2007)

Last week I told you how to use Word's "Find" feature to highlight all occurrences of a particular word in the document. (See http://www.roem.co.uk/tip_328.html if you missed it.) Karen (you know who you are; thanks for the inspiration!) asked whether you can do the same in Excel. Well, you cannot using the Find feature, but you can find and highlight particular words if you use "conditional formatting". There are various ways to do this (good old Microsoft!) but let's go for the one I think is easiest.

Here's how:

In Excel 2007:

1. Select the cells that contain the word or number you want to highlight. (Or press CTR+A to select the whole worksheet.)
2. On the Home tab, in the Style group, click on the Conditional Formatting button.
3. Click "Highlight Cells Rules", followed by "Text that Contains".
4. Type the text or number that you want to search for, select your preferred colour and click OK.

Excel 2003:

1. Select the cells that contain the word or number you want to highlight. (Or press CTR+A to select the whole worksheet.)
2. Choose the Format, Conditional Formatting command.
3. Select "equal to" from the second dropdown box.
4. In the next box, type the text or number that you want to search for.
5. Click on the Format button, select the Patterns tab and select your preferred colour.
6. Click OK twice.

Unlike in Word, the text is not just highlighted on the screen; it is also highlighted when the document is printed.

To turn off the highlighting:

1. In Excel 2007: On the Home tab, in the Styles group, click on the Conditional Formatting button, and then click "Clear Rules", followed by "Clear Rules from Selected Cells" or "Entire Sheet".
2. In Excel 2003: Choose the Format, Conditional Formatting command, click on the Delete button, select the condition you want to delete and click OK twice.

Finally, you have just over 3 days left to take part in Roem's 10th anniversary quiz and win a bottle of 10 year old whisky. All you have to do is identify events that took place in 2001; see http://www.roem.co.uk/1111.html for further details.

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   Virtual Press Room   Disclaimer