This is part 2 of a series focusing on using RPA to automating processes in Excel – we are covering everything from when and why to use RPA, to details around how to most effectively design the solutions and get the most bang for your buck. Below is a preview on this series in its entirety:
Part 2: Design Considerations for Stability
Part 3: Publishing for Collaboration
Part 4: To Click or Not To Click?
It is truly amazing to think about the prevalence of Microsoft Excel across companies large and small, and how countless mission-critical business processes rely on spreadsheets. Of course, the application has been around since the late 1980’s and is quite versatile, so it does make sense that organizations find themselves in this position, but the numbers are still staggering. Forrester surveys estimate that over 80% of companies are using MS Office and Excel. And many of the others? Well, they’re likely using competing spreadsheet programs like Google Sheets.
So, what does all of it have to do with RPA? It’s simple: due to the overwhelming prevalence of Excel-based business processes, you are likely to encounter good candidates for automation that involve spreadsheets, and that shouldn’t scare you! While spreadsheets are notoriously brittle, if you take a few simple design recommendations into account you can automate Excel-heavy processes using RPA with a high degree of confidence and stability.
Templatize!
First and foremost, create Gold Template versions of the spreadsheets used in your business process. This is the most critical aspect of how automations are designed to work within Excel. Don’t assume that you can effectively pick up spreadsheets currently sitting on users’ desktops and plug in an RPA Bot to interact with them…it’s not quite so easy. You will be much better served to spend some analysis time on the current state spreadsheets (do different users have slightly different formats?), determine what a standardized format should look like, and get it created before building the RPA script.
I’m thinking of a recent use case which involved a team of ~10, who rotated responsibility for producing and publishing an Excel-based report each day to the rest of the group and their management. And guess what? Over time they had naturally each created their own “flavor” of the spreadsheet, leading to many unique formats used to produce a single report. Before attempting to build automation, we needed to get agreement on a single “best” format, and institute that as the Gold Template moving forward.
And Don’t Forget to Secure those Templates
Once you got your Gold Template created, you’ll want to lock it down. Assign 1 business owner of the template, then make sure the live version sits in a shared location that can be accessed by the RPA Bot(s) and the business owner. On top of that, you can password protect the workbooks for structure, as well as each of the worksheets. This way, you will minimize the risk of things changing in the templates, and in turn causing errors for the RPA Bots running processes. Then, when developing the RPA scripts as a best practice the Bots can be instructed to unlock/lock the spreadsheets before and after completing the relevant steps in the process. In fact, those passwords can also be stored securely using an RPA software’s “Command and Control” platform e.g. UiPath Orchestrator’s credential vault.
(Don’t worry, we’ll talk more about shared access to templates, reports, and more in part 3 of this series dedicated to all things to do with Collaboration)
The Bottom Line
At the end of the day, your organization is likely stuck with a bunch of business processes that rely heavily on Excel spreadsheets. And thankfully, in the world of RPA that is completely ok. Excel does bring some unique challenges as opposed to automating processes that use Enterprise Applications, but with a little bit of careful analysis and design rigor up front you can absolutely create effective and stable processes using RPA with Excel spreadsheets.
