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 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:
Method 1 – Refreshing Pivot Table Automatically When the Workbook Is Opened
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.
- Hit OK to close the window.
Read More: How to Update Pivot Table Range
Method 2 – Auto Refreshing Excel Pivot Table with VBA
Steps:
- Go to the Developer tab in the ribbon and click on the Visual Basic option to open 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. This opens a new module to write the necessary code.
- Click the object-dropdown, on the left of the module and choose Worksheet.
- The above step would add a Worksheet_SelectionChange event.
- Click on the Procedure dropdown and choose Change
- 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.
- Add this 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
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)
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.
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
- Pivot Table Not Refreshing
- How to Refresh All Pivot Tables in Excel
- How to Refresh All Pivot Tables with VBA
- How to Refresh Pivot Table with VBA in Excel
