Why is Replace so useful in Microsoft Word, Excel, PowerPoint and Power BI?

Aug 3, 2023 | Excel Hints and Tips, Power BI Hints and Tips, PowerPoint Hints and Tips, Word Hints and Tips

Replace is a simple but powerful feature throughout the Microsoft suite.  It helps you to find some text (or numbers or dates) and replace it with something else.

Microsoft Word

In Word it is often used where information has changed but has already been used throughout the file.  This could be a company name, a product name amongst other things.

  1. Click on the Replace icon on the Home tab
Replace icon Word image

2. In the first box type what you want to find

3. In the second box what you want to replace it with

4. If the More button is pressed then extra options appear.  Particularly useful are the Format button where the Format of the text that is being used as a replacement can be formatted at the same time as the text being replaced.  Also handy is Special where special characters can be found such as line breaks which is helpful if you want to remove them.  They can be replaced with nothing.

Replace dialog box word image

Microsoft Excel

If Excel is being used for data analysis then it is vital the data is consistent.  Using replace to find consistent inconsistencies is a way to help this.  An example would be replacing Ltd with Limited if there is a mix of the 2 in your data.

  1. Click on the Find and Select icon on the Home tab
  2. Choose Replace
Find and Select icon image

3. In the first box type what you want to find

4. In the second box what you want to replace it with

5. It is easy to end up replacing words that you don’t want to change so if the words are short then ticking the Match Case or Match entire cell contents boxes can be really useful.  If this is not visible then clicking the Options button will allow them to be seen.

Replace dialog excel image

Microsoft PowerPoint

The use discussed for Word is the most likely option in PowerPoint too and it is used in a similar way but there is another option that is more helpful for PowerPoint and that is replace Fonts.  If you have created much of the presentation in one font and realise that is not on brand then it can be changed very quickly.

  1. From the Home tab select the drop down on the Replace icon and choose Replace Fonts
Replace fonts image

2. This can replace one front with another across the whole presentation.

Microsoft Power BI Desktop

Power BI is likely to have a similar situation to Excel but may also be used if you start creating visualisations and then realise that labels or text are long and abbreviating certain words would make it work better.   In this case you are more likely to shorten Limited to Ltd than the other way round.

The procedure to do this is totally different to the other packages.

  1. From the Home tab select the Transform Data icon.  This will open the Power Query Editor
Transform Data image

2. Select the table then click in the column you want to change.  Unlike all the other Microsoft packages where it will perform replace on the whole document if nothing is selected with Power BI it is vital to select the column you wish to change.

3. Click on the Replace Values icon

Replace values Power BI image

4. Select the value to find and what to Replace it with.

Replace values dialog Power BI image

5. Click OK.

6. Close and Apply to return to Power BI Desktop and all visualisations already created using that field will be updated.

Shortcut for Replace

In most Microsoft packages the shortcut to replace is CTRL + H.  This doesn’t work in Power BI.

 

 

Further Reading

If you’ve enjoyed reading about why Replace is so useful in Microsoft Word, Excel, PowerPoint and Power BI, there are some other blogs below that you might find useful:

Want to learn more about Microsoft? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Courses I run.