Automatically Update a Pivot Table When Source Data Changes in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Data


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.

Steps to Automatically Update a Pivot Table When Source Data Changes

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

Steps to Automatically Update a Pivot Table When Source Data Changes

  • 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.

Steps to Automatically Update a Pivot Table When Source Data Changes


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.

Steps to Automatically Update a Pivot Table When Source Data Changes


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.

Steps to Automatically Update a Pivot Table When Source Data Changes

 Notes:  A worksheet event means that any changes you make to the source data will be reflected in your program immediately. That’s why the program will run automatically when you make any changes to the source data set.


Step 4: Inserting a New Event for Change in Worksheet

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

Steps to Automatically Update a Pivot Table When Source Data Changes

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

Steps to Automatically Update a Pivot Table When Source Data Changes


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.

Sample Data


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

Sample Data


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.

Sample Data

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

Sample Data


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

Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo