We have learned that Pivot Tables automatically update when a field is added to the Rows, Columns, Filter, or Values area of Pivot. This isn’t feasible if you have larger datasets, since it may take a longer time to complete every update. This is where Excel Pivot Table has provided us with an amazing option of Defer Layout Updates. In simple words, you can add or move all the fields to the Pivot Table Layout i.e. complete the entire layout changes, and then you can re-calculate the pivot table.
So let us now learn how to control when our Pivot Tables need to be updated.
Defer Layout Update Option in Pivot Table
To activate the options click on the checkbox that says “Defer Layout Update” below the Pivot Table fields. This will now allow you to add, remove or modify your pivot Tables field without actually updating it.
How does it work?
Now that we have activated the option, let us understand how does this works. Below is a Pivot Table which contains Customer Names in rows and their respective Sales Amount, Region-wise in Columns.
Now if you wish to modify your pivot table by adding or moving more than one field, you can check the Defer Layout Update box. By doing this the Pivot Table won’t be auto-calculated. In the below example you can see we have added a new field as “Order Date” in the Rows, but the pivot has not auto-updated.
Once you are done with setting up the fields you can click on the “Update” button which is towards the right of the Defer Layout Update box, which will now update or recalculate the Pivot Table. You can remove the checkmark from the Defer Layout Tab as well.
You can now add, remove or modify fields as many times as you want and when you are done setting up the fields, you can finally click on the Update button. This is extremely helpful when you have larger data-sets wherein you require to change fields often.