It would be nice to automatically refresh an Excel pivot table, whenever its source data changes. Unfortunately, there isn’t a built-in way to make that happen. However, here are 3 ways you can refresh a pivot table, after you make changes to the source data.
No Built-In Automatic Refresh for Pivot Table
If you add new records, or delete records, or edit the existing data, the pivot table doesn’t show the revised data right away. There isn’t any setting the you can change, to make Excel automatically refresh a pivot table, as soon as the data changes.
As a result, to keep the pivot table up-to-date, you have 3 choices:
- Manually refresh the pivot table
- Use a macro to automatically refresh an Excel pivot table
- Change a pivot table setting, to get some automatic updates
Manually Refresh
No, this isn’t automatic, but a quick and easy way to refresh the pivot table after the data changes is to manually update it:
- Right-click any cell in the pivot table, then click on Refresh.
Pro: This is a simple way to instantly refresh the pivot table
Con: It’s not automatic. You have to remember to do this, every time the data changes!
Automatically Refresh When File Opens
One way to automatically refresh is to set the pivot table to refresh when you open the file that it’s in. With this method, you’ll get partial automation, without having to add macros to the file.
Any time you close, and then re-open the file, the pivot table will be refreshed.
To set up this refresh method:
- Right-click any cell in the pivot table
- Click PivotTable Options
- In the PivotTable Options window, click the Data tab
- In the PivotTable Data section, add a check mark to Refresh Data When Opening the File
- Click OK to close the dialog box.
Pro: Any time you close, and then re-open the file, the pivot table will automatically refresh
Con: The pivot table does not refresh automatically while you have the workbook open
Automatically Refresh With Macro
The final way to update the pivot table is with a macro. You can set up an Excel macro to automatically refresh a pivot table when its worksheet is activated.
If this is something that you haven’t done before, there are instructions on my Contextures website, for copying VBA code to your own files.
Macro for One 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 = False Me.PivotTables(1).RefreshTable Application.EnableEvents = True End Sub
Note: If the pivot table sheets are protected, go to this article for help with pivot tables on a protected sheet.
Macro for Multiple Pivot Tables
If there are two or more pivot tables on the sheet, with different source data, use this code, pasted onto the worksheet module. This will automatically refresh all the pivot tables on the sheet:
Private Sub Worksheet_Activate() Dim pt As PivotTable Application.EnableEvents = False For Each pt In Me.PivotTables pt.RefreshTable Next pt Application.EnableEvents = True End Sub
Video: Pivot Table Troubleshooting
Before you add macros, it might help 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, and you can get the sample file from my Contextures website, to follow along.
More Information
Excel Pivot Table — Dynamic Data Source
Automatically Include New Data in a Pivot Table
_______________________________
Great help than you very much
I had the issue of my Pivot tables updating before the External Data Source refresh had finished.
So on the sheets that hold the external data source I added, you code to update PivotTables on data change (I think its working, and data and Pivot tables in Sync)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim PT As PivotTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.RefreshTable
Next PT
Next WS
End Sub
Thanks
Hi Steven,
Good day to you. I tried using this code and it keeps crashing my workbook. Can you be of help please?
Patrick
Private Sub Worksheet_Activate()
PivotTables(“PivotTable1”).PivotCache.Refresh
End Sub
Hello guys, i have data in sheet 1 and want to create a pivot in sheet 2 from it (the range for data in sheet 1 is dynamic) . Now I want to use pivot data from sheet 2, copy and paste it in sheet 3 from cloumn A to J and has already prepared some formulas from column k onwards which will be using data pasted in sheet 3 from column A to J.
Please note – Pivot in sheet 2 to be arranged as (1 column in filters, 1 column in rows, 7 columns in E-values) and as and when we filter the data based on values present in column filter the data should get updated in sheet 3 where we want to copy pivot data, so this data must be linked)
help me with the same
Regards,
Akshay Sharma
I want to create a excel macro to do this task.
Thanks in advance
Hello where you able to do this, I am doing exact same. sheet 1 has dynamic range which is used to create a pivot table in sheet 2. I want to keep refreshing the Pivot Table every run and Send email out.
hello , please how can i use Pivot Table to populate on excel sheet.
Is there any possible way that we change the data source of pivot table or we add some rows in the source data of pivot table the sheets we alreday open through pivot table also change when the source data change
I have a Pivot table in where the data needs to be arrange based on the priority of the item, however every time that i refresh the pivot table it was not automatically arranged based on the priority. Could you suggest a fix on this please. Thank you!