Introduction
A pivot table allows you to effortlessly study vast volumes of data. However, as we all know, data may and often does alter. Here’s how to refresh a pivot table in Excel to ensure that your data is up to date.
You may update the data in your pivot table manually or automatically, regardless of whether it comes from an external source or the same worksheet. You may also alter a setting to prevent the formatting from changing when you update the table.
Manually Refresh a Pivot Table
If you want to manually update your pivot table as required, begin by choosing the table.
Navigate to the PivotTable Analyze tab. In the Data area of the ribbon, choose the Refresh drop-down arrow.
Select “Refresh” to refresh the specified pivot table. Choose “Update All” to refresh all pivot tables in your worksheet.
You may also right-click the pivot table and choose “Refresh” from the shortcut menu.
If the update takes a while, you may check the status by selecting Refresh > Refresh Status. To cancel, go to Refresh > Cancel Refresh.
Automatically Refresh a Pivot Table
Perhaps you’d like that your pivot table update every time you open the spreadsheet. This is a fantastic technique to ensure that your data is always up to date and saves you from having to remember to manually update the table.
Navigate to the PivotTable Analyze tab after selecting the pivot table. Use the PivotTable drop-down arrow on the left to get to Options > Options.
Select the Data tab in the PivotTable Options window. Then, check the Refresh Data When Opening the File box. “OK” will be shown.
Prevent Formatting Changes When You Update
When data is updated, it may be bigger than your column width or longer than your row height. It’s a simple option to keep the formatting for your columns and rows while refreshing a pivot table.
Navigate to the PivotTable Analyze tab after selecting the pivot table. Use the PivotTable drop-down arrow on the left side, then select Options > Options.
Select the Layout & Format tab in the PivotTable Options box. Then, choose the Preserve Cell Formatting on Update checkbox. You may also tick the item just above for Autofit Column Widths on Update if you like. “OK” will be shown.
Keep your data current and up to date by manually or automatically updating your pivot table in Excel.
