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 tip_328.php 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 Styles 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 1111.html for further details.

.