Automatically Update a Pivot Table When Source Data Changes in Excel

When you need to refresh or update data in an existing PivotTable after creating it, you must use the Change Data Source command. However, manually updating the pivot table is inconvenient and time-consuming. As a result, you’ll need to figure out how to create an automated pivot table that will automatically update data. So, in this tutorial, we will show you how to automatically update a pivot table when source data changes.

We’ve included a data set with information on sales details in various regions in the image below. You can assume that our data set is quite large. So, for a dynamic purpose, we need an automated process every time we update data. To do so, we’ll run a VBA code and apply it to the data set below. To complete the task, follow the steps below.

Step 1: Creating an Excel Pivot Table with a Source Data Range

• Firstly, click on the Insert tab to insert a pivot table.
• Then, click on the PivotTable command from the PivotTables group.
• Select the From Table/Range option from the list.

• Select the table range with the header.
• Choose the New Worksheet option to place the pivot table on a new worksheet.
• Then, click OK.

• In a new worksheet (Sheet4), the pivot table will be created.
• As you can see in the image below, you can choose the fields (Region, Branch, Price, Quantity, etc.) to appear in the pivot table.
• Select the PivotTable command from the PivotTable Analyze You can find the name of your pivot table (PivotTable2) there and edit it as needed.

Step 2: Opening Visual Basic Editor to Apply VBA Code

• Press  Alt +  F11  to open the Visual Basic Editor.
• From the VBA Excel Objects, double-click to select the worksheet name (Sheet2) where your data set is located.

Step 3: Creating a Worksheet Event with Your Sheet Containing Data Set

• To create a worksheet event for Sheet2, select the Worksheet option from the list.

Step 4: Inserting a New Event for Change in Worksheet

• Choose Change instead of SelectionChange to create a new worksheet event.

• Delete the previous one to keep only the change event.

Step 5: Declaring All Variables with an Option Explicit Statement

• To declare all variables and to find the undeclared variables, type Option Explicit at the top of the program page.

Step 6: Inserting VBA Code to Update Excel Pivot Table Automatically

• Get the completed VBA code and paste it.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet4 is the sheet name of PivotTable location
'PivotTable2 is the PivotTable Name
Sheet4.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

Step 7: Running VBA Code to Get Changes in Results

• Press F5 to run the VBA code.
• Make a change to the data set to see if the program is still working. For example, in the IRE region, the price value for the Belfast branch has been changed to £113.

• Go back to your pivot table, and see that the changed price value (£113) is updated.

Download Practice Workbook

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

Conclusion

I hope this article has given you a tutorial about how to automatically update a pivot table when sourcing data changes in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us and keep learning.

Related Articles

• How to Refresh Excel Sheet Automatically (3 Suitable Methods)
• How to Refresh Chart in Excel (2 Effective Ways)
• Excel VBA: Turn Off Screen Update
• How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)
• [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
• How to Refresh Pivot Table in Excel (4 Effective Ways)