How to Find and Replace Cell Formats in Excel

3
24794

One of Excel’s most powerful formatting tools is the Find and Replace command. Find and replace allows users to specify elements to locate in a spreadsheet. You can use this powerful command to locate a formula you wish to update, a set of values that have changed, and the formatting applied to cells. In this tutorial we will walk you through steps necessary to find and replace formatting of cells in Excel.

Note: I used Excel 2007 to create this tutorial.

Find and Replace Cell Formats

Find and Replace Cell Formats in Excel

1. Click on the Home tab located in the Office Ribbon

2. Click on the Find and Replace button located in the Editing group

3. Click on Replace from the menu

Find and Replace Cell Formats in Excel

4. When the Find and Replace dialog box opens click on the small arrow next to the first Format button.

5. Click on Choose Format From Cell from the drop-down menu.

a. If the Find and Replace dialog box does not appear like the one above, click on the Options button to expand the dialog box.

Also note that when you open this dialog box again to locate another type of format, you must click on Clear Find Format from the drop-down menu.


Find and Replace Cell Formats in Excel

6. Choose the format you wish to replace.

b. Note how the cursor changed to an eyedropper.

c. (Screenshot below) – Notice that the format you selected is previewed

 

Find and Replace Cell Formats in Excel

7. When the Find and Replace dialog box opens click on the small arrow next to the second Format button.

8. Click on Choose Format From Cell from the drop-down menu.

Find and Replace Cell Formats in Excel

9. Choose the format you wish to use as reference.

d. Note how the cursor changed to an eyedropper.

e. (Screenshot below) – Notice that the format you selected is previewed

Find and Replace Cell Formats in Excel

10. Once you’ve made your selections you have two options;

Replace – Will only replace the selected cell that matches the format designated in the dialog box.

Replace All – Will replace the formatting for all of the cells that match the format specified in the dialog box.

Click on the one you wish to use. (I chose Replace All for this tutorial)

Find and Replace Cell Formats in Excel

11. A dialog box will appear confirming the search and replacement of the cells formatting. Click OK to close the dialog box.

12. Click on the Close button to close the Find and Replace dialog box.

Find and Replace Cell Formats in Excel

f. The screenshot above illustrates how the formatting for the two cells highlighted above changed from a green pattern to a yellow pattern.