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.
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:
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.
- 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.
- 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
- The above step would add a Worksheet_SelectionChange event.
- To add an event to the module let’s click on the Procedure dropdown and choose the Change
- 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
- 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
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.
