Method 1 – Auto Refresh Pivot Table Without VBA While Opening File
Steps:
- Select any data from the dataset.
- Click as follows: Insert > PivotTable.
A dialog box will open.
- Mark your desired sheet. I marked the New sheet.
- From the PivotTable Fields, mark the headers.
- Click on PivotTable Analyze > PivotTable > Options.
- From the Data section, mark ‘Refresh data when opening the file’.
- Press OK.
I changed Anne’s salary from $1500 to $2000 and reopened the file.
It is auto-updated in the PivotTable.
Read More: How to Update Pivot Table Range
Method 2 – Insert Data from External Source in Pivot Table to Auto Refresh without VBA
Steps:
- Click on Insert > PivotTable > From External Data Source.
- Click on Choose Connection.
- Select a table or click Browse for More to import from your system if you have any saved tables. I selected a previously used table.
- Press Open.
- Select the worksheet and press OK.
The Pivot Table Fields will appear.
- Mark the headers.
- Click Data > Properties.
- From the Query Properties dialog box, mark Refresh every box with the time intervals. Mark Refresh data when opening the file.
- Press OK.
PivotTable will be auto refreshed after your set minutes.
Read More: Automatically Update a Pivot Table When Source Data Changes in Excel
Method 3 – Using PivotTables Based on Power Query to Auto Refresh
3.1. Data Loaded to Data Model
Load data to the data model to set auto-refresh time.
Steps:
- Click any data from the dataset and press CTRL + T to insert a Table.
- Click any data from the data Table and click as follows: Data > From Table/Range.
A Power Query window will open.
- Click on Close & Load > Close & Load To.
- Mark Only Create Connection.
- Mark Add this data to the Data Model and press OK.
- Click as follows: Insert > PivotTable > From Data Model.
- Select desired worksheet and press OK.
- From the PivotTable Fields, mark the headers from the inserted Table.
- Click Properties from the Data tab.
- From the Query Properties dialog box, mark Refresh every box with the time intervals. Mark Refresh data when opening the file.
- Press OK.
I changed one value in the dataset.
It is updated in the Pivot Table after some time.
3.2. Data Loaded to PivotChart or PivotTable Report
Steps:
- Open the Import Data dialog box.
- Mark PivotTable Report or PivotChart.
- Select worksheet- Existing or New.
- Mark Add this data to Data Model and press OK.
- Open the Query Properties dialog box.
- Mark Refresh every box with the time intervals. Mark Refresh data when opening the file.
- Press OK.
The PivotTable will be auto refreshed after your set minutes or whenever you reopen the file.
3.3. Data Loaded Directly to Data Model
Steps:
- Click any data from the table.
- Click as follows: Insert > PivotTable.
- Mark the worksheet, I marked New Worksheet.
- Mark, Add this data to the Data Model and press OK.
The Queries & Connections field will open.
- Click Connections and right-click on the connection name and select Properties from the context menu.
- Mark Refresh every box with the time intervals. Mark Refresh data when opening the file.
- Press OK.
Excel will auto-refresh the PivotTable after your set minutes or whenever you reopen the file.
Read More: How to Refresh All Pivot Tables in Excel
Download Practice Workbook
Related Articles
- How to Refresh All Pivot Tables with VBA
- How to Refresh Pivot Table with VBA in Excel
- How to Auto Refresh Pivot Table in Excel
- Pivot Table Not Refreshing
