Bacon Bits:

The frustrating part of building a pivot table from a large data source is that each time you add a field to the pivot area, you are left waiting while Excel crunches through all that data. This can become maddening if you have to add several fields to your pivot table. But did you know you can defer layout changes until you are ready to apply them?

.

That's right – you can activate this option by clicking the relatively inconspicuous Defer Layout Update check box on the PivotTable Field List dialog box

.

Here's how it works.

When you place a check in the Defer Layout Update check box, you prevent your pivot table from making real-time updates as you move your fields around without your pivot table. When you are ready to apply your changes, simply click the Update button on the lower-right corner of the PivotTable Field List dialog box.

.

Oh – and remember to remove the check from the Defer Layout Update check box when you are done building your pivot table. Leaving it checked prevents you from using the other features of the pivot table (sorting, filtering, grouping, etc).

.

If you're a complete Excel nerd, you're probably wondering if this can be applied via VBA. Why yes it can! In fact, you can greatly speed up your VBA procedures by applying it.

.

To defer updates in VBA, you set the ManualUpdate property of the PivotTable object to true. Then set it back to false when you're done.

Here's an example:

With Sheet1.PivotTables("PivotTable1")

.ManualUpdate= True

.PivotFields("PivotField1").Orientation = xlRowField

.PivotFields("PivotField2").Orientation = xlColumnField

.ManualUpdate= False

End With

.

Now if I can only defer my appetite.

• Previous Entry: Drinks that Look Like Urine
• Next Entry: The Lamest Apocalypse Ever