Update (refresh) data in a PivotTable

At any time, you can click Refresh to update the data for the PivotTables in your workbook. You can refresh the data for PivotTables connected to external data, such as a database (SQL Server, Oracle, Access, or other), Analysis Services cube, data feed, and many other sources. You can also refresh data from a source table in the same or a different workbook. And you can set your workbook to refresh its PivotTable data automatically when you open it.

To learn more about refreshing various kinds of external data, see the following articles:

Manually refresh

To manually refresh your PivotTable or PivotTables, do the following:

  1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

PivotTable Tools

  1. Click Analyze > Refresh, or press Alt+F5.

Refresh button on the Analyze tab

 Tip    To update all PivotTables in your workbook at once, click Analyze > Refresh arrow > Refresh All.

  1. If refreshing takes longer than you expect, click Analyze > Refresh arrow > Refresh Status to check the refresh status.

To stop refreshing, click Cancel Refresh.

Prevent column widths and cell formatting from adjusting

If the column widths and cell formatting of your data adjust when you refresh the PivotTable data, and you don’t want that to happen, make sure the following options are checked:

  1. Click Analyze > Options.

Options button on the Analyze tab

  1. On the Layout & Format tab, check the Autofit column widths on update and Preserve cell formatting on update boxes.

Refresh data automatically when opening the workbook

To set an option to automatically refresh PivotTable data when you open your workbook, do the following:

  1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

PivotTable Tools

  1. Click Analyze > Options.

Options button on the Analyze tab

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

More about PivotTables

 
 
Applies to:
Excel 2013, Power BI for Office 365