Changing formats in Excel in one go

A relatively unknown feature is the ability to change formats in Excel in one go using the FIND/ REPLACE tool.

So let’s say, as per below, all the amounts are formatted into South African Rands but we need to convert them all to US Dollars. We could go to each one and change it, or try and highlight them all and then change it, but on big spreadsheets this can be time consuming.

Changing-formats-in-Excel-in-one-go

A better way is to use the FIND/ REPLACE tool. If you use the FIND (CTRL F) and click the OPTIONS button, you will see a button that says FORMAT. By clicking on it you can tell Excel what you are looking for, in this case a Currency Format of Rands

Changing-formats-in-Excel-in-one-go

When you have specified this, you need to tell Excel what to replace it with. You click on the REPLACE tab and the FORMAT button next to REPLACE. Choose what you want the format to be (US Dollars in this case).

Changing-formats-in-Excel-in-one-go

When you click REPLACE ALL you will see that any cell that had a Rand currency converts to US Dollars as shown below, and all other cells are left alone.

Changing-formats-in-Excel-in-one-go

It is also worth noting that you can pick the format from one of the cells by clicking on the dropdown arrow by Format and choosing the ‘Choose Format From Cell’.
Changing-formats-in-Excel-in-one-go

If this is useful, have a look at our online Intermediate Excel course for more useful tips and tricks.