Home Work How to Refresh a Pivot Table in Microsoft Excel

How to Refresh a Pivot Table in Microsoft Excel

by Kevin

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.

Refresh or Refresh All for a pivot table

You may also right-click the pivot table and choose “Refresh” from the shortcut menu.

Refresh in 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.

Open the pivot table Options

Select the Data tab in the PivotTable Options window. Then, check the Refresh Data When Opening the File box. “OK” will be shown.

Refresh when opening the file

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.

Preserve pivot table formatting

Keep your data current and up to date by manually or automatically updating your pivot table in Excel.

You may also like

Navhow is dedicated to teaching people all over the world how to do anything.

 

Subscribe

The Best How-To Newsletter Anywhere

©2022 Navhow, All Right Reserved