Yes. In Excel for Microsoft 365 on Windows, right-click a PivotTable, choose PivotTable Options, open the Data tab, select Refresh data when opening the file, and click OK. Save the workbook afterward. Excel will refresh that PivotTable from its source when the workbook opens, provided the source is available and any required connections can be accessed.
What refreshing on open actually does
A PivotTable summarizes source data using a stored cache or a data connection. Refreshing tells Excel to retrieve the latest available source data and update the summary. For example, if a sales table has received new rows since the workbook was last opened, refreshing can bring those rows into the PivotTable so its totals and categories reflect the updated data.
The setting is not a live link that updates the PivotTable every time someone edits a source cell. It runs at workbook opening, and it does not guarantee that the underlying source itself is current. If the source is another workbook, database, web connection, or query, the refresh can only use data Excel can reach at that time. Permissions, sign-in requirements, network availability, or connection errors can prevent an update.
Also distinguish refreshing a PivotTable from recalculating formulas. Refreshing updates the PivotTable from its source; calculation updates formula results in worksheet cells. Excel may calculate formulas as part of normal workbook opening, but a formula recalculation is not a substitute for refreshing a PivotTable cache.
Turn on the setting in Excel for Windows
Use the PivotTable itself to open its options. The setting is available when a PivotTable is selected, so click inside the summary first. The Windows steps are:
- Click any cell inside the PivotTable.
- Right-click and choose PivotTable Options.
- In the dialog, select the Data tab.
- Check Refresh data when opening the file.
- Click OK, then save the workbook with Ctrl+S.
You can also access PivotTable options from the contextual PivotTable Analyze tab on the ribbon, but the right-click route is usually the most direct. The contextual tab appears when the active cell is inside a PivotTable. If you do not see it, click back into the PivotTable and look for the PivotTable-related ribbon tab.
To refresh immediately rather than wait for the next opening, click inside the PivotTable and choose PivotTable Analyze > Refresh. To update all refreshable PivotTables and connections in the workbook, use Data > Refresh All. On Windows, Alt+F5 refreshes the selected PivotTable, while Ctrl+Alt+F5 refreshes all. The ribbon commands are a straightforward alternative if a keyboard shortcut differs on a particular keyboard or setup.
Excel for Mac and Excel for the web
In Excel for Mac, select the PivotTable and look for its contextual PivotTable ribbon tab and options dialog. The exact ribbon arrangement can vary by Excel release. If the dialog exposes the Refresh data when opening the file option, select it and save the workbook. If you cannot find that option in your Mac edition, configure the workbook in desktop Excel for Windows, where the steps above apply. Mac users can also use the available PivotTable refresh command or Data > Refresh All for an immediate update.
Excel for the web can refresh some PivotTables and connections, depending on the workbook and its source, but it does not expose every desktop PivotTable setting. To set or verify the open-time option, use the desktop application. Afterward, the workbook can be opened in the web app, but the result still depends on whether the relevant source and connection are supported and accessible there.
What happens with different data sources
The refresh-on-open option is most predictable when the PivotTable uses a simple worksheet source in the same workbook. For external sources, opening the file can involve additional connection behavior. Excel may need a network path, a database login, an approved data source, or a current account session before it can retrieve anything.
- Excel Table: A Table generally expands when new rows are added directly beneath it or entered into the next row. A PivotTable based on that Table can then include the additional records after refresh.
- Fixed worksheet range: If the source was set to, for example, A1:D500, adding records in row 501 does not necessarily expand that source. Refreshing the existing range cannot include rows that are outside it. Change the source range or convert the data to a Table.
- Power Query or external connection: The query or connection must refresh successfully. A PivotTable refresh alone cannot resolve missing credentials, a disconnected server, or a changed file path.
- Data Model or OLAP source: The PivotTable depends on its model or analytical connection. Refresh behavior and available options can differ from a PivotTable based on a local range, so verify that the model or connection is also being refreshed as intended.
For a workbook with queries, tables, and PivotTables, Data > Refresh All is often useful for an intentional manual update because it asks Excel to refresh the workbook’s refreshable connections as well as PivotTables. It is still important to check the order and dependencies of queries if one query supplies another. An automatic PivotTable refresh should not be assumed to repair a broken query or to update every unrelated connection in exactly the order a particular workflow requires.
One PivotTable or every PivotTable?
The checkbox is set through a PivotTable’s options, so do not assume that selecting it once configures every PivotTable in the workbook. If a workbook contains several PivotTables, inspect the others or test the workbook after saving and reopening it. Some PivotTables can share a cache, and settings or source changes may affect more than one PivotTable that uses that shared cache; separate PivotTables may use separate caches. The practical rule is to verify the actual workbook rather than infer behavior just from how many summaries appear on the sheet.
This matters when different summaries use different sources. A monthly sales PivotTable might use a local Excel Table, while a staffing PivotTable uses an external connection. The first could refresh successfully while the second fails because a user lacks access to the network source. Checking each PivotTable and connection makes it easier to identify which one needs attention.
Common problems and how to fix them
If a PivotTable appears unchanged after opening, start by checking the source rather than repeatedly clicking Refresh. Confirm that the new records are in the source range, that the source file is in the expected location, and that the workbook has been saved after changing the option. Then refresh manually and note any warning or connection prompt.
- New rows are missing: Check whether the source is a fixed range. Expand it or convert the source data to an Excel Table, then refresh.
- The workbook opens offline: A source on a server or shared drive may not be reachable. Reconnect to the network or open the workbook when the source is available.
- A sign-in or security prompt appears: Follow your organization’s approved sign-in process. Do not enable unknown content or bypass security warnings just to force a refresh.
- The refresh option seems not to stick: Reopen PivotTable Options, confirm the checkbox, click OK, and save the workbook. Verify again after reopening the saved file.
- The PivotTable refreshes but values still look old: Check whether the source data itself was updated, whether a query needs refreshing, and whether a filter or slicer is hiding the new records.
- Opening takes longer: Large caches and slow external connections can add time. Consider whether every PivotTable needs to refresh on every opening, or whether users should refresh manually when they need current data.
When diagnosing a refresh problem, check the PivotTable’s source with PivotTable Analyze > Change Data Source for a worksheet-based PivotTable. That command helps reveal a fixed or outdated range. It is not a general repair tool for all connection-based PivotTables, so use the workbook’s query or connection settings when the source is external.
Refresh on open versus manual refresh
Automatic refresh is useful for reports that should normally show current source data whenever someone opens them. It reduces the chance that a reader will interpret an old summary as current. Manual refresh is preferable when the source is large, slow, intermittently available, or costly to query, or when the workbook is meant to preserve a particular snapshot for review.
There is a trade-off: a refresh can change visible totals, labels, and the set of items in a report. If someone needs an auditable snapshot, preserving the original workbook or recording when the source was extracted may be more important than refreshing automatically. A workbook with the option enabled can still contain cached results when saved, but users should not treat those saved results as proof that the source was successfully refreshed at the latest opening.
Refresh-on-open is also different from a connection’s separate refresh settings, where available, such as scheduled or periodic refresh behavior. Those settings concern a connection’s own update behavior. For a workbook that combines queries and PivotTables, decide which objects need refreshing and test the sequence with a copy before relying on it for a recurring report.
Frequently Asked Questions
Will a PivotTable refresh when I open the workbook?
Yes, if Refresh data when opening the file is enabled for it, the workbook was saved with that setting, and Excel can access its source. A failed connection or an unavailable source can stop the update.
Does refresh on open include rows added to my data?
Only if those rows are within the PivotTable’s source. An Excel Table usually provides a flexible source as it grows, while a fixed range must be expanded if new records fall outside it.
How do I refresh every PivotTable at once?
In desktop Excel, use Data > Refresh All. This also attempts to update refreshable workbook connections. The result depends on each source being available and authorized.
Can I make a PivotTable refresh automatically every few minutes?
The PivotTable option discussed here refreshes when the workbook opens; it is not a repeating timer. Some external connections have their own refresh controls, but availability and behavior depend on the connection type and Excel version.
Why does my PivotTable still show old information after refresh?
Check whether the source has changed, whether the PivotTable source includes the new records, and whether a filter is hiding them. For a query or external source, confirm that the connection itself refreshed without an error.
Related
- Power Query: Append Excel Files Into One Table
- Excel Cleanup Taking Too Long? Make Cleanup Faster
- Excel Percentage Number Format: How It Works
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Access Power Pivot Add-In: Use Access Data in ExcelYou cannot add Power Pivot to Microsoft Access: Power Pivot is an Excel add-in, not an Access feature.…
- How to Left Align a Pie Chart in ExcelTo left align a pie chart in Excel, select the chart’s outer border and drag it until its…
- Google Sheets Gantt Chart Formatting GuideTo format a Gantt chart in Google Sheets, arrange tasks in rows, put each task’s start and end…
- Excel Line Chart with Two Lines: Setup, Labels, and FixesTo make an Excel line chart with two lines, arrange the shared category labels in one column and…
- Excel Chart Timeline: Create and Format OneIn Excel for Microsoft 365 on Windows, add a timeline that filters a chart by selecting its PivotTable…
- Excel Column Chart With Error Bars: Setup and MeaningTo add error bars to a column chart in Excel for Microsoft 365 on Windows, select the chart,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.