<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

How to Automatically Update Your PivotTables and PivotCharts

Published on
78,961 Points
34,561 Views
34 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
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:
The number of rows in the source data set will not change
The number of columns in the source data set will not change
The contents of the source data set will not 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:
The number of rows in the source data set may change
The number of columns in the source data set may change
The contents of the source data set may change

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:
Making sure the PivotTable/PivotChart source ranges are dynamic and constantly updated
Forcing frequent updates for the PivotTables/PivotCharts via VBA code

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.

Note: This article assumes that the data source for your PivotTables/PivotCharts will always be on some worksheet in the same workbook as the PivotTable/PivotChart.  For PivotTables/PivotCharts based on external data sources, frequent updates may or may not be appropriate, depending on factors such as network capacity and the nature of the ultimate data source.


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:
Make sure that your data has headings in the first row, and then one or more rows of "detail" data, which may include formulas.  Do not leave any blank rows in your Table range!
From the Ribbon's Insert tab, click Table in the Tables group.  Excel will pre-populate the dialog box with the current region
When Excel creates your Table, it will assign a "placeholder" name such as Table1, and you will see a new Table Tools/Design tab on the Ribbon.  In the Properties group on that tab, change the name of your Table to something more meaningful

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):
Select Insert|Name|Define from the menu
In the define name dialog, enter your Name (I usually use PivotRange or the like)
In the Refers to box, enter a formula such as:
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))

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:
On the Ribbon, go to the Formulas tab, and click Name Manger or Define Name in the Defined Names group
In the New Name dialog, enter your Name (I usually use PivotRange or the like), and give it a scope of Workbook
In the Refers to box, enter a formula such as:
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))

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:

PivotTable Source Form
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!
PivotTables by nature are "re-sizable" objects: as you change the layout of row/column fields and/or the number of items in each field changes, upon refreshing PivotTables automatically re-size on the worksheet to accommodate the changes
Since PivotTables will automatically re-size, if you have anything in your worksheet below or to the right of the PivotTable, these items are in danger of being overwritten
If your source data are themselves dynamic, and thus you may see the number of rows and columns of source data changing over time, not placing the PivotTable on the same worksheet as the source data mitigates the risk that the source data may impinge on the space occupied by the PivotTable
If you are using a dynamic Name to define the source range for your PivotTable, placing the PivotTable on the same worksheet may interfere with the formulas used to determine the dynamic range

 

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:
PivotTable Options - Excel 2007
Right-click on the PivotTable, and select PivotTable Options from the pop-up menu; or on the Ribbon's PivotTable Tools|Options tab, click Options in the PivotTable group
On the PivotTable Options dialog, click the Data tab
Check the box for Refresh data when opening the file

If you are using Excel 2000, XP/2002, or 2003, use the following work steps to make your PivotTable always refresh on file open:
PivotTable Options - Excel 2003
Right-click on the PivotTable, and select PivotTable Options from the pop-up menu; or on the floating PivotTable toolbar, select PivotTable Options
On the PivotTable Options dialog, check the box for Refresh data when opening the file

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

Open in new window


To add this code to your VBA Project:
From Excel, hit Alt+F11 to get to the VB Editor
In the Project Explorer, find the workbook you want to add the code to.  Expand the node for Microsoft Excel Objects, and double-click ThisWorkbook to open the module
Paste the code above into that module

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

Note: If you place your PivotTable/PivotChart on the same worksheet as the source data--which, as above, is NOT recommended--and you change the source data, this change will not be automatically reflected in the PivotTable/PivotChart, unless you activate a different sheet and then return to that worksheet (which will force the Workbook_SheetActivate event to fire).

Note: If desired, you could also use this shorter and simpler code to force the PivotTable/PivotChart refreshes.  The key difference is that in this procedure, whenever a sheet is activated, ALL PivotCaches (and thus all PivotTables/PivotCharts) will be updated, regardless of whether the sheet being activated actually has a PivotTable/PivotChart on it.  For most implementations that is probably not a problem.  However, for workbooks with lots of PivotCaches, these frequent updates may present performance problems.  Your mileage, as they say, may vary.

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

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:
CreateName_PivotRange creates a dynamic Name for you on the active worksheet, using whatever Name you supply.  This range will always start in A1 of the active worksheet, and will look for the last populated cells in Column A and Row 1 to determine the endpoint of the range dynamically
AddRefreshPTEventSub automatically adds the Workbook_SheetActivate code described above to the active workbook

Note: For AddRefreshPTEventSub to work, you must allow programmatic access to the VBA project.  In Excel 2003 and 2002, 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.

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

Open in new window



Sample Files


Below please find two sample files:
Expandable PivotTable.xls
Expandable PivotTable.xlsm

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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
34
8 Comments
LVL 16

Expert Comment

by:Jerry Paladino
Very Nice article Patrick!  
Jerry
0
LVL 2

Expert Comment

by:rvfowler2
Great article.  Will take awhile to absorb it all.
0
LVL 85

Expert Comment

by:Rory Archibald
Just imagine the "if only I could remember where"s
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Expert Comment

by:Bright01
MathewsPatrick,

Great article!  As a "novice" I will need to play with this today to get it to work and for me to understand.  While the article is excellent, I do not have access (when I click on it to the sample XLSM file.  Can you send it to me or tell me where I can go to find it??

Thank you,

B.
0
LVL 93

Author Comment

by:Patrick Matthews
Bright01,

So glad you liked the article!  I just tested the download links for both sample files using both IE 8 and FireFox 10, and they work for me, so I am not sure what the problem is.

I can email you the files.  Drop me a line; my email address is available in the About Me tab of my profile.

Patrick
0
LVL 1

Expert Comment

by:Stephen Byrom
Very helpful article. The "Yes" vote thingy isn't working though :(
0

Expert Comment

by:Rayne
This is very very helpful, thank you again for yet another awesome article :)
0
LVL 67

Expert Comment

by:Jim Horn
Excellent article.

In Excel 2010 I have this firing on a button click, and it's running like a champ, but the refresh only occurs when I'm doing step-through debugging.
0

Featured Post

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month