Automatically Refresh a Pivot Table

It would be nice if Excel would automatically refresh a pivot table, whenever its source data changes, but unfortunately, there isn’t a built-in way to make that happen. Here are 3 ways that you can refresh a pivot table, after you make changes to the source data

No Built-In Way to Automatically Refresh a 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.

To keep the pivot table up-to-date, you have 3 choices:

  1. Manually refresh the pivot table
  2. Use programming to update it
  3. Change a pivot table setting, to get some automatic updates

Manually Refresh

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.

Automatically Refresh a Pivot Table

Refresh When File Opens

Another way to update 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 then re-open the file, the pivot table will be refreshed.

To set this up:

  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 check mark to Refresh Data When Opening the File
  5. Click OK to close the dialog box.

pivotrefresh02

Automatically Refresh With Programming

The final way to update the pivot table is with programming. You can an Excel macro to automatically refresh a pivot table when its worksheet is activated. There are instructions on my Contextures website, for copying VBA code to your own files.

Note: See this article for help with pivot tables on a protected sheet.

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()
   Me.PivotTables(1).RefreshTable
End Sub

Macro for Multiple Pivot Tables

If there are multiple pivot tables on the sheet, with different source data, use this code, pasted onto the worksheet module:

Private Sub Worksheet_Activate()
   Dim pt As PivotTable
   For Each pt In Me.PivotTables
      pt.RefreshTable
   Next pt
End Sub

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

  • copy the formatting from one pivot table, and apply it to another pivot table.
  • change all the values from Count to Sum
  • remove the “Sum of” from all the headings

and much more!

_______________________________

This entry was posted in Refresh. Bookmark the permalink.

6 Responses to Automatically Refresh a Pivot Table

  1. Steven Drake says:

    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

    • Patrick Idemudia says:

      Hi Steven,

      Good day to you. I tried using this code and it keeps crashing my workbook. Can you be of help please?

      Patrick

  2. Pingback: Automatically Update Data in Pivot Table

  3. Johnny Larsen says:

    Private Sub Worksheet_Activate()
    PivotTables(“PivotTable1”).PivotCache.Refresh
    End Sub

  4. Akshay says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *