Will A Pivot Table Will Automatically Update When The Source Data Is Changed?

It would be overnice to automatically refresh an Excel pivot table, whenever its source data changes. Unfortunately, at that place isn't a built-in way to brand that happen. However, here are 3 means you can refresh a pin tabular array, afterward you make changes to the source data.

No Congenital-In Automatic Refresh for Pivot Table

If you add together new records, or delete records, or edit the existing information, the pin table doesn't bear witness the revised information right abroad. There isn't any setting the you tin change, to make Excel automatically refresh a pivot table, as presently as the data changes.

As a outcome, to go along the pivot table upwards-to-engagement, you take 3 choices:

1. Manually refresh the pin tabular array
2. Use a macro to automatically refresh an Excel pivot table
3. Modify a pivot table setting, to get some automatic updates

Manually Refresh

No, this isn't automatic, just a quick and easy way to refresh the pin tabular array after the data changes is to manually update it:

• Right-click any jail cell in the pin table, then click on Refresh.

Pro: This is a simple way to instantly refresh the pivot table

Con: Information technology's not automatic. You lot take to remember to do this, every time the information changes!

Automatically Refresh When File Opens

One fashion to automatically refresh is to set the pin table to refresh when you open the file that information technology'due south in. With this method, you lot'll get partial automation, without having to add macros to the file.

Whatever time you lot close, and then re-open the file, the pivot table volition be refreshed.

To fix this refresh method:

1. Right-click any cell in the pivot table
2. Click PivotTable Options
3. In the PivotTable Options window, click the Data tab
4. In the PivotTable Data section, add a cheque mark to Refresh Data When Opening the File
5. Click OK to close the dialog box.

Pro: Whatsoever time you shut, so re-open the file, the pivot table will automatically refresh

Con: The pivot table does non refresh automatically while y'all accept the workbook open

Automatically Refresh With Macro

The last way to update the pin table is with a macro. You tin can set upward an Excel macro to automatically refresh a pin tabular array when its worksheet is activated.

If this is something that you lot haven't done before, in that location are instructions on my Contextures website, for copying VBA lawmaking to your own files.

Macro for  1 Pivot Table

Put the following code on the worksheet module, if there is one pivot table on the sheet:

Private Sub Worksheet_Activate()    Application.EnableEvents = Simulated    Me.PivotTables(1).RefreshTable    Awarding.EnableEvents = True Terminate Sub

Note: If the pivot tabular array sheets are protected, go to this article for help with pivot tables on a protected sheet.

Macro for Multiple Pivot Tables

If in that location are ii or more pin tables on the canvas, with different source information, employ this code, pasted onto the worksheet module. This will automatically refresh all the pivot tables on the sheet:

Private Sub Worksheet_Activate()    Dim pt Every bit PivotTable    Application.EnableEvents = False    For Each pt In Me.PivotTables       pt.RefreshTable    Adjacent pt    Application.EnableEvents = True End Sub

Video: Pin Table Troubleshooting

Before y'all add macros, information technology might assist to get a list of all the pivot tables in your workbook, with details on where they're located,

This video shows a macro that creates that list for you lot, and yous can get the sample file from my Contextures website, to follow along.

More than Information

Pivot Table Refresh Tips

Stop a Pivot Table Refresh

Create a Pivot Table

Excel Pin Table — Dynamic Data Source

Automatically Include New Data in a Pin Table

_______________________________

Source: https://www.pivot-table.com/2014/10/15/automatically-refresh-a-pivot-table/

Posted by: upchurchyouren.blogspot.com

Share this post