While the default option in Microsoft Excel is to allow users to manually refresh the Pivot Table, it also provides the option to set the Pivot Table to automatically refresh its data.
When a Pivot Table is set to refresh its data, it will automatically refresh its data, every time the worksheet that contains the Pivot Table is opened by the user.
However, you need to be aware that this option is only suitable if the Pivot Table is linked to a limited amount of data. If you are dealing with a large amount of data, the automatic data refresh option can slow down things.
1. Automatically Refresh Pivot Table Data
You can follow the steps below to set a Pivot Table to Automatically Refresh its Data
1. Right-click anywhere on the Pivot Table and click on PivotTable Options in the menu that appears.
2. On PivotTable Options screen, click on the Data Tab and select Refresh data when opening the file option.
3. Click on the OK button to save this change.
After this setting, you will find the Pivot Table refreshing its Data automatically, every-time you open the file containing Pivot Table.
2. Manually Refresh Pivot Table Data
If you are dealing with a large Data source, it is better to manually refresh Pivot Table and make sure that changes made in the Source Data are reflected in the Pivot Table.
Right-click anywhere on the Pivot Table and click on Refresh in the menu that appears.
Another way of to Manually Refresh Pivot Table is to click on the Analyze Tab in the top menu bar and click on Refresh option in the Data section.
You can make use of any of the above methods to Refresh Pivot Table Data and make sure that changes made in Source Data are reflected in the Pivot Table.
Prevent Column Widths and Cell Formatting From Adjusting
When you refresh Pivot Table, you may find column widths and cell formatting changing. You can prevent this from happening by choosing “Preserve Cell Formatting” option in Pivot Table Options.
1. Right-click anywhere on the Pivot Table and click on PivotTable Options… in the menu that appears.
2. On PivotTable Options screen, click on Layout & Format and select Preserve cell formatting on update and Auto-fit Column widths on update options.
3. Click on OK to preserve this setting.
After this, you won’t find the Pivot Table losing its cell formatting and column widths, whenever the Pivot Table data is refreshed.