Yes. In Excel for Microsoft 365 on Windows, you can usually refresh a PivotTable while its worksheet is protected by selecting Use PivotTable reports when you protect the sheet. This allows PivotTable use without giving users unrestricted permission to edit the worksheet, though refresh can still be blocked by other workbook, data-source, or layout constraints.
What sheet protection allows
Worksheet protection is a set of permissions for a particular sheet. It can prevent accidental changes to cells, formulas, and objects while allowing selected actions. It is not the same as protecting the workbook’s structure, and it does not turn a worksheet into a secure place to store confidential information.
The relevant permission is Use PivotTable reports, which appears in the Protect Sheet options in desktop Excel. With it enabled, a user can work with PivotTable reports, including refreshing them in supported circumstances. Without it, Excel generally blocks PivotTable actions on the protected sheet. The permission is not a blanket grant to edit every cell or freely redesign the report.
Whether a refresh succeeds also depends on factors outside sheet protection. Excel must be able to access the PivotTable’s source data, any required credentials or connection, and a usable destination for the refreshed report. A protected worksheet setting cannot resolve a missing file, an expired sign-in, or a report that cannot expand into its surrounding cells.
How to protect the sheet and allow refresh
In Excel for Microsoft 365 on Windows, use these steps:
- Open the worksheet containing the PivotTable. If the sheet is already protected, choose Review > Unprotect Sheet first and enter the password if prompted.
- Choose Review > Protect Sheet.
- In the protection dialog, select Use PivotTable reports under the actions users are allowed to perform. Select any other permissions the users need, such as selecting locked or unlocked cells.
- Optionally enter a password, confirm it if Excel asks, and choose OK.
- Test the result by selecting the PivotTable and choosing PivotTable Analyze > Refresh, or right-clicking inside the report and choosing Refresh.
If the source is a query or external connection, test the actual refresh path users will use. Data > Refresh All may refresh connections and PivotTables across the workbook, and it can reveal a connection or permission issue that a single-PivotTable refresh does not.
On Excel for Mac, the sheet-protection controls are also on the Review tab, but dialog layout and available choices can vary by release. Look for the PivotTable-report permission in the protection options. Excel for the web has different capabilities and may not expose all the same protection and connection controls; when the exact permission or refresh behavior matters, configure and test the workbook in desktop Excel.
What users can do after protection
Allowing PivotTable reports does not mean users can edit the worksheet as if it were unprotected. Sheet protection continues to restrict ordinary changes according to the selected permissions. The PivotTable permission is intended to let users work with a report, rather than grant general access to locked cells.
For example, a workbook owner might protect a monthly sales sheet so formulas and headings cannot be overwritten, but allow users to refresh a PivotTable built from a sales table. A user can update the report after new rows are added to its source, while the rest of the protected worksheet remains guarded against routine edits.
Other interactions, such as rearranging fields, changing report layout, sorting, filtering, or using slicers, can depend on the permission selected, the PivotTable type, and the Excel version. Test the specific tasks users need instead of assuming that refresh permission includes every design or filtering action. If users need to build or substantially restructure reports, a protected sheet may be the wrong arrangement.
Refresh is different from changing the source
A PivotTable refresh reads its source again and updates the report. It does not necessarily change which cells, table, connection, or external source the PivotTable uses. For a PivotTable based on an Excel table, adding records to that table and refreshing is a common workflow. For a report based on a fixed cell range, new records outside that range might not be included unless the source range is expanded or changed.
Refresh also does not guarantee that all external data is current. The workbook may need a network connection, valid credentials, permission to access a database, or access to a source file. A connection can have its own refresh settings or restrictions. If Excel reports that it cannot access or authenticate to the source, changing sheet protection alone will not fix the underlying issue.
There is a further practical distinction between refreshing and making room for the result. A refresh can change the number of rows or columns displayed. If other content occupies the area into which the PivotTable needs to expand, Excel may report that it cannot complete the operation. Keep the report’s surrounding area clear and avoid placing fixed notes or other tables directly beside a PivotTable that can grow.
Limits and permissions to consider
Before distributing a protected workbook, decide which actions are essential. A user who only needs to update a report may need fewer permissions than someone who also filters it or changes its layout. Grant only the necessary options, then test with a normal user account or a separate copy of the workbook.
- Selection permissions: Choose whether users can select locked cells, unlocked cells, or both. This affects navigation and interaction with the sheet, not access to the source data.
- PivotTable actions: Enable Use PivotTable reports if users must work with or refresh reports on the protected sheet. Check filtering and layout tasks separately.
- Objects and controls: Charts, slicers, and other objects can have separate protection behavior. Allowing users to edit objects may permit more than slicer interaction, so test before enabling it broadly.
- Workbook structure: Protecting workbook structure is a separate feature that restricts actions such as adding, deleting, or moving sheets. It does not replace the PivotTable permission on the worksheet.
Passwords for worksheet protection are useful for preventing casual changes, but worksheet protection should not be treated as strong data security. A user who must not see source records should not receive a workbook containing those records merely because a sheet is protected. Control access to the file and data source appropriately.
Common refresh problems and fixes
If refresh is unavailable or fails, first identify whether the problem is sheet protection, the PivotTable, or its data source. Try these checks in order:
- Confirm the permission: Unprotect the sheet, protect it again, and verify that Use PivotTable reports is selected. Re-protecting is necessary if the original protection settings did not include it.
- Check whether the PivotTable refreshes unprotected: If it still fails after you unprotect the sheet, investigate the source, connection, or report itself instead of changing protection options repeatedly.
- Look for a source or sign-in error: Confirm that the source file or database is reachable and that the user has the required access. A workbook owner’s successful refresh does not prove that every recipient has the same access.
- Check the report’s footprint: Clear nearby cells that could block the PivotTable from growing, and check for merged cells or other layout obstacles in the destination area.
- Separate workbook-level protection: If workbook structure is protected, that is a different setting. It may affect sheet management but does not substitute for enabling the PivotTable-report option on the sheet itself.
- Test the intended command: Try right-clicking inside the PivotTable and choosing Refresh, then test Data > Refresh All if the workbook relies on connections. Note which operation fails and the message Excel displays.
If the report must refresh reliably but the protected-sheet permission does not work for a particular workbook or connection, a controlled alternative is to let an authorized person unprotect, refresh, and protect the sheet again. That adds a manual step and requires access to the protection password. Automating the process with a macro is another possible design, but it introduces macro security and maintenance considerations and should be tested on a copy before use.
When to use another arrangement
Sheet protection is a reasonable fit when the main goal is to prevent accidental edits while still letting people update a report. It is less suitable when users need broad PivotTable redesign, when several external connections require different credentials, or when the workbook must enforce strict access to underlying records.
Alternatives include keeping source data and reporting sheets separate, providing a dedicated unprotected reporting sheet, or having a designated workbook owner refresh and distribute the result. If users need to explore the data extensively, a reporting tool or a separately permissioned data source may fit better than trying to make one protected worksheet serve as both a locked form and a flexible analysis workspace.
Frequently Asked Questions
Can I refresh a PivotTable without unprotecting the sheet?
Usually, yes. Protect the sheet with Use PivotTable reports selected, then try the PivotTable’s Refresh command. A blocked source, connection, or report layout can still prevent the update.
Where is the Use PivotTable reports option?
In desktop Excel for Microsoft 365 on Windows, choose Review > Protect Sheet. The option appears among the actions allowed for users in the protection dialog. If the sheet is already protected, unprotect it before changing the protection settings.
Does this let users change the PivotTable layout?
Not necessarily. The option permits PivotTable report use, but it does not make every layout, field, filter, or object action available in every setup. Test the precise tasks users need with the actual workbook.
Why does refresh still fail when the option is checked?
Check whether the source is reachable, credentials are valid, the user has access, and the refreshed report has space to expand. If the PivotTable also fails on an unprotected sheet, the cause is probably not worksheet protection.
Does protecting the sheet hide or secure the source data?
No. Sheet protection controls editing actions; it is not a reliable confidentiality measure. Do not distribute a workbook to someone who must not see its underlying data.
Can I protect the sheet but let users refresh with Refresh All?
It can work when the sheet permission and workbook connections allow it, but Refresh All may also update other queries or connections. Test it in the intended workbook and verify that users have access to every source it needs.
Related
- Tool Inventory Spreadsheet: Setup, Tracking, and Tips
- Excel Power Query: How to Create a Connection Only Query
- The Knot Wedding Budget Spreadsheet: What to Know
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Microsoft Excel Always Not Responding? Causes and FixesNo—Microsoft Excel does not always become unresponsive because it is broken. A brief freeze can happen while a…
- How to Add Automatic Page Numbers in Microsoft WordTo add automatic page numbers in Word, open the Insert tab, choose Page Number, select where the numbers…
- How to Sum in Excel Using the Keyboard: Quick StepsTo sum a column quickly in Excel for Microsoft 365 on Windows, select the blank cell directly below…
- Track Changes in Excel Online: View and Review EditsTo track edits in Excel Online, open the workbook and choose Review > Show Changes. Excel for the…
- Share Your Outlook Calendar: Steps and PermissionsTo share an Outlook calendar, open Calendar, choose Share Calendar, select the calendar, enter the person’s email address,…
- CSV vs Excel File Size: Why the Difference MattersCSV files are usually smaller than Excel workbooks when they contain the same plain table of data, because…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.