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

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.


Download Practice Workbook

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


2 Methods to Auto Refresh Pivot Table in Excel

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. Refresh 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 Refresh All Pivot Tables in Excel


Similar Readings


2. Auto Refresh 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: How to Refresh All Pivot Tables with VBA (4 Ways)


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 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: VBA to Refresh Pivot Table 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.


Conclusion

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


Further Readings

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

ExcelDemy
Logo