Refresh data in a PivotTable or PivotChart report

 Note   If the report is based on an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), refreshing it completely rebuilds the cube file from the 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.) server and may take as long as initial creation of the cube file.

  1. Click the report.
  2. On the PivotTable toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click Refresh Data Button image.
  3. If you want Microsoft Excel to refresh the report automatically when you open the workbook, set this option.

ShowHow?

  1. On the PivotTable toolbar, click PivotTable or PivotChart, and then click Table Options or Options.
  2. Select the Refresh on open check box.

Security Note   ¬†Setting this option can pose as a security risk as arbitrary SQL scripts can be executed upon automatic refreshing of reports.

  1. If the report is based on external data, and you want Excel to refresh it at timed intervals, set this option.

ShowHow?

  1. On the PivotTable toolbar, click PivotTable or PivotChart, and then click Table Options or Options.
  2. Select the Refresh every check box, and then enter the interval you want in the minutes box.

 Note   When you refresh a PivotChart report or its associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), some chart formatting may be lost.

ShowTip

If your database prompts for a password every time you refresh, you can have Excel save the password so you won't have to enter it repeatedly. On the PivotTable toolbar, click PivotTable or PivotChart, click Table Options or Options, and then select the Save password check box. This option saves the password as readable text, so if security is a greater concern than convenience, you may not want to use this option.

 
 
Applies to:
Excel 2003