Excel automation removes the manual steps between raw data and a finished report: importing, cleaning, recalculating, checking and sending. Most of it is already built into Microsoft 365. The tricks below cover both sides of the job, the ones that make the workbook itself repeatable and the ones that let the report run on a schedule with nobody opening the file. The second side is where most Excel automation stops short.
Table of Contents
What can you actually automate in Excel?
Excel automation covers four jobs: importing and cleaning data, recalculating results, running the workbook’s routine steps and moving files and messages around the reporting cycle. Microsoft 365 has a tool for each. The first three run inside the workbook. The fourth runs outside it and that is the one most reporting never reaches.
Inside the workbook, Power Query handles the import and the cleanup, named tables and dynamic arrays handle the recalculation and Office Scripts handle the routine button-press steps. Outside it, Power Automate handles the schedule, the file movement and the notifications. Our guide to Excel for business covers where each one sits and the Excel data analysis guide goes deep on Power Query.
The Australian Bureau of Statistics reported in its Characteristics of Australian Business release for 2024-25, published 25 June 2026, that 10 per cent of businesses actively collected or analysed data to make informed decisions, down from 24 per cent in 2021-22. That figure counts whether the analysis happens at all, not what stops it. Our own answer to that second question is narrow. Reporting that needs a person to open a file and press refresh eventually stops being opened. Automating the workbook is only part of the answer and the rest belongs to business process automation rather than to Excel.
What is an Office Script and how is it different from a macro?
An Office Script is TypeScript that runs against a workbook through the Excel service and is saved to OneDrive or a SharePoint library rather than inside the file. A VBA macro lives inside a macro-enabled workbook or an add-in on the machine and needs Excel itself running. That difference decides what can run without you.
Both can be recorded or written by hand. The split is where they live and what has to be running. A macro needs a copy of Excel open on a machine. A script is stored separately and runs against the file in the service, so a scheduled flow can call it with nobody logged in. Microsoft’s comparison of Office Scripts and VBA lists Scripts as available in Excel on the web, Excel for Windows and Excel for Mac.
Office Scripts are not free of requirements. Microsoft’s guidance on running Office Scripts with Power Automate states that a business licence of Microsoft 365 is required, and Microsoft’s Office Scripts admin guidance describes a switch controlling whether users can run scripts inside Power Automate at all, notes it can be scoped to a single group and says changes can take up to 48 hours to apply.
Can Power Automate run an Excel macro?
Not in a cloud flow. Microsoft’s documentation on macro-enabled files in Power Automate, updated 14 May 2024, states that “the macros contained in the .xlsm files cannot be run through Power Automate” and that “only Office Scripts are enabled there”. A cloud flow can read and write the workbook. It cannot trigger the VBA inside it.
That matters more than it sounds. A reporting routine built on a macro button works perfectly on the person’s desktop and cannot be scheduled from a cloud flow. The moment it has to run overnight or run when someone is on leave, the choice is to rewrite it as an Office Script or to keep a machine running Excel and drive it with a desktop flow or the machine’s own scheduler.
There is one exception and it is not really an escape. Power Automate desktop has a Run Excel macro action, documented in the Excel actions reference updated 11 December 2025, which “runs a specified macro on the document of an Excel instance”. It needs an Excel instance created by a Launch Excel action, which means a real machine with Excel installed and running. You have not removed the machine. You have moved it into a flow.
10 Excel automation tricks that save time in reporting
The ten tricks below fall into two groups. The first six make the workbook repeatable: cleanup in Power Query, named tables, dynamic arrays, Office Scripts, a control table for parameters and exception checks. The last four take the running of the report out of the workbook: the schedule, the file handling, the delivery and the run log.
Do them in that order, because automating an unstructured workbook only makes the breakage faster.
- Put the cleanup in Power Query, not in the sheet. Helper columns and manual find-and-replace do not survive next month’s file. A query does and it reruns on refresh.
- Convert every source range to a named table and keep the calculated output outside it. Structured references keep working as rows arrive and cell addresses do not. Microsoft’s note on the table-formula spill error states that spilled array formulas are not supported in Excel tables, so the dynamic array layer in trick 3 sits beside the table, not inside it.
- Replace the formula rebuild with dynamic arrays. FILTER, UNIQUE and SORT expand on their own, so nobody has to drag formulas down when the data grows.
- Split the one-click routine and move only the in-workbook steps to an Office Script. A recorded month-end macro usually does three jobs at once: it changes the workbook, it makes a file and it sends something. Only the first becomes a script, because Microsoft states that Office Scripts “only have access to the workbook, not the machine hosting the workbook”. The file and delivery steps become flow actions.
- Put the report parameters in a control table, not inside the formulas. Month, region and threshold live in one visible place, so a flow or a person can change them without opening a formula. If a flow sets the month, take it from a local-time expression, because Microsoft records that Power Automate always uses UTC while an Excel session uses the opener’s local time zone.
- Add exception checks that fail loudly. A cell that turns red when a join drops rows is worth more than a dashboard that quietly reports the wrong total. Our guide to data validation covers the input side of this.
- Run the refresh on a schedule, not on a person. A scheduled flow is the difference between a report that is current and one that is current when someone remembers. How the flow does it matters, because a script that calls a refresh method can report success without refreshing anything.
- Let the flow do the file handling. Naming, versioning and moving the finished pack into the right library. Doing the same with a macro means keeping a machine running to do it.
- Deliver the pack to where people already look. A message in the Teams channel with the link beats a file sitting in a folder nobody opens.
- Log every run so you know when it stopped working. A scheduled automation that silently fails is worse than a manual process, because nobody notices for a month. Write the run result somewhere a human sees it.
The tools behind those ten are not interchangeable and one difference decides what you can schedule. This table sets out which of them run with nobody there and what stops each one.
| Tool | Runs unattended | What stops it |
|---|---|---|
| Power Query | Only if something else triggers the refresh | Source connectors not supported in the browser |
| Dynamic array formulas | Only when something else recalculates the workbook | Blocked spill ranges and versions before Microsoft 365 or Excel 2021 |
| Office Scripts | Yes, called by a flow | Licence requirements, the tenant setting and unsupported APIs |
| VBA macros | Only on a machine you keep running | Needs a running copy of Excel |
| Power Automate | Yes | Connector limits and file locks |
How do you refresh an Excel report without anyone opening the file?
Two routes work without a person: a flow that writes the data straight into the table, or query logic moved to a dataflow or Power BI semantic model with Excel connected to the result. A third keeps a machine with Excel on it. The obvious route, a scheduled flow calling a script, fails silently on ordinary Power Query sources.
Microsoft’s troubleshooting guidance for Office Scripts in Power Automate, updated 7 December 2025, states that Workbook.refreshAllDataConnections “only refreshes PowerBI sources” and that “for other sources, the method returns successfully but does nothing”.
The same page lists PivotTable.refresh, Workbook.refreshAllPivotTables and Worksheet.refreshAllPivotTables among the methods that do nothing when called from a flow. So the standard recipe, a scheduled flow calling a script that refreshes the queries and the pivots, does not do what it is widely described as doing.
Choosing between the two cloud routes is a design decision rather than a trick.
- Move the source, not the refresh. Have the flow write the new data straight into the workbook’s table through the Excel Online (Business) connector, so there is no query to refresh. The workbook becomes a presentation layer over data the flow maintains. A PivotTable built on that table keeps its cached totals and no cloud flow can refresh it, so tick Refresh data when opening the file in PivotTable Options. Write in bulk rather than row by row, because the connector throttles and has no bulk delete, so pass the whole set of rows into one Run script call and clear last month’s rows in the same script. Keep the aggregation light, because Microsoft records that heavy formulas or too many rows can time out the connector on recalculation and that the retry can insert the same data twice.
- Move the refresh out of Excel. Put the query logic in a dataflow or a Power BI semantic model, which have real scheduled refresh and leave Excel connected to the result. Two conditions catch people out. Microsoft’s dataflow licensing guidance states that creating a dataflow in a Power BI workspace needs a paid Power BI Pro licence, that a free licence will not do it and that a Power Apps plan is required to create one in a Power Platform environment. The second bites whenever a query reads something not already in the cloud, because Microsoft states that a dataflow requires the on-premises data gateway to reach an on-premises source, naming Excel files in a shared folder and an on-premises SQL Server as its examples. Be clear what updates the file, too. Either service runs on its own schedule while the workbook holds cached values, so a dataflow refreshes the data but not the Excel file. A Power BI semantic model is the one case where a scheduled script does work.
The third route is a build rather than a setting. A machine with Excel on it can refresh the workbook the way a person would, driven by a desktop flow or by the machine’s own scheduler, but read the conditions first. Microsoft’s guidance on unattended desktop flows records that they need an available machine with all users signed out and the Power Automate Process plan, and Microsoft’s guidance on unattended automation of Office states that it does not support automating Office from an unattended, non-interactive component, and that the licensed RPA configuration is strictly as is. Like the other two it needs its own check that the data actually moved.
None of the three is worth building on a workbook that is still four workbooks stitched together. We rebuilt reporting for a social services provider in Victoria by fixing that first. Four locations fed a central workbook through direct workbook links, reconciled by hand. The multi-site reporting case study records the outcome: one refresh rebuilds all reporting through Power Query, with zero fragile workbook links left to break. Linked workbooks put any schedule at the mercy of those file paths and Microsoft records that Power Automate does not trigger a data refresh for formulas that use workbook links at all.
What are the limits of automating Excel with Power Automate?
Four connector limits decide most Excel automation designs: a 25 MB file ceiling, 256 rows returned by default, no pivot table support and no concurrent modification. Microsoft’s connector reference, updated 11 July 2026, sets out these and more. They bite at exactly the scale a reporting workbook reaches, so read them before you build.
The table below sets out all eight and what each one costs you.
| Limit | Value | Why it matters for reporting |
|---|---|---|
| Maximum file size | 25 MB | A consolidated reporting workbook passes this sooner than you expect |
| Pivot tables | Not supported, due to Graph API limitations | Connector actions cannot reach them, and a script called from a flow can build a pivot but not refresh one |
| Rows returned by List rows present in a table | Up to 256 by default, pagination must be turned on | Silent truncation that the flow reports as success |
| File lock after use | Up to 6 minutes | A later connector update or delete on the same file can fail inside that window |
| Concurrent modification | Not supported | One writer at a time, across Excel desktop, Excel web, Power Automate, Logic Apps and Power Apps |
| Write access | Required for every action, including reads | A file or library you can only read returns 403, and a read-only workbook returns 502 |
| Connector throttling | 100 calls per 60 seconds per connection | Row-by-row loops hit this fast |
| Run script calls | 3 per 10 seconds and 1,600 per day | The per-day figure is per user, resetting at midnight UTC |
Office Scripts carry their own ceilings on top of the connector’s. Microsoft’s platform limits page for Office Scripts, updated 24 October 2024, records a 120-second timeout on synchronous Power Automate operations, requests and responses capped at 5 MB and a range limited to five million cells.
Two of the connector limits are cheaper to design for than to discover. Pagination should be on before the first live run, because until it is the flow reports success on a truncated dataset. And a workbook should not be both the automation target and the thing people have open, which is the usual cause of a flow that works in testing and fails in the office. Designing for the limits is the same discipline covered in what a Power Automate flow actually is.
What breaks when you automate a spreadsheet and how do you stop it?
Automated Excel reporting breaks in predictable ways: a connection expires, a refresh reports success and does nothing, the structure moves, the source changes shape, two writers collide or a limit truncates the data. Some error loudly and some do not. You stop the quiet ones by comparing a known figure in the output against its source on every run.
Work through these in order when a scheduled Excel automation stops producing the right answer.
- Check the run actually ran and check it soon. Look at the flow run history before anything else, because a disabled flow and a failing flow look identical from the workbook. Microsoft’s run history guidance states that flow run data is stored for 28 days by default, so on a monthly report the failed run may already be gone and an empty history is not proof it never ran.
- Check the connection, not just the flow. A scheduled flow still runs as a person. A password change, a re-authentication prompt, a reassigned licence or a departure breaks the connection. Microsoft’s limits and configuration guidance records that a cloud flow whose trigger or actions fail continuously is turned off after 14 days.
- Confirm the row count, in both directions. On a read, compare the rows the flow returned against the rows in the file. A count that stops at 256 is the pagination default, and any other short count is usually a filter, because Microsoft records that a filter set in the action stacks on top of any filter already saved in the workbook. On a write, more rows in the file than the flow sent means last month’s rows were never cleared, or a connector retry after a recalculation timeout landed the same rows twice.
- Check whether anything actually recalculated. A script that calls a refresh method and returns success has not necessarily refreshed anything, so compare a known value in the output against the source. Check the calculation mode in the same pass, because a workbook set to manual holds last month’s results above the flow’s new rows without erroring.
- Look for a second writer. Someone with the file open or a second flow will produce intermittent failures that never reproduce on demand.
- Check the structure and the source shape, not the values. A table converted back to a range turns its structured references into fixed addresses that stop expanding, and a deleted column leaves #REF! behind. A column renamed inside the workbook is the quiet failure, because Excel rewrites your own formulas for you while the flow keeps asking for the old name and gets nothing back. A column renamed in the source is the loud one, hitting a step-level error that fails the refresh outright. A changed date format is quieter again, because Microsoft’s guidance on Power Query errors states that a cell-level error does not prevent the query loading and shows the failed cells as Error.
The prevention is duller than the diagnosis and worth more. Comparing a known value against its source is the only check in the list that catches a refresh which reported success and did nothing. Getting the workbook structured so it survives all this is a separate discipline, covered in spreadsheets people actually want to use.
When should the reporting leave the workbook entirely?
When the automation around the workbook costs more to maintain than the reporting is worth. The signals are specific: you are working around the Excel connector’s limits, the file is approaching its 25 MB ceiling, more than one person needs to write to it or a machine runs only to refresh it. Excel is the interface, not the system.
Excel stays the right answer where one team owns the report, changes it often and understands it completely. It stops being the right answer when the workbook has quietly become the database, the integration layer and the report at once.
The timing question is live for a lot of mid-sized businesses. MYOB’s Autonomous Business Report 2026 found that three quarters of decision-makers across Australia and New Zealand plan to change or upgrade their ERP in the next two years. The survey covered more than 1,000 of them at businesses with 20 to 500 full-time employees and revenue above MYOB’s stated $5 million floor, 518 of them in Australia, with fieldwork between 13 February and 16 March 2026. Every one of those upgrades raises the same question about what stays in a spreadsheet, and the answer is easier to give before the migration than during it.
The move is usually smaller than people expect. The query logic goes to a dataflow or a semantic model. The presentation goes to Power BI or stays in Excel connected to the result, and the scheduling goes to business automation. The workbook does not disappear. It stops being load-bearing.
Frequently asked questions
Do you need a licence to use Office Scripts with Power Automate?
Yes. Microsoft’s guidance on running Office Scripts with Power Automate states that a business licence of Microsoft 365 is required. It also notes that Office 365 Enterprise E1 and Office 365 F3 can use Scripts with Power Automate but do not get the integration inside Excel itself. Confirm what your team holds before designing around scripts.
Can Power Automate refresh a Power Query connection in Excel?
Not reliably. Microsoft’s troubleshooting guidance states that Workbook.refreshAllDataConnections only refreshes Power BI sources and that for other sources the method returns successfully but does nothing. The flow reports success either way. For a scheduled refresh that holds up, write the data into the table directly, move the query logic out of Excel or have a desktop flow run a refresh macro on a machine with Excel installed.
Why does my flow only return 256 rows from an Excel table?
That is the default. The Excel Online (Business) connector returns up to 256 rows from List rows present in a table unless pagination is turned on in the action settings. Nothing errors, so the flow reports success on a truncated dataset. Turn pagination on and set a threshold above your expected row count.
Can a flow refresh a pivot table?
Not from a cloud flow. Microsoft lists PivotTable.refresh, Workbook.refreshAllPivotTables and Worksheet.refreshAllPivotTables among the script methods that do nothing when run through Power Automate, and the connector reference separately records that pivot tables are not supported. Ticking Refresh data when opening the file in PivotTable Options rebuilds the cache for whoever opens the pack.
How do you know when a scheduled Excel automation has stopped working?
You do not, unless you build the check in yourself. Flow run history shows failures but not silent no-ops, so add an explicit check: have the flow compare a known figure against the source, or write the run time and row count into a one-row log table. Put a wait before the read, because connector writes can take up to 30 seconds to take effect after the action reports success.
Where to start
Pick the report that costs the most time each month and ask two questions about it. What has to happen inside the workbook, and what has to happen around it. The tricks that answer the second question only pay off once the workbook underneath can survive being automated.
If the report is already consolidated, already large and already being refreshed by hand, the honest next step is a review of the workbook before you automate anything else. Ask us for one through our spreadsheet solutions service.
