Advanced find and replace

Our friend Bahadur Singh has a peculiar problem today. He says “I’ve marked a few hundred cells in a large workbook using a red font. My manager is fussy and wants all the red cells changed to blue. The red cells are not continuous.” Many of us would have faced such situations and than either would have spent hours doing it or might not have followed the orders of manager.

Also we frequently use find and replace functions to change a particular word to another but can we use the same to find and change a particular colour, font, etc to another?

We can use Find and Replace to change formats. Here’s what to do:

1. Select the entire range that contains the red cells.

2. Select Edit – Replace or use “Ctrl + H”. Excel will display the Find and Replace dialog (see Figure 1130).

3. Click the Options button to show additional options.

4. Leave the Find What and Replace With boxes blank. On the right side, choose the dropdown next to the top Format button. Excel will offer options for Format, Choose Format from Cell, and Clear Find Format. For the maximum flexibility, choose the Format option. Excel will display the Find Format dialog.


5. In the Find Format dialog, go to the Font tab. Change the Color dropdown to Red. Do not select a font. Don’t make any selections on any other tab. Click OK to return to the Find and Replace dialog.

6. Click the second Format button. On the Font tab, choose blue as the Color.

7. After specifying both the original and new font colors, click the Replace All button in the Find and Replace dialog.

All the red fonts are changed to blue.

The option tab in the find and replace dialog box can be used to find and replace font style, font size, format, font colour, etc.

Also at times we need to find a particular word in the entire file i.e. across sheets. The option tab can be useful to find and replace the entire workbook. This can be done by changing the within field from sheet to workbook (as highlighted in the picture above).