Excel Automation with Power Automate

Excel is the most widely used tool in businesses — and at the same time one of the most common sources of manual errors and inefficient processes. Spreadsheets emailed back and forth, endless copy-paste sessions, VBA macros that only one employee understands: it doesn’t have to be this way.

In this article, I’ll show you how to modernize and automate Excel-based workflows — using the right tools from the Microsoft 365 ecosystem.

The Problem with Manual Excel Processes

Almost every company knows this situation: an Excel file is emailed between departments, everyone adds something, versions get mixed up, and eventually no one knows which file is current.

Common symptoms:

Multiple versions of the same file with dates in the filename (Report_final_v3_FINAL2.xlsx)
Data is manually copied from another system into Excel — daily or weekly
Reports are manually compiled and sent by email
VBA macros that are fragile and break with every update

Four Approaches to Excel Automation

1. Power Automate + Excel Online

Power Automate offers a native Excel Online (Business) connector that lets you read, add, update, and delete rows directly — without opening the file.

Typical use cases:

Add a new row to Excel when a form is submitted
Update an Excel table daily and send it as an email attachment
Merge data from multiple Excel files into a master file
Use Excel values as a trigger (e.g. when a status changes to “Approved”)

Prerequisite: The Excel file must be stored in SharePoint Online or OneDrive for Business (not locally).

2. Office Scripts — the modern VBA replacement

Office Scripts are JavaScript-based scripts that run directly in Excel Online — similar to VBA, but more modern, stable, and cloud-ready.

Advantages over VBA:

Runs in the browser, no local Excel required
Can be called by Power Automate — a decisive advantage
No macro security issues
TypeScript-based — more maintainable than VBA code

Example: An Office Script automatically formats an Excel table, calculates subtotals, and creates a new summary row — triggered by a Power Automate flow.

3. Power Query — automatic data transformation

Power Query is the most powerful tool for data transformation in Excel and Power BI. Instead of manually cleaning and reshaping data, you define a query once — and Excel refreshes the data at the click of a button or automatically.

What Power Query can do:

Automatically merge CSV and Excel files from a SharePoint folder
Load data from web APIs directly into Excel
Define data cleaning (remove duplicates, rename columns, standardize formats) once and reuse it
Connect to SQL databases, SharePoint lists, or OData feeds

4. Replacing VBA — when and how?

VBA isn’t inherently bad — but it’s often the wrong toolset for modern automation:

Scenario	Recommendation
Macro should be triggered by Power Automate	Office Scripts
Data transformation and cleansing	Power Query
Integration with other M365 services	Power Automate
Complex UI logic in Excel Desktop	VBA (or better: Canvas App)

If you have VBA macros that run daily or on specific events, switching to Office Scripts + Power Automate is usually the right move.

Practical Example: Automated Weekly Report

Here’s a real-world scenario: a sales team maintains customer contacts in an Excel file in SharePoint. Every Friday, a report is manually compiled and emailed to management.

The automated solution:

Every Friday at 5pm, a scheduled Power Automate flow starts
Office Script reads the Excel table, calculates weekly totals, and formats a summary
Power Automate exports the result as a PDF attachment
Email is automatically sent to management

Result: 30 minutes of manual work eliminated every week — that’s over 26 hours per year.

Excel + SharePoint = Real Value

The biggest lever comes when Excel files are stored in SharePoint Online rather than locally. Only then can you:

Have Power Automate flows react to file changes
Run Office Scripts in the cloud
Use version history (SharePoint automatically saves all versions)
Allow multiple people to work in the file simultaneously

Migrating a local Excel file to SharePoint takes just a few minutes in most cases.

Conclusion

Excel isn’t going away — but how you use Excel should evolve. With Power Automate, Office Scripts, and Power Query, you have three powerful tools that turn Excel from a passive spreadsheet into an active part of your process landscape.

Relevant services:

Further Reading:

Office Scripts Documentation — getting started with JavaScript-based Excel automation
Power Query: What is it? — official Microsoft introduction
Excel Online Connector for Power Automate — all available actions and triggers
Power Automate + Excel: Overview — getting started guide

Do you have Excel processes you’d like to automate? Contact me — I’ll show you what’s specifically possible and the fastest path to get there.