Excel’s Find and Replace is quick and powerful. It’s great for replacing text. Did you know you can also use it to replace formatting? Well you can in Excel 2007 and Excel 2010.

Let’s say you’ve created a wonderful spreadsheet report and presented it to your boss and he says “I hate the orange background, change it to green”. You stress because you’ve used the orange colour throughout the file and its going to take ages to change all the ranges.

Well thanks to Find and Replace it won’t be such a big deal. The Find and Replace (Ctrl + h if you’re a keyboard shortcut person) is on the far right of the Home ribbon.

The cell(s) you select before using Find and Replace determines where the Find and Replace operates. If you have one cell selected when you activate Find and Replace then Excel will review the whole sheet. If you select two or more cells then only the selected cells are reviewed by the Find and Replace.

To use the Replace Format feature click the Format drop down (see image below).

You can click the Format … option to go to the Find Format dialog ( see image below) which allows you to specify the format to find including number, alignment, font etc. Alternatively you can select Choose Format From Cell option which allows you to select a cell that has the format you want to change. This process is the same to select the format you want to replace it with.

You can then use the Find and Replace as normal using Replace All or Replace one cell at a time.