Category: Refresh

Refreshing When a File Opens

In Excel 2003, if your pivot table source data changes frequently, you might want to ensure that the pivot table is updated as soon as the file opens.

You can set a pivot table option to refresh the pivot table automatically:

  1. Right-click a cell in the pivot table, and choose Table Options.
  2. Under Data options, add a checkmark to Refresh on open.
  3. Click OK to close the PivotTable Options dialog box.

Tip: To stop a long refresh, as the file opens press the Esc key.

___________________________

For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.

Refreshing a Pivot Table on a Protected Sheet

In Excel 2003, if you protect a worksheet, you’ll see a list of options, with specific items that can be allowed.

You can add a checkmark to “Use PivotTable reports”, so that people will be able to use the existing pivot tables on the worksheet.

protect worksheet settings

However, once the sheet is protected, you can’t create a new pivot table. Also, you won’t be able to refresh a pivot table on the worksheet, because that feature is disabled on a protected sheet. On the PivotTable toolbar, you can see that the Refresh button, and other features, are disabled.

pivot table toolbar with buttons disabled

Refresh the Pivot Table on a Protected Sheet

To refresh the pivot table on a protected worksheet,

  • temporarily unprotect the worksheet,
  • refresh the pivot table,
  • protect the sheet.

You can do this manually, or record a macro, then run that macro to refresh the pivot table.

More Protection Tips

There are more pivot table protection tips on my Contextures website.

___________________________