Microsoft Office Tutorials and References
In Depth Information
A useful, but often overlooked, Excel feature is the ability to search for (and replace) cell
formatting. For example, if you have cells that use the 14-point Calibri font, it’s a simple matter to
change the formatting in all those cells to something else.
The process isn’t as intuitive as it could be, so I walk you through the steps. Assume that your
worksheet contains many cells that are formatted with a yellow background and in 14-point
Calibri in bold. Furthermore, assume that these cells are scattered throughout the workbook. The
goal is to change all those cells so that they’re displayed with 16-point Cambria in bold, with
white text on a black background.
To change the formatting by searching and replacing, follow these steps:
1. Click any single cell and choose Home
Find & Select
Replace (or press Ctrl+H)
to display the Find and Replace dialog box.
If you want to limit the searching to a particular range, select the range rather than a
2. In the Find and Replace dialog box, make sure that the Find What and Replace With
fields are blank.
3. Click the upper Format button (the one beside the Find What field) to display the Find
Format dialog box.
If these fields aren’t visible, click the Options button to expand the dialog box.
4. You can use the Find Format dialog box to specify the formatting you’re looking for, but
it’s much easier to click the arrow on the Format button, click Choose Format from Cell,
and then click a cell that already has the formatting you want to replace.
5. Click the lower Format button (the one beside the Replace With field) to display the
Replace Format dialog box.
6. You can use the Choose Format from Cell option and specify a cell that contains the
replacement formatting, or you can use the tabs in the Find Format dialog box to specify
the desired formatting.
In this example, click the Font tab and select Cambria, size 16, bold style, and white color.
On the Patterns tab, choose black as the cell shading color. At this point, the Find And
Replace dialog box should resemble Figure 22-1.
7. In the Find and Replace dialog box, click the Replace All button.