Don’t forget to share this post

It’s undeniable that Excel is among the best solutions for businesses when it comes to storing, managing, and visualizing data for better decision-making. The release of Microsoft Office 365 increased the offer of automation tools, making all tasks related to data treatment faster and easier to achieve. If you work with large datasets or need to create inventories from scratch, it’s crucial that you learn how Excel automation works.

In this article, you’ll learn how to automate tasks in Excel spreadsheets using automation tools within your spreadsheet. This will include VBA Macros as well as integrated applications, such as Power Automate. Alternatively, Layer can be used as an automation tool that works on top of your spreadsheets, so you can boost any business process, all from a single platform.

Supermetrics Excel Banners 800x800 27
Automate Marketing Reporting!

Get data from your marketing platforms into your Excel sheet in minutes.

GET STARTED FOR FREE

What can you use Excel Automation for?

Automating Excel spreadsheets is not only easy to learn but will have a significant impact on your team’s productivity level. Let’s say you need to update your inventory or your sales commission figures regularly. These processes are just some of the many you can automate in Excel. Following are some examples of the most common tasks you can automate in Excel.

Excel Automation examples

  • High-volume data input
  • Data entry forms creation
  • New files creation
  • Data format and style modifications
  • Refresh data
  • External data source connections

You’ll now learn how to automate two very common spreadsheet tasks in Excel; refreshing data and external data source connections.

How to automate Excel spreadsheets?

The first example illustrates how to refresh data via a macro. This is especially useful in case your day-to-day work involves high-volume data that needs to be refreshed regularly. This is how you can automate updating or refreshing data in Excel.

How to automate Excel spreadsheets using VBA Macros?

Before automating any task, ensure you enable direct access to VBA in your Ribbon toolbar. If it’s not currently there, you’ll need to enable the “Developer” tab to access the VBA and Macros features.

Add Developer tab, including VBA and Macros features

  1. 1. Click on the drop-down arrow and select “More Commands…” as shown below.
Excel Automation Easily Automate Spreadsheet Tasks More Commands
Excel Automation: Easily Automate Spreadsheet Tasks - More Commands…
  1. 2. Select “Customize Ribbon” from the left-side menu and then “Main Tabs” from the “Customize the Ribbon” drop-down menu to the right.
Excel Automation Easily Automate Spreadsheet Tasks Customize Ribbon
Excel Automation: Easily Automate Spreadsheet Tasks - Customize Ribbon
  1. 3. Select the “Developer” option and then “OK” to enable it in your toolbar.
Excel Automation Easily Automate Spreadsheet Tasks Developer
Excel Automation: Easily Automate Spreadsheet Tasks - Developer

Now that the “Developer” tab is enabled, you’ll see a basic example of refreshing data automatically using Macros. To make the following steps easier, make sure you save the file as a macro-enabled spreadsheet with the “.xlsm” format.

Power Automate Excel Examples And Use Cases
Power Automate & Excel: Examples And Use Cases

Power Automate allows you to create workflows, e.g. for data collection. Here's how to combine Power Automate with Excel to automate tedious daily tasks.

READ MORE

How to refresh data in Excel using VBA Macros?

Macros are basically user-generated functions that are executed in VBA. It’s usually the case that we need to refresh data automatically to always have access to information in real time. For example, if you use a PivotTable for quick reporting, then you want to make sure that the reports provide the most recent data. This is how you can create a macro to refresh the information ‌in your PivotTable.

  1. 1. Go to your new “Developer” tab and select “Record Macro”.
Excel Automation Easily Automate Spreadsheet Tasks Record Macro
Excel Automation: Easily Automate Spreadsheet Tasks - Record Macro
  1. 2. Enter the name of the Macro. Here, I’ll use "RefreshChartA". Click “OK” to continue.
Excel Automation Easily Automate Spreadsheet Tasks Name macro
Excel Automation: Easily Automate Spreadsheet Tasks - Name macro
  1. 3. Click on the “VBA” button from your “Developer” tab and adapt the following code according to your sheet and macro names:
Private Sub Sheet1(data)
Call RefreshChartA
End Sub
Sub RefreshChartA()
ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
End Sub
Excel Automation Easily Automate Spreadsheet Tasks Enter Refresh Code
Excel Automation: Easily Automate Spreadsheet Tasks - Enter Refresh Code
  1. 4. Click on the “Play” green icon in VBA and then select the macro you just created. Then, click “Run” to execute the macro.
Excel Automation Easily Automate Spreadsheet Tasks Run macro
Excel Automation: Easily Automate Spreadsheet Tasks - Run macro
  1. 5. To check that it works, apply any change to the PivotTable to see how it reflects in the sheet containing the Chart. For example, here, I’ll remove the last row.
Excel Automation Easily Automate Spreadsheet Tasks Edit Pivot Table
Excel Automation: Easily Automate Spreadsheet Tasks - Edit PivotTable
  1. 6. Head over to the sheet containing the chart, and it should automatically reflect the change made.
Excel Automation Easily Automate Spreadsheet Tasks Refreshed Chart
Excel Automation: Easily Automate Spreadsheet Tasks - Refreshed Chart

Macros can be used for many spreadsheet purposes, from automated updates to synching data. However, there are other Excel automation methods that work without Macros, such as creating “Flows” in Power Automate.

How To Use Macros In Excel To Automate Tasks?

Macros are a powerful tool that can be used to automate tedious tasks in Excel. Here's how to use Macros in Excel.

READ MORE
How To Use Macros In Excel To Automate Tasks

How to automate Excel spreadsheets using Power Automate?

Power Automate is a platform where you can create all types of workflows to automate connections and specific data transfers between Microsoft applications. The great thing is you don’t need to use a single line of code and can set up your workflow from scratch or use among the many templates made available on the platform.

Here, you’ll see how to set up a template to create a row in another spreadsheet based on a specific row.

  1. 1. Launch Power Automate and click on “Templates”.
Excel Automation Easily Automate Spreadsheet Tasks Templates
Excel Automation: Easily Automate Spreadsheet Tasks - Templates
  1. 2. Enter “Create a row in another spreadsheet for a selected row” to automate synchronization tasks between two spreadsheets. Click “Continue” to edit your template.
Excel Automation Easily Automate Spreadsheet Tasks Continue
Excel Automation: Easily Automate Spreadsheet Tasks - Continue
  1. 3. You’ll see two boxes corresponding to each dataset that you’ll synchronize. You simply need to click on the field and choose the file location from the drop-down menu.
Excel Automation Easily Automate Spreadsheet Tasks Edit template
Excel Automation: Easily Automate Spreadsheet Tasks - Edit template
  1. 4. The second box contains the information related to the table that will be updated, so you’ll need to provide further information on the type of data to add. As soon as you click on the fields, you’ll be prompted to enter the values from the available options to the right. Click “Save” to finish.
Excel Automation Easily Automate Spreadsheet Tasks Dynamic content
Excel Automation: Easily Automate Spreadsheet Tasks - Dynamic content

Once you save your created flow, it’s important to test it to ensure that it works correctly. You can do this by clicking on the “Test” button in the top-right corner. Alternatively, Layer presents many options to automate your business processes, all without having to leave the same platform.

Experience Confluence: The Ultimate Team Workspace

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

  • Centralized Knowledge: Access your team’s collective wisdom with ease.
  • Collaborative Workspace: Foster engagement with flexible project tools.
  • Seamless Communication: Connect your entire organization effortlessly.
  • Preserve Ideas: Capture insights without losing them in chats or notifications.
  • Comprehensive Platform: Manage all content in one organized location.
  • Open Teamwork: Empower employees to contribute, share, and grow.
  • Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

Any spreadsheet user knows how many small but time-consuming tasks are necessary when working with databases. These not only take up most of your time, but they also absorb your focus from other more critical tasks. Automation tools such as VBA Macros or Power Automate save you from having to perform manual tasks related to data treatment and allow you to concentrate on data analysis.

You should now know how to automate tasks in Excel spreadsheets using VBA Macros or Power Automate. From updating information within a single workbook to synchronizing data between two spreadsheets, Microsoft Office 365 has become a powerful automation tool. Alternatively, Layer also works on top of your spreadsheets, so you can also perform any automation action without the need to learn code and in just a few clicks.

Supermetrics Excel Banners 800x800 27
Automate Marketing Reporting!

Get data from your marketing platforms into your Excel sheet in minutes.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 7 2022, Updated Mar 22 2023