Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)

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.

Auto Refresh Pivot Table without VBA When Opening File

  • Mark your desired sheet, I marked the New sheet.

Auto Refresh Pivot Table without VBA When Opening File

  • After appearing the PivotTable Fields, mark the headers.
  • Later, click as follows: PivotTable Analyze > PivotTable > Options.

Auto Refresh Pivot Table without VBA When Opening File

  • Next, from the Data section in the appeared dialog box, mark ‘Refresh data when opening the file’.
  • Finally, just press OK.

Auto Refresh Pivot Table without VBA When Opening File

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.

Insert Data from External Source in Pivot Table to Auto Refresh without VBA

  • Click on Choose Connection from the appeared dialog box.

Insert Data from External Source in Pivot Table to Auto Refresh without VBA

  • 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.

Insert Data from External Source in Pivot Table to Auto Refresh without VBA

  • Later, select desired worksheet and press OK.

Insert Data from External Source in Pivot Table to Auto Refresh without VBA

Soon, you will get the Pivot Table Fields on the right side of your sheet.

  • Mark the headers.

Insert Data from External Source in Pivot Table to Auto Refresh without VBA

  • 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:

  • First, click any data from the dataset and then press CTRL + T to insert a Table.

Using PivotTables Based on Power Query to Auto Refresh

  • Then click any data from the data Table and click as follows: Data > From Table/Range.

Using PivotTables Based on Power Query to Auto Refresh

Soon after you will get a Power Query window.

  • Later, click: Close & Load > Close & Load To.

Using PivotTables Based on Power Query to Auto Refresh

  • Next, mark Only Create Connection.
  • Then mark Add this data to the Data Model and press OK.

Using PivotTables Based on Power Query to Auto Refresh

  • After that, click as follows: Insert > PivotTable > From Data Model.

Using PivotTables Based on Power Query to Auto Refresh

  • Select desired worksheet and press OK.

Using PivotTables Based on Power Query to Auto Refresh

  • Then from the appeared PivotTable Fields in the selected worksheet, mark the headers from your inserted Table.

Using PivotTables Based on Power Query to Auto Refresh

  • Later, click Properties from the Data tab.

Using PivotTables Based on Power Query to Auto Refresh

  • 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:

Using PivotTables Based on Power Query to Auto Refresh

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.

Using PivotTables Based on Power Query to Auto Refresh

  • At this moment, mark your desired worksheet, I marked New Worksheet.
  • Then mark, Add this data to the Data Model, and press OK.

Using PivotTables Based on Power Query to Auto Refresh

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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo