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
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
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.
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
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.
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
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)
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.
f. The screenshot above illustrates how the formatting for the two cells highlighted above changed from a green pattern to a yellow pattern.
