Find and Replace in Excel using Power Automate Desktop

A popular RPA use case is finding and replacing data within a spreadsheet. Within Excel, we do have native capabilities that can be leveraged including the Find and Replace Dialog.

However, our options were limited when it came to using this within Power Automate Desktop. Yes, we could use hotkeys to access this particular feature but that didn’t scale that well and introduced some additional complexity/risk.

But the good news is that Microsoft provided a native action with Power Automate Desktop that allows us to use this Find and Replace feature in a more reliable and scalable manner. A built-in action can now be found under Excel called Find and replace cells in Excel worksheet.

Note: This feature is found in the August 2021 release. If you don’t see it, please update to the latest version of the software.

Scenario Walk-through

Let’s walk through a sample use case to see how this new feature works. We have a simple spreadsheet that has 4 columns. Within the Counter Party column, we will find a list of companies that we do business with. In the case of Contoso we have recently been informed that they have changed their company name to Contoso Inc. To address this requirement, we want to go through each row and update any instances of Contoso to Contoso Inc.

Solution

1. Before we use the Find and Replace action, we need to first open our spreadsheet. We can do so by using the Launch Excel action and providing the location of our Document path.
   

2. Next, we can add the Find and replace cells in Excel worksheet action. Within this action we have many options including whether we want All matches (or the first match), whether to Match case and Match entire cell contents. We can also specify our Search by criteria. Since all of my values are in 1 column we will set our value to be Columns.
   

3. Lastly, we need to save and close our spreadsheet an can do so by adding the Close Excel action and indicating that we want to Save document.
   

Testing

After running our Desktop flow, we will discover that each instance of Contoso has been replaced with Contoso Inc.

Conclusion

Find and Replace functionality is a popular requirement in RPA use cases. Up until now, we had to find our own solution. That was up until the August 2021 release of Power Automate Desktop where we now have a built-in action that is easy to use.

If you would like to see this content in video format, please check out the following YouTube video: Find and Replace in Excel using Power Automate Desktop.