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: However, in some cases the source data may be dynamic rather than static: 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 the Data Source Dynamic If you are creating a PivotTable, you should always consider whether the source data layout for that PivotTable is stable. If you are using Excel or , you are probably best served by creating a Table for your source data.
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.
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: If the values in Column A are text If you are using Excel or and wish to use a dynamic Name instead of a Table, use the following work steps: 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 or later, if that current region is part of a Table, Excel will pre-populate with the Table's name.
If you created a Name for the PivotTable's source, then enter that Name here. PivotTables by nature are "re-sizable" objects: 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 or , use the following work steps to make your PivotTable always refresh on file open: 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.
Please note that the Type property for a Chart will actually return 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.
Name Then If Not Sh. 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. Enabling macros in Excel Note: 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. That code has two procedures: In Excel , click the Office button, and click Excel Options.
Check the box for Trust access to the VBA project object model. For more information on creating a Personal Macro Workbook, please see: The code creates the event sub in the currently active workbook. CodeModule ' Create a "stub" for the event sub.