Robotic Process Automation using Excel Macros

Microsoft Excel is probably one of the tools that almost every company in Singapore uses.  We use Excel almost everyday.  However, we do a lot of mundane, repetitive, non-value-added tasks such as copy-and-pasting of data from one Excel spreadsheet to another, looking up of data from another spreadsheets, formatting of spreadsheet into a specific format and consolidation of data from multiple Excel spreadsheets into one master spreadsheet, etc.  Many of these tasks can actually be automated using Excel Macro. 

Besides Excel VBA, Excel actually has two other macro tools: Power Query and Power Pivot.

Power Query comes with the Microsoft Excel that all of us are using. It’s one of the least known yet powerful, and probably the “best kept secrets” from Microsoft. It is especially powerful in cleaning, transforming and consolidating data from multiple spreadsheets. Many research have shown most of us spent more than 70% of our time cleaning up the data before we can perform any analysis.

The best part about Power Query is you don’t need to learn or use any code to do any of this. We call this No-code RPA.

Another amazing thing is, it allows you to set up a query once and then reuse it with a simple refresh. Once you have defined the logic say for the January report, you can generate the February, March, etc, report at the click of a button!

All the 3 Excel macros – Excel VBA, Power Query and Power Pivot – will be covered in class.