Excel Automation
What is Microsoft Excel Automation?
Microsoft Excel Automation is the automation of spreadsheets, and their use of data, in Microsoft Excel using Visual Basic for Applications (VBA). Excel Automation can also include setting up spreadsheets to use tables and advanced formulas. Advanced Excel setup can allow you to use less VBA code.
Microsoft Excel is extremely powerful. Imagine reporting and analysis that can be completed in minutes, instead of hours. Our company can automate anything in Excel and can completely customize solutions to fit how you perform your business processes.
Our Process
Approach to providing Microsoft Excel Automation solutions for any of your company’s business processes:
- 1Assess the business process with a Subject Matter Expert
- Can the process be automated in Excel and not require an RPA robot to be built? This can be a good thing because Excel automation can be created much faster than RPA and you will not incur any additional licensing fees, since most companies already use Excel.
- Is the process consistent and predictable?
- How long is the current process vs the anticipated timing after Excel Automation?
- Are there upstream or downstream processes that should be included with the automation? Can any of these processes be anticipated to change, or be removed, due to better timing, accuracy, etc.?
- 2Create a solution plan: determining the process flow and how the Excel Automation is to be built.
- 3Build the Excel Automation in a development environment.
- 4Have the Subject Matter Expert, and any other users, test the Excel Automation in the Development environment.
- 5After all testing is confirmed and approved, setup the Excel Automation to be used in a production environment.
How Can Excel Automation Help Your Business?
The benefits of Excel Automation are very similar to the benefits of RPA
- Excel Automation can be very flexible and built to match your business processes.
- Excel Dashboards can be created to help your employees follow specific workflows and complete tasks in the correct order.
- Each workflow item can be a customized automation.
- Employee interaction with the solution can be built into the workflow.
- Excel automation can execute Robotic Process Automation (RPA) robots. In turn, RPA robots can execute Excel Automation.
- The combination of Excel Automation with RPA robots allows full end-to-end automation solutions to be built with necessary employee interaction.
- Excel can easily be integrated with SQL, web data via APIs, or any other data source.
- Significant Time Savings – hours of work can be performed in minutes, or even seconds.
- Accuracy – calculations and performance are consistent and correct. This can remove downstream work related to handling of errors.
- More productive and cost-efficient employees.
- Excel Automation allows analysts to be analysts and managers to be managers.
- If you remove tasks that normally take employees hours to perform, you can remove the need for excessive overtime.
- Repetitive task related jobs can also lead to high employee turnover.
Case Study: Excel Automation in Action
SCENARIO
An oil company had two employees focused on creating a consolidated forecasting report every month in Microsoft Excel. The report included combining multiple reports from approximately 20 subsidiaries, which were also in Microsoft Excel.
Management wanted the report in a certain format, so it required these two employees to use a copy-paste process to combine the reports. This process required 70 to 80 hours of work for each employee during the week of preparation. Both employees are MBAs. They did not have time to provide analysis by the time the report was prepared for the management meeting.
SOLUTION
Our company analyzed all the Microsoft Excel spreadsheets and modified them to creating a mapping process, from each subsidiary Excel file to the consolidated Excel file. It took some time, because the forecasting spreadsheets were not small.
We developed VBA code to populate the subsidiary data into the consolidated file. We built an Excel dashboard in the consolidated file to make the workflow easy to understand. The user would press a button and was prompted for the location of where the subsidiary files were saved. They were instructed to save all subsidiary files in the same folder.
After the user selected the folder, the VBA code would open each file and populate the appropriate area of the consolidated file. The Excel automation performed this same process for all subsidiary files in an aggregated time of approximately one minute. The two MBAs were blown away. Now they had the time to focus on analysis instead of using all their time to create the consolidated forecasting report in Excel.