Datasheet

Replacing Formatting

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 on any single cell, and choose Home ➪ Editing ➪ 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 single cell.

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.

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 and click Choose Format from

Cell. Then click on 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

Find Format dialog box again.

6. You can use the Choose Format from Cell option and specify a cell that contains the

replacement formatting. Or, 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 20-1.

7. In the Find and Replace dialog box, click the Replace All button.

Figure 20-1: The Replace tab in the Find and Replace dialog box.

Tip 20: Replacing Formatting

Part I

04_137666 ch01.qxp 5/22/07 7:31 PM Page 53