Excel automation: the methods, from formulas and macros to connected workflows
Short answer
Excel automation runs from built-in features (tables, forms, Power Query), through recorded macros and Office Scripts, to Power Automate flows and connected workflow tools. Pick the lowest rung that does the job. Microsoft’s Excel Online connector works on tables in OneDrive or SharePoint files up to 25 MB, so a spreadsheet that outgrows those limits may belong in a database or accounting system.
On this page
The Excel automation ladder
- 1
- Built-in features: tables, formulas, the data form, Power Query
- 2
- Macros: record steps, edit as VBA code
- 3
- Office Scripts: record or write scripts, run them on a button or from a flow
- 4
- Power Automate with Excel: flows that read and write tables
- 5
- Connected workflow tools: Excel as one app in a larger workflow
- Sources
- Microsoft Learn and Microsoft Support, read 12 Oct 2026
- Testing
- Documentation-based. The examples are illustrations, not tested runs.
Start with the lowest rung
Pick the rung by the job. A task done by one person, on one file, on a desk needs rung 1 or 2. A task that must run when the person is away, or touch email and other apps, needs rung 4 or 5. The examples below are illustrations written for this page.
Rung 1: built-in features
Tables and formulas. Convert a range to a table so each new row extends formulas and ranges. Many other rungs need a table, so this is the first step anyway.
The data entry form. Excel has a built-in form that lists each column header with a text box. Microsoft’s support article says you add the Form button to the Quick Access Toolbar, because it is not on the ribbon by default, and that the form supports up to 32 columns. Use it when several people enter rows into one table and a form is easier than the grid. Searches for an “automated data entry form in Excel” mostly mean this.
Power Query. Microsoft describes Power Query as “a data transformation and data preparation engine”. It records your steps, such as removing a column or filtering rows, as a repeatable query that you can refresh to get up-to-date data. Excel for Windows and Excel for Mac include Power Query Desktop. Microsoft says queries refresh manually, by scheduled refresh in specific products such as Power BI, or programmatically through the Excel object model.
Illustration: each Monday you paste a CSV export from a store and clean the same five columns. A Power Query that cleans the export turns a ten-step routine into one Refresh click.
Rung 2: macros
Microsoft defines a macro as “an action or a set of actions that you can run as many times as you want.” To record one, show the Developer tab, which is hidden by default, choose Record Macro in the Code group, do the steps, then stop recording. You can open the recorded macro in the Visual Basic Editor and see the actions as code.
Illustration: a recorded macro that formats a monthly report, with the same header, column widths and totals row each time.
Macros run on the desktop app on one machine. A macro from someone else is code that runs on your computer, so only run macros from sources you trust. Microsoft’s quick-start page did not cover macro security, so read Microsoft’s guidance on macro settings before you enable them.
Rung 3: Office Scripts
Microsoft’s documentation says Office Scripts “let you automate your day-to-day tasks”. The action recorder turns manual steps into reusable scripts, and a code editor lets you edit them or write new ones, with conditional statements and loops. Scripts can be shared with coworkers who have access to the workbook, and a script button lets anyone run one from the sheet.
Microsoft’s page says scheduling inside Office Scripts is “temporarily disabled”, that existing scheduled scripts continue to run, and that for new schedules you should use a Power Automate flow. Check Microsoft’s current licence and platform requirements for Office Scripts before you plan around them. They were not part of the page read.
Illustration: a script that, each morning, deletes unneeded columns from a downloaded CSV, formats a table, adds formulas and builds a PivotTable, which is the example Microsoft’s own page uses.
Rung 4: Power Automate with Excel
Power Automate is Microsoft’s flow service, and the Excel Online (Business) connector lets a flow work with Excel files. It is a standard connector. Microsoft’s connector page lists actions such as add a row into a table, update a row, delete a row, list rows present in a table, create a table, create a worksheet and run script. The Run script action is how a flow starts an Office Script.
The limits are specific, and they decide whether this rung fits:
| Limit stated by Microsoft | Value |
|---|---|
| Files must live in | OneDrive for Business, SharePoint sites or Office 365 Groups |
| File formats | .xlsx and .xlsb (Run script also supports .xlsm) |
| Maximum file size | 25 MB |
| Rows returned by “List rows present in a table” by default | 256, until you turn on pagination |
| API calls per connection | 100 per 60 seconds |
| Run script calls | Up to 3 per 10 seconds and 1,600 per day |
| Pivot tables | Not supported |
| Writing from several places at once | Not supported, which can cause data conflicts |
Microsoft also says that changes from Add, Update or Delete a row operations may take up to 30 seconds to show.
Illustration: a flow that starts when a form is submitted, adds a row to a table in a SharePoint workbook, and emails the person who owns the account.
“Excel to web” searches usually mean typing values from a sheet into a web page that has no connection to read them. That is the kind of job robotic process automation targets. See RPA vs AI automation.
Rung 5: connected workflow tools
Tools such as Zapier, n8n and Make treat Excel as one app among many. They suit jobs where the work starts or ends outside Microsoft: a payment record becomes a row, a new customer becomes a task, a sheet row becomes a message. Whether each has an Excel connector for your Microsoft plan, and its limits, is on that tool’s own pages. Look it up before you commit.
Illustration: a payment confirmation from a gateway adds a row to a sales sheet. See GCash for business for how payment records reach a sheet, and the workflow guide for describing the process first.
Copilot in Microsoft 365 can also help write formulas and summarize data. See Microsoft Copilot for Philippine business. Check an AI-written formula on a few rows before you rely on it.
When a spreadsheet has outgrown itself
Signs to watch for, the first two drawn from the connector limits above:
- The file nears 25 MB, or flows keep hitting rate limits.
- Many people write to one file at the same time.
- The sheet holds records that other systems also need, such as customers or invoices.
- You rebuild the same lookup in several files.
Then a system built for the records is the next step. An accounting tool such as QuickBooks lists data sync with Excel, and an ERP such as Odoo keeps sales, stock and accounting in one database. The decision depends on the process, so describe it before you shop.
How to choose
Describe the job
Write who does it, how often, what starts it and what the result is.
Check the data
Is it in a table? Is the file in OneDrive or SharePoint? Is it near 25 MB?
Try rung 1 or 2
If one person runs it on one machine, a feature or a recorded macro may be enough.
Move up if it must run alone
If it must run when nobody is at the desk, use an Office Script with a flow, or a workflow tool.
Add a check
Have a person sample the output for the first runs and keep a copy of the original file.
Personal data in a workbook is covered by the Data Privacy Act of 2012. Keep customer lists in files with controlled access, as the pillar guide describes.
Frequently asked questions
What is the best way to automate Excel?
Use the lowest rung that does the job. A table or Power Query suffices for a repeated cleanup, a recorded macro for a desktop routine, and Office Scripts with Power Automate for work that must run alone.
How do I automate Excel reports?
Load the source with Power Query so it refreshes, build the report from the query, and record a macro or script for formatting. Add a flow if it must be sent on a schedule.
Can Power Automate work with Excel?
Yes. The Excel Online (Business) connector works with tables in files stored in OneDrive for Business or SharePoint, with limits such as a 25 MB file size.
Can Excel send automated emails?
A Power Automate flow can read rows from a table and send an email. A workflow tool with an Excel connector can too. Check each tool’s limits.
What is an Office Script?
A script recorded or written in Excel that automates tasks and can be run from a button or from a Power Automate flow.
How do I automate data entry from Excel to a website?
Check whether the website has an import or an API first. If not, robotic process automation can type values into it. See RPA vs AI automation.
Sources
All sources were read on 12 Oct 2026.
- Microsoft Learn, Office Scripts in Excel.
- Microsoft Learn, Excel Online (Business) connector: actions, file limits, throttling.
- Microsoft Support, Quick start: Create a macro.
- Microsoft Learn, What is Power Query?.
- Microsoft Support, Add, edit, find, and delete rows by using a data form.
- Intuit QuickBooks Philippines, Features: data sync with Excel.