PivotTables VIII: “Defer Layout Update”, or adding multiple fields in one fell swoop
In this article, I’ll cover a small yet very useful PivotTable feature – deferring PivotTable updates. In current versions of Excel, PivotTables are updated each time a field is added, removed, or moved to a different position. For PivotTables based on large data sets, these actions can take some time to complete, meaning, for example, you end up waiting 7 times if you create a new a PivotTable and add 7 fields.
To address this scenario, we have added a feature to Excel 12 that allows you to control when the PivotTable is updated. As some of you may have noticed in screenshots in previous blogs, there is a checkbox and a button at the bottom of the field list.
"Defer Layout Update" controls
When this checkbox is checked, the “Update” button next to it becomes enabled, and now you can add, remove and move fields around without the PivotTable updating itself. When you are done arranging fields, you press the Update button, and the PivotTable does one and only one update.
Here’s an example. I created a new PivotTable, checked the Defer Layout Update check box, and then added four fields. At this point, my PivotTable is still empty. Now, I’ll press the “Update” button to apply the changes.
(Click to enlarge)
And the PivotTable is modified with a single update.
(Click to enlarge)
By the way, the label “Defer Layout Update” is a work in progress, so if you have a suggestion for something which better conveys to you what this feature is all about, please post your suggestions as comments here. We are currently evaluating different names for this feature and would love any feedback you might have.
In my next post I will start a series of topics covering PivotTables based on OLAP data sources. Specifically I’ll be highlighting support for all the great new features of Microsoft SQL Server 2005 Analysis Services.
PS Updated title to reflect correct numbering (part 8, not part 7)
