How to Auto Refresh Pivot Table in Excel (2 Methods)

To illustrate how to refresh an Excel pivot table, we created two pivot tables for a dataset. The dataset shows a list of sale data with all required details like date, region, city name, product name, product category, unit price, quantity, and total price.

Auto Refresh Pivot Table in Excel

There are two pivot tables we made using this dataset. One shows how the total sales vary for different cities (screenshot 1) and the other displays total sales for different categories of products (screenshot 2).

Screenshot 1:

Screenshot 2:

Auto Refresh Pivot Table in Excel

Method 1 – Refreshing Pivot Table Automatically When the Workbook Is Opened


  • Right-click any cell of the pivot table to open the context menu.
  • Choose PivotTable Options from the context menu.

  • From the PivotTable Options window, go to the Data tab and check the Refresh data when opening the file option.

Auto Refresh Pivot Table in Excel

  • Hit OK to close the window.

Read More: How to Update Pivot Table Range

Method 2 – Auto Refreshing Excel Pivot Table with VBA


  • Go to the Developer tab in the ribbon and click on the Visual Basic option to open the Visual Basic Editor.

Auto Refresh Pivot Table in Excel

  • Go to the VBA Project Explorer where all the worksheets are listed. Choose the worksheet that contains source data and double-click. This opens a new module to write the necessary code.

  • Click the object-dropdown, on the left of the module and choose Worksheet.

Auto Refresh Pivot Table in Excel

  • The above step would add a Worksheet_SelectionChange event.

Auto Refresh Pivot Table in Excel

  • Click on the Procedure dropdown and choose Change

Auto Refresh Pivot Table in Excel

  • A new event macro has been added to the module named Worksheet_Change. We’ll write our code inside this one. So, delete the Worksheet_SelectionChange.

Auto Refresh Pivot Table in Excel

  • Add this VBA code inside the change event.
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

This VBA code will run anytime we change cell data in the source file. All the pivot tables related to the source will be updated accordingly and instantly.

Read More: Automatically Update a Pivot Table When Source Data Changes in Excel

Method 3 – VBA Code for Auto Refreshing a Single Pivot Table

This code will only update the pivot table in the sheet pivot-category when we change the data source.

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

Auto Refresh Pivot Table in Excel

In this code, pivot-category is the sheet name that contains the PivotTable. We can easily check the name of a worksheet and a pivot table.

In the screenshot above, we can see the sheet name in the bottom tab of the Excel worksheet.

Read More: How to Auto Refresh Pivot Table without VBA in Excel

Things to Remember

Using VBA code in Method 2 automates our pivot tables but it loses the undo history. After making a change, we cannot go back to the previous stage. This is a disadvantage of using a macro to update pivot tables automatically.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF