Md. Sourov Hossain Mithun
Pivot Table can’t auto-refresh data from its source data by default. So, if you change any source data then it won’t update automatically in the Pivot table. We can do it easily with VBA. But if you want to do it without VBA then of course there are some smart ways. So in this article, I’ll show 3 smart ways to auto-refresh pivot table without VBA in Excel.
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
3 Ways to Auto Refresh Pivot Table without VBA in Excel
Get introduced to our dataset first, it represents some employees’ salaries.
1. Auto Refresh Pivot Table without VBA While Opening File
One of the easiest ways to auto-refresh pivot tables without VBA is to use an option in Excel which can auto-refresh data every time you open the file.
Steps:
- First, select any data from the dataset.
- Then click as follows: Insert > PivotTable.
Soon after, you will get a dialog box.
- Mark your desired sheet, I marked the New sheet.
- After appearing the PivotTable Fields, mark the headers.
- Later, click as follows: PivotTable Analyze > PivotTable > Options.
- Next, from the Data section in the appeared dialog box, mark ‘Refresh data when opening the file’.
- Finally, just press OK.
Then I changed Anne’s salary from $1500 to $2000 then reopened the file.
And now it is auto-updated in the PivotTable.
Read More: VBA to Refresh Pivot Table in Excel (5 Examples)
2. Insert Data from External Source in Pivot Table to Auto Refresh without VBA
We can import data from any external source for PivotTable and then we can get an option to auto-refresh data.
Steps:
- Firstly, click as follows: Insert > PivotTable > From External Data Source.
- Click on Choose Connection from the appeared dialog box.
- Then select a table from the appeared list or click Browse for More to import from your PC if you have any saved tables. I selected a previously used table.
- Next, press Open.
- Later, select desired worksheet and press OK.
Soon, you will get the Pivot Table Fields on the right side of your sheet.
- Mark the headers.
- Next, click Data > Properties.
- After that, from the Query Properties dialog box, mark Refresh every box with your desired time intervals. Also mark Refresh data when opening the file.
- Finally, just press OK.
From now, PivotTable will be auto refreshed after your set minutes.
Read More: How to Refresh Pivot Table in Excel (4 Effective Ways)
3. Using PivotTables Based on Power Query to Auto Refresh
In this section, we’ll learn to use Power Query for auto-refreshing the Pivot Table. There are 3 ways in it.
3.1. Data Loaded to Data Model
First, we’ll load data to the data model and then will be able to set auto-refresh time.
Steps:
- Then click any data from the data Table and click as follows: Data > From Table/Range.
Soon after you will get a Power Query window.
- Later, click: Close & Load > Close & Load To.
- Next, mark Only Create Connection.
- Then mark Add this data to the Data Model and press OK.
- After that, click as follows: Insert > PivotTable > From Data Model.
- Select desired worksheet and press OK.
- Then from the appeared PivotTable Fields in the selected worksheet, mark the headers from your inserted Table.
- Later, click Properties from the Data tab.
- Then from the Query Properties dialog box, mark Refresh every box with your desired time intervals. Also mark Refresh data when opening the file.
- Finally, just press OK.
Then I changed one value in the dataset.
Now see, it is updated in the Pivot Table after 5 minutes.
3.2. Data Loaded to PivotChart or PivotTable Report
Like the previous method, if we mark PivotTable Report or PivotChart instead of Only Create Connection, we can also set a timer to auto-refresh data.
Steps:
- Follow the first 3 steps from the previous section to get the Import Data dialog box.
- Then mark PivotTable Report or PivotChart.
- Later, select worksheet- Existing or New.
- Mark Add this data to Data Model and press OK.
- After that, follow the 8th and 9th steps from the previous section to open the Query Properties dialog box.
- Later, mark Refresh every box with your desired time intervals. Also mark Refresh data when opening the file.
- Then press OK to finish.
From now, PivotTable will be auto refreshed after your set minutes or whenever you reopen the file.
3.3. Data Loaded Directly to Data Model
You can load data to the Data Model directly while creating a PivotTable, then you can set a timer to auto-refresh. This way uses fewer steps than the previous way.
Steps:
- Click any from the dataset.
- Next, click as follows: Insert > PivotTable.
- At this moment, mark your desired worksheet, I marked New Worksheet.
- Then mark, Add this data to the Data Model, and press OK.
Soon after, on the right side of the selected worksheet Queries & Connections field will open up.
- In that section, click Connections and right-click on the connection name, and select Properties from the context menu.
- After appearing the Connection Properties dialog box, mark Refresh every box with your desired time intervals. Also mark Refresh data when opening the file.
- Finally, press OK to finish.
Then 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 (3 Ways)
Conclusion
I hope the procedures described above will be good enough to auto-refresh the pivot table without VBA in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.
