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

Get FREE Advanced Excel Exercises with Solutions!

This article shows how to auto-refresh pivot table in Excel using two different methods. Auto-updating the pivot table with the change in the data source is a powerful feature that Excel provides. But it is not a built-in function. Let’s follow the guide to automate your Excel calculation.

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  2 pivot tables we made using this dataset- one to show how the total sales vary for different cities (screenshot 1) and another table that displays total sales for different categories of products (screenshot 2).

Screenshot 1:

Screenshot 2:

Auto Refresh Pivot Table in Excel


1. Refreshing Pivot Table Automatically When the Workbook is Opened

This method will update the pivot table each time the workbook is opened, not each time a change is made to the dataset. So, it is like partial automation of the pivot table. Let’s follow the steps to enable the auto-refreshing feature for a pivot table:

Steps:

  • 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

  • Finally, hit OK to close the window.

Read More: How to Update Pivot Table Range


2. Auto Refreshing Excel Pivot Table with VBA

Using simple VBA code we can automatically update our pivot table when we change any source data. Most importantly it happens immediately unlike the previous method where we need to close and reopen the file again to see the updates. To make it happen let’s follow the guide!

Steps:

  • From the Excel Ribbon go to the Developer Tab and click the Visual Basic tab to open the Visual Basic Editor.

Auto Refresh Pivot Table in Excel

  • In the Visual Basic Editor go to the VBA Project Explorer where all the worksheets are listed. Choose the worksheet that contains source data and double-click. That’ll open a new module to write the necessary code.

  • In this step, we want to add an event macro. For this, click the object-dropdown, on the left of the module and choose

Auto Refresh Pivot Table in Excel

  • The above step would add a Worksheet_SelectionChange event.

Auto Refresh Pivot Table in Excel

  • To add an event to the module let’s click on the Procedure dropdown and choose the Change

Auto Refresh Pivot Table in Excel

  • Now we see a new event macro is 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

  • Finally, add the simple VBA code inside the change event.
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
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


3. VBA Code for Auto Refreshing a Single Pivot Table

If we don’t want to auto-refresh all the pivot tables in the workbook rather than just a specific one, we can use the following code. 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)
Worksheets("pivot-category").PivotTables("PivotTable1").PivotCache.Refresh
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.

If we don’t want to auto-refresh all the pivot tables in the workbook rather just a specific one, we can use the following code. This code will only update the pivot table in the sheet pivot-category when we change the data source.

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.


Conclusion

Now, we know how to automate pivot tables in Excel. Hopefully, it will encourage you to use this feature more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo