See More About: , ,

How to Find and Replace Cell Formats in Excel

by

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 formats in excel How to 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 formats in excel1 How to 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 formats in excel2 How to 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 formats in excel3 How to 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 formats in excel4 How to 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 formats in excel5 How to 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 formats in excel6 How to 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 formats in excel7 How to 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.

See More About: , ,
Originally posted Feb 01, 2010

We close comments after 30 days on GilsMethod.com and ask that you post questions and issues on our Answers website.

This helps us better serve you by allowing us to keep track of unanswered questions that are usually hidden in long comment threads.

Answers.GilsMethod.com is a community-powered support website where you will be able to post questions, and other users including us will respond with expert advice.

So if you are having trouble with your PC, Mac, iPad, iPhone, or other piece of technology, head over to Answers.GilsMethod.com and ask away.

  • Bong

    Great! Another question: can you use find/replace feature to find a word and replace its format. I only want the word to change and not the whole cell, is that possible?

  • http://www.facebook.com/office kim_office_team

    This is a great tutorial. I highly recommend checking out Ribbon Hero, a new add in from Office Labs, to continue learning more about Excel's (and Word and PowerPoint) features. Download it for free here: bit.ly/8kObRk. Then, you can challenge your friends to see who knows Office best!

    Cheers,
    KIM
    MSFT Office Outreach Team

  • http://www.facebook.com/office kim_office_team

    This is a great tutorial. I highly recommend checking out Ribbon Hero, a new add in from Office Labs, to continue learning more about Excel's (and Word and PowerPoint) features. Download it for free here: bit.ly/8kObRk. Then, you can challenge your friends to see who knows Office best!

    Cheers,
    KIM
    MSFT Office Outreach Team