How To Auto Refresh Pivot Table When Data Changes

Once you have created a Pin Tabular array, it doesn't automatically refresh when y'all add new data or modify the existing data.

Since your Pin Table is created using the Pivot Enshroud, when the existing information changes or when yous add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table besides does not update.

Y'all need to force a refresh every time there are changes. Once y'all strength a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Tabular array.

This tutorial covers a couple of ways to exercise this.

Refresh Pivot Tabular array

This option is all-time suited when in that location are changes in the existing data source and yous want to refresh the pivot table to reflect these changes.

Here are the steps to refresh a Pivot Table:

Right-click on whatever jail cell in the Pivot Tabular array.
Select Refresh.

This volition instantly refresh the Pivot Tabular array.

You can also by selecting any cell in the Pin Table and using the keyboard shortcut ALT + F5.

Quick Tip: It'due south a good practise to catechumen the data source into an Excel Tabular array, and utilise this Excel Table to create the Pivot Tabular array. If you do this, you can also employ the refresh technique to update the Pin Table even when new data (rows/columns) are added to the data source (since an Excel Table automatically accounts for new rows/columns that are added).

Update Pivot Table by Irresolute the Data Source

If y'all've added new rows/columns to the data source, you need to alter the data source to make sure new rows/columns are a role of the dataset.

To do this:

Note that if you alter the data source into an Excel Table so apply the Excel table to create the Pivot Table, you lot don't need to use the change data source option. You can just refresh the Pivot Table and information technology'll business relationship for the new rows/columns.

Autorefresh Pivot Table Using a VBA Macro

While refreshing a Pivot table is as easy as two clicks, you still need to do this every time there is a change.

To make it more than efficient and auto-refresh the Pin Table whenever in that location is a change in the data source, you can apply a simple i-line VBA macro code.

Here is the VBA code:

          Private Sub Worksheet_Change(ByVal Target As Range)          Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh          Stop Sub

Decoding the Code: This is a change outcome which gets triggered whenever there is a change in the sheet that contains the source data. As soon as at that place is a change, the lawmaking refreshes the Pivot Enshroud of the Pin Table with the name PivotTable1.

You need to modify this code to make it work for your workbook:

"Sheet1" – change this function of the lawmaking with the name of the canvass that has the Pivot Tabular array.
"PivotTable1" – modify this to the name of your Pivot Table. To know the proper name, click anywhere in the Pin Table and click the Analyze Tab. The name would be visible in the left office of the ribbon under the 'PivotTable Proper name' header.

Where to put this VBA code:

Now when you change annihilation in the data source, the Pivot Tabular array would automatically get refreshed.

Click hither to download the example file.

Notation: Since at that place is a macro in the workbook, save this with .xls or .xlsm extension.

You May Likewise Like the Following Pin Table Tutorials: