Refresh PivotTable data

When a PivotTable report is connected to another PivotTable in the same workbook or in another workbook, or to another external data source, such as a Microsoft Access or SQL Server database, or an Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) cube, you can perform a refresh operation to retrieve any data updates. Each time you refresh the PivotTable data, you’ll see the most recent version of the data, including changes that were made to the data since it was last refreshed.

By default, PivotTables are not refreshed automatically, but you can specify that the PivotTable is automatically refreshed when you open the workbook that contains the PivotTable.

What do you want to do?


Refresh PivotTable data manually

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options and a Design tab.

  1. On the Options tab, in the Data group, do one of the following:
  • To update the information to match the data source, click the Refresh button, or press ALT+F5.

You can also right-click the PivotTable, and then click Refresh.

  • To refresh all PivotTables in the workbook, click the Refresh button arrow, and then click Refresh All.

Excel Ribbon Image

 Note    If you experience display or formatting changes when you refresh the PivotTable report, make sure that the Autofit column width on update and Preserve cell formatting on update check boxes are selected on the Layout & Format tab in the PivotTable Options dialog box (PivotTable Tools, Options tab, PivotTable group, Options command).

Top of Page Top of Page

View the refresh status or cancel the refresh

After starting a refresh, you can review the status or cancel it at any time.

  • On the Options tab, in the Data group, click the Refresh button arrow, and then click Refresh Status to view the status or Cancel Refresh to stop refreshing.

Excel Ribbon Image

Top of Page Top of Page

Refresh PivotTable data automatically when opening the workbook

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options and a Design tab.

  1. On the Options tab, in the PivotTable group, click Options.

PivotTable group on the Options tab under PivotTable Tools

  1. In the PivotTable Options dialog box, on the Data tab, select the Refresh data when opening the file check box.

Top of Page Top of Page

 
 
Applies to:
Excel 2010