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.
Find all the same formats and highlight the cells
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.
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).
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.
Select the format from an example cell (and a warning)
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’. Just be careful though because when you do this, it looks at the EXACT format. So if the one you are referencing is filled with blue and all the others are white, it will only find the blue ones.
If this is useful, have a look at our online Intermediate Excel course for more useful tips and tricks.