Yes—Power Automate can refresh Excel PivotTables by running an Office Script on a workbook stored in OneDrive for Business or SharePoint. There is no standard Power Automate action named “Refresh PivotTable”; the usual solution is to add a script that calls the PivotTable refresh method, then run it from a flow.
What a PivotTable refresh actually does
A PivotTable summarizes data from a source, such as an Excel table, a cell range, or an external connection. Refreshing it tells Excel to read the available source data again and update the summary. For example, if a source table contains monthly sales and a new row is added for April, refreshing the PivotTable can bring April into its totals and categories.
Refreshing the summary is not the same as importing or updating the source data. If a flow writes new records into a workbook table, the PivotTable refresh can then recalculate its summary from that table. But if the workbook’s source is a database that has not been refreshed, refreshing the PivotTable does not necessarily fetch the latest database records. Keeping those two operations distinct is the key to designing a dependable flow.
Power Automate is useful when that refresh should happen on a schedule or after a process completes—for example, after a file is uploaded or after a flow adds rows. It can run without someone opening Excel manually, provided the workbook, script, and data source are compatible with the online workflow.
Why there is no built-in refresh action
The Excel Online (Business) connector offers actions for working with workbook data, including running an Office Script, but it does not provide a dedicated action for refreshing a PivotTable. A flow that only adds or updates rows should not be assumed to refresh summaries automatically. The script bridges that gap by using Excel’s scripting API to refresh PivotTables in the workbook.
This is a cloud-based approach: the workbook must be accessible through a supported business storage location, normally OneDrive for Business or a SharePoint document library. A workbook that exists only on a user’s local computer is not a suitable target for the Excel Online (Business) Run script action. Upload or move it to the appropriate cloud location before building the flow.
Office Scripts availability can depend on the Microsoft 365 account, administrator settings, and the environment where the flow runs. If the Automate tab or Run script action is missing, check that Office Scripts is enabled for the account and that the flow is using the Excel Online (Business) connector.
Prepare the workbook and script
Open the workbook in Excel for the web or a supported Microsoft 365 version of Excel, and confirm that the PivotTable works when refreshed manually. Give the PivotTable a clear name if you plan to target just one. In Excel for Windows, select the PivotTable and use the PivotTable Analyze tab to find its name in the PivotTable Name box. Also confirm that the source range includes the rows the flow will add. An Excel table is generally easier to maintain than a fixed cell range because new rows added to the table become part of its data.
To create a script in Excel for the web, open the Automate tab and choose New Script. Replace the example script with this code to refresh every PivotTable in the workbook, then save it with a descriptive name such as Refresh All PivotTables:
function main(workbook: ExcelScript.Workbook) {
const pivotTables = workbook.getPivotTables();
for (const pivotTable of pivotTables) {
pivotTable.refresh();
}
}
The script gets the workbook’s PivotTables and calls refresh on each one. If the workbook contains no PivotTables, the loop simply has nothing to refresh. If the workbook has several summaries and only one needs updating, you can instead write a script that gets that PivotTable by name and refreshes it. A targeted script avoids refreshing unrelated summaries, but its name must match the PivotTable’s actual name.
Build the Power Automate flow
Create a cloud flow in Power Automate and choose a trigger that matches the business process. A Recurrence trigger works for a regular schedule; a SharePoint trigger can start a flow when a file is created or modified. Add the Excel Online (Business) action named Run script, then select the workbook’s storage location, document library if applicable, file, and saved script.
- Choose a trigger, such as Recurrence or a suitable SharePoint file event.
- If the flow adds or changes source rows, put those actions before Run script.
- Add Excel Online (Business) > Run script and select the target workbook and Office Script.
- Save the flow, run a controlled test, and open the workbook to confirm the updated summary.
For example, a flow might run when a sales file arrives in a SharePoint folder, copy its records into a master workbook’s Excel table, and then run the script to update the workbook’s PivotTables. Put the script action after the row-writing action; otherwise the refresh might happen before the new records are present. When the trigger is based on a workbook being modified, take care that the flow’s own changes do not cause an endless sequence of new runs. Use an appropriate trigger condition, a separate control file, or a schedule to prevent that loop.
Make the source data and timing reliable
A refresh only summarizes records that Excel can see in the PivotTable’s source. If a PivotTable uses a fixed range such as A1:F500 and the flow writes row 501, that record may be outside the source. Changing the source to an Excel table and having the flow append rows to that table usually avoids this particular problem. If the source is a range, check and update the source range when its size changes.
Also ensure that the flow finishes writing before the refresh begins. In a multi-step flow, place Run script after the actions that add or edit rows. If data arrives through a separate flow or external process, arrange a clear completion signal or an adequate delay rather than assuming that two independent processes will finish in the desired order. A delay can help with timing, but it does not fix an incorrect source range or an unavailable data connection.
Test with a small, identifiable record—for example, a new date and amount that should change one PivotTable total. Check both the source table and the summary. If the source row is present but the summary is unchanged, investigate the PivotTable’s source, filters, and refresh status. If the source row is absent, fix the part of the flow that writes data before changing the refresh script.
Limits with external data and connections
Not every workbook data source can be refreshed in the same way in Excel for the web. The PivotTable refresh method is appropriate for refreshing PivotTables from data already available in the workbook, such as a local table. It should not be treated as a universal command to log in to every database, rerun every Power Query, or update every external connection.
Office Scripts also provides a workbook method for refreshing data connections, but Microsoft documents that refresh-all behavior as applying to Power BI connections; it does not make every other connection type refreshable through a cloud script. If the PivotTable depends on an external source, verify that the source and its authentication are supported for the particular online workflow. A desktop refresh that succeeds with a user’s saved credentials may not work from a cloud flow.
For connection-heavy workbooks, separate the steps: first make sure the source data is refreshed by a supported service or process, then refresh the PivotTable from the available data. If a source cannot be refreshed in the cloud, possible options include changing the data-delivery method, using a supported dataflow or service, or having an attended desktop process handle the refresh. Do not assume that running an Office Script removes the source’s authentication or compatibility requirements.
Common problems and practical fixes
- The script or action is missing: Confirm that Office Scripts is available to the Microsoft 365 account, that the script has been saved, and that the flow uses Excel Online (Business).
- The summary does not include new rows: Check that the flow wrote to the correct workbook and that the PivotTable source includes the new rows. A table-based source is often easier to extend than a fixed range.
- The script reports that a workbook or file is unavailable: Recheck the selected location, library, file, and permissions. Confirm that the flow connection can access the same file the script is meant to edit.
- The workbook is locked or an action fails intermittently: Avoid having users or other flows edit the workbook at the same time. Where possible, serialize runs and use the flow’s retry settings thoughtfully; repeated simultaneous edits can create conflicts.
- The flow runs repeatedly: A file-modified trigger may react to the workbook update made by the flow itself. Add trigger conditions or redesign the trigger so the refresh does not retrigger the same process.
Excel connector operations can also encounter service limits or transient errors. Keep flows focused, avoid launching overlapping runs against the same workbook, and inspect the flow’s run history to identify which action failed. A successful Run script action means the script completed; it does not prove that an upstream data connection returned the expected latest records.
Alternatives and when to use them
If the workbook is used only by people working in desktop Excel, they can refresh a PivotTable manually from the PivotTable Analyze tab using Refresh or Refresh All. This is simple, but it depends on someone opening the file and may not meet a scheduled or unattended requirement. Excel for the web offers PivotTable refresh controls too, though available behavior can vary with the data source.
For an automated cloud workflow, Office Scripts plus Power Automate is generally the direct option when the source data is in the workbook and the script is supported. If the workbook relies on a connection that cannot refresh online, consider refreshing the data upstream and delivering a refreshed table to Excel, or using an appropriate reporting service rather than treating the workbook as the entire data pipeline. Power Automate Desktop can automate desktop Excel in some scenarios, but it requires a machine and an appropriately designed desktop flow; it is not the same as a cloud flow running an Office Script.
Frequently Asked Questions
Can Power Automate refresh an Excel PivotTable without a script? Not through a dedicated standard Excel Online (Business) refresh-PivotTable action. For a cloud flow, use Run script with an Office Script that calls the PivotTable refresh method.
Can I refresh just one PivotTable? Yes. Use a script that targets the PivotTable by its name and calls its refresh method. Check the name in Excel’s PivotTable Name box; names such as PivotTable1 are common, but should not be assumed.
Will refreshing the PivotTable also refresh Power Query? Do not assume so. PivotTable refresh and external-data refresh are separate concerns, and cloud support depends on the connection type. Verify that the query or connection is refreshed by a supported method before relying on the PivotTable summary.
Can I refresh a PivotTable in a local Excel file? The cloud Run script action needs a workbook available through a supported cloud location, such as OneDrive for Business or SharePoint. A file stored only on a local drive is not directly available to that action.
Why does my PivotTable still show the old total after the flow succeeds? Check whether the new record reached the source, whether the PivotTable source covers it, whether filters exclude it, and whether the source requires a separate refresh. A successful script run cannot correct missing source data or an unsupported connection.
Related
- Lock a Slicer in Excel: Keep It in Place and Usable
- Location Heat Map in Excel: How to Build One
- Excel Lookup Remove Duplicates: What Works and Why
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Waterfall Chart: Show a Total at the EndTo show a final total in an Excel waterfall chart, select the last bar, right-click it, and choose…
- Percentage Half Moon Chart in Excel: How to Create OneTo create a percentage half moon in Excel, build a doughnut chart from three values: the completed percentage,…
- Proper Excel Chart Creation: A Step-by-Step GuideTo create a proper chart in Excel for Microsoft 365 on Windows, arrange your data in a clear…
- Excel Chart Types: Uses, Examples, and How to ChooseExcel includes chart types for comparing categories, showing change over time, displaying parts of a whole, and exploring…
- Combo Waterfall Charts in Excel: How to Build OneYes, you can make a chart that combines a waterfall-style breakdown with another measure, such as a target…
- Create a Pivot Table in Excel From Multiple Sheets?To create a PivotTable from multiple sheets in Excel for Microsoft 365 on Windows, combine the sheets’ data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.