When you make any changes on Source Data, it is essential to ensure that your Pivot Table is updated. Below are the steps to automatically refresh Pivot Table Data and also the steps for manually refreshing the Pivot Table Data.
Refresh Pivot Table Data
In default Pivot Tables are in Excel aren’t set to be automatically refreshed. It is however possible to create the Pivot Table which will refresh the data automatically using PivotTable options.
If an Pivot Table is set to refresh its data it will refresh its data every time the worksheet that contains that Pivot Table is opened.
If you’re working with large quantities of data, then the automatic data refresh option could cause problems.
Thus, it’s better to refresh the data manually Pivot Table Data, in the case of worksheets with larger dimensions.
1. Automatically Refresh Pivot Table Data
Follow the instructions below to set up a Pivot Table to refresh automatically its Data
1. Right-click anywhere on the Pivot Table and then click PivotTable Options from the menu that opens.
2. On PivotTable Options screen, select the Data Tab and choose Refresh data upon opening the file.
3. Click on the OK button to save this modification.
Once you have this set it will show that the Pivot Table refreshing its Data at a regular interval, each time that you access the data file that includes Pivot Table.
2. Manually Refresh Pivot Table Data
If you’re dealing with huge Source Data, it is best to manually refresh Pivot Table and make sure that any changes made to your Source Data are reflected in the Pivot Table.
Right-click on any part of the pivot Table and click Refresh within the menu which opens.
Another method to manually Refresh the Pivot Table is to select the Analyze tab on the top menu bar and then select Refresh within the section Data.
You can use any of the methods above to refresh Pivot Table Data in order to to make sure that the changes made in Source Data are reflected in the Pivot Table.
Prevent Column Widths and Cell Formatting From Adjusting
When you refresh your Pivot Table You may notice the column widths and formatting of cells changing. It is possible to stop this from happening by selecting “Preserve Cell Formatting” option within Pivot Table Options.
1. Right-click on any part of your Pivot Table and then select Options for Pivot Table… from the menu menu that opens.
2. On PivotTable Options screen, select Layout and Format and then select the option to preserve cell formatting during updates as well as Autofit Column widths in update options.
3. Click on OK to save this setting.
In the following days, you won’t see any Pivot Table losing its cell format and column widths when it is Pivot Table data is refreshed.