Power Quey is a strong command used in Microsoft Excel. It allows you to perform different steps in order to transform the data within your Excel sheets. One of the functionalities it offers is the facility of replacing values with great ease. In order to do that, all you need is to go through the following simple steps.

In the first step, you need to select the data you want to process and transform it into a table. You can either do that by clicking on ‘Insert’ and then ‘Table’. Or you could simply press Ctrl + T for that purpose.

In the second step, you need to click on ‘Data’. In the menu, you need to select ‘Get and Transform’. Then click on ‘From Table’. You can also do that by clicking on ‘Power Query’ followed by ‘Excel Data’ and then selecting ‘From Table’. This step is different for different versions of Microsoft Excel so you can go for whichever works for your version of Excel.

The above commands would open up a dialogue box called Power Query Editor. Now you can change the country Australia to New Zealand. It is important to select the Country column by making a click on the header Country.

Then you need to click on ‘Transform’ followed by ‘Replace Values’.

This will open a new dialogue box with title Replace Values. You need to put Australia within the Value to Find field and New Zealand within the field marked as Replace With. When you press OK, all the values saying Australia would be replaced with New Zealand. You can go through the work sheet to see the new changes.

In the end, you need to make a click on ‘Home’ tab and select ‘Close and Load’ in order to open a new workbook having all the updated values. That’s it! Now you have a new table filled with updated values.