Automatically Refresh Power Query or Power Pivot Data in Excel
Question details
The user needs to configure automatically refreshing Power Query or Power Pivot data connections in Excel so imported or analyzed data updates when the workbook opens or at a scheduled interval.
- Product
- Microsoft Excel (Power Query and Power Pivot)
- Device & OS
- not provided
- Scenario
- The user wants imported or analyzed data in Excel to update automatically without manual refreshes.
- Observed behavior
- Data should refresh automatically based on connection properties or scheduled refresh settings.
Make sure the workbook contains an existing Power Query query or Power Pivot data model connection, and save it as a standard Excel workbook so connection properties can be edited.
Set Power Query connection to refresh automatically
Use this when you want a Power Query query to update on workbook open or at a regular interval without manual refresh.
Excel stores Power Query refresh behavior in each query's connection properties. These settings are per-query, so you may need to update important queries individually or use a shared connection.
In Excel, go to the Data tab and select Queries & Connections to display workbook queries.
Right-click the query in the Queries & Connections pane and choose Properties.
In the Usage tab, check Refresh every and set the interval in minutes, and/or check Refresh data when opening the file.
Click OK and save the workbook so the automatic refresh settings are retained.
Set Power Pivot connection to refresh automatically
Use this for Power Pivot data model connections when you need automatic refresh at open or at a timed interval.
Use VBA for custom automatic refresh behavior
Use VBA when you need custom refresh conditions, multiple queries refreshed at once, or extra actions before or after refresh.
Use WPS Office Spreadsheets as a Free, Lightweight Excel Alternative
WPS Office opens and edits Excel workbooks with a familiar ribbon layout, supports .xlsx format, and is free and lightweight—making it easy to move from Microsoft Excel while keeping your everyday spreadsheet work flowing.
- 1. Download WPS Office: Go to the official WPS Office website and download the free installer for your device.
- 2. Install and open WPS Spreadsheets: Install WPS Office, launch WPS Spreadsheets, and open your existing Excel workbook.
- 3. Continue working with familiar tools: Use the ribbon-style tools, formulas, and pivot tables to manage your data without changing your file format.
Frequently Asked Questions
How do I make a Power Query refresh automatically every 30 minutes in Excel?
Open Data > Queries & Connections, right-click the query, choose Properties, and on the Usage tab check Refresh every and enter 30. You can also check Refresh data when opening the file.
Where are the Power Pivot refresh settings?
Open the Power Pivot window from the Power Pivot tab, click Manage, then select Connection Properties and use the Usage tab.
Does an automatic refresh also update Power Pivot data models?
Yes, if the Power Pivot connection is set to refresh automatically; otherwise you can refresh it manually from the Power Pivot window.
Can I use VBA to refresh Power Query automatically under custom conditions?
Yes, you can use Workbook_Open or Application.OnTime with ThisWorkbook.RefreshAll or query-specific refresh commands, but the workbook must be saved as .xlsm and macros enabled.
Will Excel refresh automatically when the source file changes?
No, Excel does not monitor source files in real time. It refreshes when the workbook opens or at the scheduled interval you set in connection properties.
