Change cell formats with find-replace in excel

Posted on April 4th, 2008 in hacks , ideas , Learn Excel , technology - 5 comments

Do you know that you can use excel find – replace dialog to change cell formats?

Often we prepare a report or project plan and our boss or someone else in the team feels that all the red color cells need to change to blue or something similar, worry not, you can use find-replace (ctrl+h) to do this for you.

I have created a small video demo on how to do this, check it out yourself

If you are not able to see the video:

  1. Press Ctrl+h
  2. click on “Format…” button on “find what:” area and specify the source format
  3. click on “Format…” button on “replace with:” area and specify the target format
  4. Select “replace all” or “replace” depending on your need
  5. Smile, you have just automated a mechanical and boring task

5 Responses to “Change cell formats with find-replace in excel”

  1. Hypnos says:
    April 5, 2008 at 6:51 am

    I see that you’ve become quite the excel guru.

    Maybe I should start blogging about powerpoint techniques :P

    Reply
  2. ajay says:
    April 7, 2008 at 9:23 am

    thanks for this excel tip.
    looking for some tips on excel

    Reply
  3. Chandoo says:
    April 7, 2008 at 10:16 am

    @Hypnos .. hehe… go ahead, enlighten the world with what you know
    @Ajay .. thanks for visiting phd, hope you like the content here :)

    Reply
  4. Spreadsheet Formulas : Edit Using Find / Replace | Pointy Haired Dilbert - Chandoo.org says:
    February 18, 2009 at 5:24 am

    [...] you know that you can use find / replace to change spreadsheet formatting too [...]

    Reply
  5. Atul says:
    July 19, 2011 at 9:11 pm

    How do I find and replace a specific character with formatting only on the replaced character? For example:
    ATUL –> replace L with a bold, green G.
    Laugh –> replace L with a bold, green G.

    I amtrying to highlight the change.

    Reply

Leave a Reply