There are countless solutions available today to automate tasks in Excel. When you search Google, the topic returns over 65,000,000 results. Excel automation solutions come in all shapes and sizes and come in many different price points.
In this article, we explore free ways for automating Excel processes. Please read on to learn which solutions are available prior to spending tens or hundreds of thousands of dollars on expensive enterprise grade solutions that may be completely unnecessary.
Automate Tasks with Excel Get and Transform
Get and Transform is one of the best and most overlooked features for new and experienced Excel Users. Get and Transform was previously known as Power Query, released in 2013 it is constantly evolved over the years to become increasingly helpful. It is also the same engine behind Power BI, Microsoft’s premier Business Intelligence and AI Insights platform.
This section of Microsoft Excel allows you to quickly do several important tasks:
- Connect to Live Data, such as SQL data warehouses and Accounting Software
- Automate Repetitive Data Cleaning and Preparation
- Combine Data from Multiple Sources
- Consolidate Folders of Excel Files or other File Types
- Connect to data from Power BI data sources, One Drive and SharePoint Folders
Get and Transform is available in Excel for Desktop under Data > Get Data
Automate Recurring Tasks with Power Automate Flows
While not technically free, Power Automate Flows are available in most Microsoft 365 enterprise licenses. Many companies already pay for the licensing to use Microsoft’s advanced Robotic Process Automation (RPA) platform, but few people utilize it to its full potential.
Power Automate Flows allow you to connect cloud services and perform a series of steps after an action is triggered.
Here are some example use cases:
- Automatically save a Financial Report to One Drive that’s sent to your e-mail monthly. Power Automate recognizes that you received an e-mail based on the subject line and who it’s from. Then saves it to One Drive or SharePoint. You can then use Excel Get and Transform to consolidate the report with others saved in the folder.
- Schedule Power Automate to Automatically Refresh a data tab in Microsoft Excel. Power Automate Flows can be setup to run at specific times of day. Setup a Power Automate Flow to update a Data tab in your Excel workbook. Then use lookups and sumifs to pull the data tab into formatted reports in other tabs.
- Use Power Automate Flows can take data from Excel and export it to other data sources, like sending it back to a database or use it to trigger an update to a Power BI Dashboard.
Automate Complex Tasks with Power Automate Desktop
Power Automate Desktop is a desktop RPA tool provided by Microsoft. It’s free to download and use on your computer. There is a cost associated with being able to schedule workflows to run autonomously, but the desktop version is incredibly. powerful.
While Power Automate Flows are used to automate tasks that occur between different cloud services, Power Automate Desktop lets you record a process that occurs on your desktop.
Here are some example use cases:
- Automate Data Entry and Processing
- Convert PDF Files to Excel Spreadsheets or Formatted Text
- Invoice Processing Automation
- Consolidating Multiple File Types into a Formatted Excel File
The use cases are almost endless for RPA. The technology interacts with a computer in a similar way that people interact with computers. Meaning that if you can do it, a robot can be setup to do it for you.
Some of these tasks do require considerable setup and can be time consuming to account for potential variances in a process. However, Microsoft does publish several Power Automate Desktop Templates specifically for Excel users to help people get started.
Using Chat GPT to Program Python, Macros, and VBA
We are not the biggest fans of using Python to automate tasks in Excel. Mainly because it can be very complicated for non-programmers to setup simple automations. However, with new tools available such as Chat GPT computer programming is much more approachable than it used to be. Chat GPT is a natural language chat bot that’s been trained on a vast body of knowledge, including computer programming.
This is a screenshot from Chat GPT, describing a task and the computer is generating the code necessary to automate an Excel spreadsheet.
There’s a decent level of setup involved in being able to run and automate Python, but you can have Chat GPT walk you through the setup process and even help troubleshoot code if you get stuck.
Utilize other Free and Open-Source RPA Solutions
Hammering out code may not be for everybody. Within the Robotic Process Automation (RPA) world, there are a number of free solutions that will generate Python code on your behalf with the use of a script editor. We like the Power Automate platform, but it’s not fully free. Check out these free and open-source solutions:
Best Open-Source Robotic Process Automation (RPA) Tools (www.popautomation.com)
Prepare Data in Power BI to Export to Excel
Our final way to automate Excel is to utilize Power BI. These two tools are related but different in the ways that they work. Power BI Desktop can be downloaded from Microsoft for free. If you want to automate data refreshes directly in the system, you do have to pay $10 per user per month, but it is a more modern solution than Excel and has many cloud features that Excel does not.
The benefit of this is that you can establish a data model in Power BI, setup an automatic refresh and then connect to the Power BI data source using Excel, or you can export the data from the Power BI Service as a .csv or .xlsx file for further processing.