How to Automatically Update Your PivotTables and PivotCharts

by Patrick G. Matthews

Introduction

Introduced in Excel 5, the PivotTable is one of the most powerful data analysis tools in the Excel arsenal.  With PivotTables, and its data visualization equivalent, the PivotChart, you can very quickly and easily aggregate, analyze, and query large quantities of data.

Sometimes a PivotTable is applied for a static set of data.  That is, the source data set to be analyzed will not itself change:

In such a case, you do not have to be concerned about making sure that the results in their PivotTable/PivotChart reflect the source.  However, in some cases the source data may be dynamic rather than static:

If the source data is (or may be) dynamic, then you should take special care to ensure that the PivotTable/PivotChart stays in sync with its source data.  This article covers techniques designed to ensure that PivotTables stay in sync with their data sources.  In doing this, the article focuses on two distinct tasks:

In addition, this article supplies code you can deploy in a Personal Macro Workbook to automate some of the set-up work for the techniques described in this article.

Making the Data Source Dynamic

If you are creating a PivotTable, you should always consider whether the source data layout for that PivotTable is stable.  That is, will the number of columns/rows always remain the same, or might the number of columns/rows change over time?  If there is any possibility that the number of columns/rows may change over time, you should use a dynamic range for your data source.  This ensures that, as you add rows and/or columns to your source data, your PivotTable will always grab the appropriate data for its calculations.

If you are using Excel 2007 or 2010, you are probably best served by creating a Table for your source data.  Tables automatically re-size as you input or paste additional rows/columns contiguous to the Table, so this makes Tables especially well-suited to handle dynamic ranges.

To create a Table:

If you are using Excel 2000, XP/2002, or 2003, you should create a dynamic Name.  Dynamic Names use a formula to determine the range it refers to, and as such can react to changing conditions and data in your worksheet.  For more information on dynamic Names, please see:

Create a Dynamic Named Range
How to create a dynamic defined range in an Excel worksheet

(Both of those links use the OFFSET function to determine the range while I prefer INDEX, but the concept is the same.)

To create a dynamic Name for your PivotTable source data (assuming the headings are in Row 1, and start in Column A):

If the values in Column A are text...
                      
                      ='sheet name'!$A$1:INDEX('sheet name'!$1:$65536,MATCH("ZZZZZZ",'sheet name'!$A:$A),MATCH("ZZZZZZ",'sheet name'!$1:$1))
                         
                      If the values in Column A are numeric/dates...
                      
                      ='sheet name'!$A$1:INDEX('sheet name'!$1:$65536,MATCH(10^300,'sheet name'!$A:$A),MATCH("ZZZZZZ",'sheet name'!$1:$1))

Select all Open in new window

In those formulas, the MATCH expression returns the number of the last populated row/column in the indicated range, and thus allows the INDEX expression to "find" the last cell of the dynamic range, and as additional rows and/or columns are populated, the formula automatically adjusts.  The key is that if the range will contain text values, MATCH must look for a string greater than any conceivable string in that range (thus "ZZZZZZ"), and if it contains numeric/date values, MATCH must be looking for a number greater than the largest conceivable number/date in that range.

If you are using Excel 2007 or 2010 and wish to use a dynamic Name instead of a Table, use the following work steps:

If the values in Column A are text...
                          
                      ='sheet name'!$A$1:INDEX('sheet name'!$1:$1048576,MATCH("ZZZZZZ",'sheet name'!$A:$A),
                      MATCH("ZZZZZZ",'sheet name'!$1:$1))
                          
                      If the values in Column A are numeric/dates...
                          
                      ='sheet name'!$A$1:INDEX('sheet name'!$1:$1048576,MATCH(10^300,'sheet name'!$A:$A),
                      MATCH("ZZZZZZ",'sheet name'!$1:$1))

Select all Open in new window

Setting the Data Source for your PivotTable/PivotChart

When you create a PivotTable, you must indicate what the data source is for that PivotTable:


By default, if the current selection contains data, Excel will pre-populate the form with an absolute range reference to the selection's current region (as in the image above), or, in Excel 2007 or later, if that current region is part of a Table, Excel will pre-populate with the Table's name.

If you are using Excel 2007/2010 and you created a Table, use the Table's name to indicate your PivotTable's source.

If you created a Name for the PivotTable's source, then enter that Name here.

Note: I always recommend that you NOT put your PivotTable/PivotCharts on the same worksheet as your source data!

 

Forcing PivotTables/PivotCharts to Refresh

Making your source data a Table or dynamic Name is not enough, however: while that ensures that your PivotTable's source always updates as you add new rows/columns, that by itself will not force your PivotTables/PivotCharts to always show up-to-date results.  Doing this will require an optional setting in the PivotTable options and a bit of Visual Basic for Applications (VBA) code.

For each PivotTable you create, one of the associated options is whether to automatically update the PivotTable whenever you open your workbook.  I always recommend that you use this option if the source data is contained within the same workbook.

If you are using Excel 2007 or 2010, use the following work steps to make your PivotTable always refresh on file open:

If you are using Excel 2000, XP/2002, or 2003, use the following work steps to make your PivotTable always refresh on file open:

For the VBA portion, the following "event" code will force all PivotTables and PivotCharts on a given sheet to update as you activate that sheet.  The code should be placed in the ThisWorkbook module of your VBA Project.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
                          
                          ' By Patrick G. Matthews
                          
                          ' Feel free to use this code anywhere, as long as you attribute authorship and the URL where you
                          ' found it
                          
                          ' This event sub fires every time a sheet is selected.  The intent is to force a refresh of any
                          ' PivotTables/PivotCharts on that sheet, as it is possible that the source data may have changed.
                          ' Thus, this event attempts to ensure that the PT/PC is always in sync with its source data.
                          
                          ' Use care with this code if the data source is external; the frequent refreshes may not not be
                          ' appropriate in that instance
                          
                          ' Code assumes that the source data and the PivotTable/PivotChart are never on the same worksheet!
                          ' If the PT/PC are on the same worksheet as the source data, changes to the source data are NOT
                          ' automatically reflected until the Workbook_SheetActivate event fires again
                          
                          Dim PT As PivotTable
                          Dim ChtObj As ChartObject
                          Dim Cht As Chart
                          
                          ' Based on the type of sheet activated (Worksheet, Chart, Excel4MacroSheet), run the appropriate
                          ' code.  Please note that the Type property for a Chart will actually return the same value as
                          ' for Excel4Macro sheets:
                          ' http://excel.tips.net/Pages/T002538_Detecting_Types_of_Sheets_in_VBA.html
                          
                          Select Case Sh.Type
                              Case xlWorksheet
                                  
                                  ' Loop through any PTs present on worksheet and refresh
                                  
                                  For Each PT In Sh.PivotTables
                                      PT.RefreshTable
                                  Next
                                  
                                  ' Loop through ChartObjects on worksheet.  If any are PivotCharts, refresh the underlying
                                  ' PT (which will also refresh the PC)
                                  
                                  For Each ChtObj In Sh.ChartObjects
                                      If Not ChtObj.Chart.PivotLayout Is Nothing Then
                                          ChtObj.Chart.PivotLayout.PivotTable.RefreshTable
                                      End If
                                  Next
                              
                              Case Else
                                  
                                  ' As above, for Chart sheets, the Type property returns the same value as for Excel4Macro
                                  ' sheets.  Since this branch of the code gets both Chart and Excel4Macro sheets, we use
                                  ' the loop to avoid the error that would occur if we just tested Sh.PivotLayout directly.
                                  
                                  ' If the sheet really is a Chart, and if that Chart is a PC, then refresh the underlying
                                  ' PT (and thus the PC itself)
                                  
                                  For Each Cht In Me.Charts
                                      If Cht.Name = Sh.Name Then
                                          If Not Sh.PivotLayout Is Nothing Then
                                              Sh.PivotLayout.PivotTable.RefreshTable
                                          End If
                                          Exit For
                                      End If
                                  Next
                          End Select
                              
                      End Sub

Select all Open in new window

To add this code to your VBA Project:

You will have to ensure that macros are enabled for this code to run.  Please note that if you use this code in Excel 2007/2010, you cannot use the *.xlsx file format, as that file format does not support VBA.

Enabling macros in Excel 2007

Enabling macros in Excel 2003

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
                          
                          Dim PC As PivotCache
                          
                          For Each PC In Me.PivotCaches
                              PC.Refresh
                          Next
                          
                      End Sub

Select all Open in new window

Automating Set-Up for Your Self-Updating PivotTables and PivotCharts

If you are using a Personal Macro Workbook, you may want to add the code below to that file.  That code has two procedures:


For more information on creating a Personal Macro Workbook, please see: How do I create a PERSONAL.XLS(B) or Add-in

Sub CreateName_PivotRange()
                          
                          Dim MakeName As String
                          Dim RefersTo As String
                          Dim Numbers As Long
                          
                          MakeName = InputBox("What do you want to use for the Name?", "Create Dynamic Name", "PivotRange")
                          If Trim(MakeName) = "" Then
                              MsgBox "You did not enter a valid Name", vbCritical, "Aborting"
                              Exit Sub
                          End If
                          
                          Numbers = MsgBox("Are the values in Column A numeric or dates?  If yes, click Yes", vbQuestion + vbYesNo, _
                              "Data Type")
                          
                          On Error GoTo ErrHandler
                          
                          If Numbers = vbYes Then
                              RefersTo = "='" & ActiveSheet.Name & "'!$A$1:INDEX('" & ActiveSheet.Name & "'!$1:$" & ActiveSheet.Rows.Count & _
                                  ",MATCH(10^300,'" & ActiveSheet.Name & "'!$A:$A),MATCH(""ZZZZZZZZ"",'" & ActiveSheet.Name & "'!$1:$1))"
                          Else
                              RefersTo = "='" & ActiveSheet.Name & "'!$A$1:INDEX('" & ActiveSheet.Name & "'!$1:$" & ActiveSheet.Rows.Count & _
                                  ",MATCH(""ZZZZZZZZ"",'" & ActiveSheet.Name & "'!$A:$A),MATCH(""ZZZZZZZZ"",'" & ActiveSheet.Name & "'!$1:$1))"
                          End If
                          
                          ActiveWorkbook.Names.Add MakeName, RefersTo
                          
                          Exit Sub
                          
                      ErrHandler:
                          
                          MsgBox "Error #" & Err.Number & ", " & Err.Description, vbCritical, "Name Not Created"
                          
                      End Sub
                          
                      Sub AddRefreshPTEventSub()
                          
                          ' This sub creates a Workbook_SheetActivate event sub useful for enforcing PivotTable/PivotChart
                          ' updates on sheet selection.  The code creates the event sub in the currently active workbook.
                          
                          ' For this code to work, "Trust access to VB Project" must be enabled.  In Excel 2003 and 2002,
                          ' to enable this, go to Tools|Macro|Security in the menu, and on the Trusted Publishers tab, check
                          ' Trust Access to Visual Basic Project.
                          
                          ' In Excel 2007, click the Office button, and click Excel Options.  Select Trust Center, and click
                          ' Trust Center Settings.  Check the box for Trust access to the VBA project object model
                          
                          Dim Line As Long
                          Dim arr(0 To 62) As String
                          
                          ' Populate array with the code for the sub, without the first line or the End Sub line
                          
                          arr(0) = "    "
                          arr(1) = "    ' By Patrick G. Matthews"
                          arr(2) = "    "
                          arr(3) = "    ' Feel free to use this code anywhere,  as long as you attribute authorship and the URL where you"
                          arr(4) = "    ' found it"
                          arr(5) = "    "
                          arr(6) = "    ' This event sub fires every time a sheet is selected.  The intent is to force a refresh of any"
                          arr(7) = "    ' PivotTables/PivotCharts on that sheet,  as it is possible that the source data may have changed."
                          arr(8) = "    ' Thus this event attempts to ensure that the PT/PC is always in sync with its source data."
                          arr(9) = "    "
                          arr(10) = "    ' Use care with this code if the data source is external; the frequent refreshes may not not be"
                          arr(11) = "    ' appropriate in that instance"
                          arr(12) = "    "
                          arr(13) = "    ' Code assumes that the source data and the PivotTable/PivotChart are never on the same worksheet!"
                          arr(14) = "    ' If the PT/PC are on the same worksheet as the source data, changes to the source data are NOT"
                          arr(15) = "    ' automatically reflected until the Workbook_SheetActivate event fires again"
                          arr(16) = "    "
                          arr(17) = "    Dim PT As PivotTable"
                          arr(18) = "    Dim ChtObj As ChartObject"
                          arr(19) = "    Dim Cht As Chart"
                          arr(20) = "    "
                          arr(21) = "    ' Based on the type of sheet activated (Worksheet, Chart, Excel4MacroSheet), run the appropriate"
                          arr(22) = "    ' code.  Please note that the Type property for a Chart will actually return the same value as"
                          arr(23) = "    ' for Excel4Macro sheets:"
                          arr(24) = "    ' http://excel.tips.net/Pages/T002538_Detecting_Types_of_Sheets_in_VBA.html"
                          arr(25) = "    "
                          arr(26) = "    Select Case Sh.Type"
                          arr(27) = "        Case xlWorksheet"
                          arr(28) = "            "
                          arr(29) = "            ' Loop through any PTs present on worksheet and refresh"
                          arr(30) = "            "
                          arr(31) = "            For Each PT In Sh.PivotTables"
                          arr(32) = "                PT.RefreshTable"
                          arr(33) = "            Next"
                          arr(34) = "            "
                          arr(35) = "            ' Loop through ChartObjects on worksheet.  If any are PivotCharts, refresh the underlying"
                          arr(36) = "            ' PT (which will also refresh the PC)"
                          arr(37) = "            "
                          arr(38) = "            For Each ChtObj In Sh.ChartObjects"
                          arr(39) = "                If Not ChtObj.Chart.PivotLayout Is Nothing Then"
                          arr(40) = "                    ChtObj.Chart.PivotLayout.PivotTable.RefreshTable"
                          arr(41) = "                End If"
                          arr(42) = "            Next"
                          arr(43) = "        "
                          arr(44) = "        Case Else"
                          arr(45) = "            "
                          arr(46) = "            ' As above, for Chart sheets, the Type property returns the same value as for Excel4Macro"
                          arr(47) = "            ' sheets.  Since this branch of the code gets both Chart and Excel4Macro sheets, we use"
                          arr(48) = "            ' the loop to avoid the error that would occur if we just tested Sh.PivotLayout directly."
                          arr(49) = "            "
                          arr(50) = "            ' If the sheet really is a Chart, and if that Chart is a PC, then refresh the underlying"
                          arr(51) = "            ' PT (and thus the PC itself)"
                          arr(52) = "            "
                          arr(53) = "            For Each Cht In Me.Charts"
                          arr(54) = "                If Cht.Name = Sh.Name Then"
                          arr(55) = "                    If Not Sh.PivotLayout Is Nothing Then"
                          arr(56) = "                        Sh.PivotLayout.PivotTable.RefreshTable"
                          arr(57) = "                    End If"
                          arr(58) = "                    Exit For"
                          arr(59) = "                End If"
                          arr(60) = "            Next"
                          arr(61) = "    End Select"
                          arr(62) = "    "
                          
                          With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
                              
                              ' Create a "stub" for the event sub.  Line captures the line number in the module for the first line
                              
                              Line = .CreateEventProc("SheetActivate", "Workbook")
                              
                              ' Advance the line count
                              
                              Line = Line + 1
                              
                              ' Insert the lines from the array
                              
                              .InsertLines Line, Join(arr, vbCrLf)
                              
                              .DeleteLines Line + UBound(arr) + 1
                              
                          End With
                          
                      End Sub

Select all Open in new window

Sample Files

Below please find two sample files:

These files demonstrate the use of a Table (Expandable PivotTable.xlsm) as the PivotTable source, and a dynamic Name as the source (both files).  Both files also include the Workbook_SheetActivate code supplied above to provide for the automatic PivotTable/PivotChart updates.

Expandable-PivotTable.xls
Expandable-PivotTable.xlsm


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=