Usually, Excel cannot refresh data into locked cells on a protected worksheet: sheet protection has no general permission that says “allow refresh.” For a dependable workflow, refresh while the destination sheet is unprotected and protect it again afterward, or load the refreshed data to a separate sheet that users can edit or view without changing protected formulas.
What sheet protection does—and does not do
In Excel for Microsoft 365 on Windows, choose Review > Protect Sheet to restrict changes to a worksheet. You can set a password and choose permitted actions, such as selecting locked cells, sorting, or using AutoFilter. These options govern user interaction with the sheet; they are not a universal exception for a query, connection, or other process that needs to write refreshed results into locked cells.
That distinction matters because a refresh is not merely a read operation. Excel may need to replace values, add or remove rows, update a table, or change a PivotTable layout. If the destination is protected, the refresh can fail, partially update, or behave differently depending on the data source and output type. Do not assume that allowing users to select cells, use filters, or edit a range also authorizes a refresh.
Protection is also not encryption or strong access control. It is useful for preventing accidental edits to formulas and layout, but it should not be treated as a way to conceal sensitive data or prevent a determined person from accessing workbook contents. Use appropriate file permissions and encryption for confidentiality.
Why the data source and destination matter
“Refresh” can refer to several different Excel features, and their write-back behavior is not identical. Power Query commonly loads results into a worksheet table or the Data Model. A legacy external data range or a connection may update cells directly. A PivotTable refresh can update its displayed summary, while a chart may simply reflect changes in its source. In each case, the sheet that receives the changed results is important.
- Power Query loaded to a worksheet: refreshing may need to resize or rewrite the output table. A protected destination can prevent those changes, especially when the result gains or loses rows.
- Power Query loaded to the Data Model: the query data is stored in the workbook’s model rather than as a conventional result table on that sheet. A PivotTable or report that displays it still has its own worksheet-protection considerations.
- PivotTables: the protection dialog includes a
Use PivotTable reportsoption. It permits certain PivotTable interactions, but it should not be confused with a general guarantee that every underlying connection or refresh will succeed on a protected sheet. - External connections: credentials, source availability, privacy settings, and connection properties can prevent a refresh regardless of sheet protection.
For example, suppose a query returns 500 sales records today and 540 tomorrow. Even if the existing result cells appear editable through a particular protection setting, Excel may need to expand the table by 40 rows. That structural change is a common reason a refresh behaves differently from a simple edit to one unlocked cell.
Reliable ways to refresh protected workbooks
Choose a workflow based on whether people need to edit the output, whether the output must stay on the protected sheet, and whether refresh should be manual or automatic. The simplest options are:
- Unprotect, refresh, protect again. Choose
Review > Unprotect Sheet, enter the password if prompted, runData > Refresh All, check the results, then useReview > Protect Sheetagain. This is the clearest manual process when only a trusted person performs refreshes. - Separate the data from the presentation. Load query results to a dedicated data sheet, and keep formulas, instructions, or dashboards on a different protected sheet. Protect only the presentation sheet if the refresh does not write to it. This is usually the best design for shared workbooks.
- Use a refresh copy or controlled owner workflow. Keep an editable source workbook for the person responsible for updates, then publish a protected output copy. This can help when users should view reports but should not control connections or refresh results.
Before relying on any approach, test with a copy of the workbook and a refresh that changes the row count. A test that only replaces existing values may miss the table-expansion problem. Also confirm that the query has completed before reapplying protection; some connections can refresh in the background.
Setting up Power Query with a separate output sheet
Power Query is often easiest to manage when its destination is separate from the user-facing report. In Excel for Windows, open the query through Data > Queries & Connections. From the query’s context menu, choose Load To when available, then select a worksheet destination or the Data Model as appropriate. Exact options can vary with the query and how it was created.
A practical layout might put the query table on a sheet named Data and the dashboard on a sheet named Report. Refresh the query on Data, and have the Report sheet display or summarize those results. Protect Report to prevent accidental changes to formulas and layout. If the query output itself must be protected from direct editing, test the refresh behavior on that sheet before distributing the workbook; do not assume protection will permit a future table resize.
For a connection that refreshes on opening, inspect the query or connection properties and test the workbook after saving, closing, and reopening it. Automatic refresh depends on the source, connection settings, credentials, and Excel environment. It does not bypass worksheet protection reliably. Excel for the web also has different refresh support depending on the data source, and it does not run VBA macros.
PivotTables and the Use PivotTable reports option
If the protected sheet contains a PivotTable, select Review > Protect Sheet and consider enabling Use PivotTable reports. That permission is specifically about using PivotTables on the protected worksheet. It is not the same as unlocking every cell, and it does not remove the need to test how refreshing the PivotTable interacts with the workbook’s connection and source.
Try the exact operation your users need: refreshing the PivotTable, changing a filter, expanding a field, and refreshing the underlying source if applicable. A PivotTable whose source is already available in the workbook may behave differently from one that depends on a connection that must fetch external data. If refresh still fails, unprotect the sheet for the update or move the PivotTable to a sheet designed for refresh and interaction.
Remember that allowing PivotTable use can give users more control over the report than simply viewing it. If the goal is a fixed, read-only presentation, consider publishing a static copy or using a separate protected report sheet rather than granting interactive PivotTable permissions.
Automating unprotect and refresh with VBA
For a desktop workflow where authorized users need one-click refresh, a macro can unprotect a sheet, refresh the workbook, wait for asynchronous queries where applicable, and protect the sheet again. This is more dependable than expecting protection settings to let a refresh write to locked cells. The macro should handle errors so the sheet is not accidentally left unprotected, and the password should not be treated as secret merely because it appears in VBA.
Macros require a macro-enabled workbook and suitable security settings. Excel for the web cannot run VBA. On Mac, Excel supports VBA, but some connection types and automation behavior differ from Windows, so test the complete process in the environment where the workbook will be used. Do not distribute a macro as a way to enforce security; users may disable macros, and worksheet protection is not a security boundary.
Save a separate copy before running or changing a macro. Macro actions cannot be undone with Ctrl+Z, and a failed refresh or protection step can leave the workbook in an unexpected state. For a team workbook, a documented manual refresh procedure may be safer than custom automation.
Common problems and checks
If a refresh fails, first identify which sheet receives the output and whether the failure occurs during the query, table update, or PivotTable update. Then check these points:
- Unprotect the destination temporarily and retry. If that works, protection is involved; if it does not, investigate the connection or source.
- Check whether the result gained or lost rows, columns, or fields. A changing output shape is more demanding than replacing values in an unchanged range.
- Confirm the source is reachable and that required credentials are available. A password prompt, expired login, or unavailable network location can look like a refresh problem.
- For
Refresh All, check whether the connection is configured to participate in that operation and whether a background refresh is still running. - Check that a PivotTable’s source, query, or Data Model is current. Refreshing a report and refreshing the data feeding it are related but not always the same operation.
A message saying the worksheet is protected points toward a destination-permission issue. A credential, privacy, or connection error points elsewhere. Separate diagnosis prevents unnecessary changes to passwords or protection settings.
Choosing the right design
For a workbook updated by one owner, manual unprotect-refresh-reprotect is simple and transparent. For a shared workbook with recurring refreshes, separate data and presentation sheets usually reduce conflicts and make it clear which parts are user-editable. If users need to filter or pivot the refreshed data themselves, keep that interaction in mind when choosing what to protect and which PivotTable permissions to allow.
Use sheet protection to prevent accidental edits, not to keep source data confidential or to make an automated refresh safe by itself. Keep a backup, document who performs refreshes, and test after changes to the query, its output columns, or the workbook’s Excel version. These precautions matter especially when a report is refreshed on a schedule or distributed to many people.
Frequently Asked Questions
Can I protect an Excel sheet and still refresh Power Query?
Sometimes a specific setup may refresh, but Excel has no universal sheet-protection permission that guarantees it. If Power Query writes to locked cells or changes the output table’s size, refresh can fail. Test the exact query and use an unprotected destination or unprotect-refresh-reprotect workflow if needed.
Does Allow Users to Edit Ranges let a query refresh?
Not as a general rule. That feature grants users editing rights to specified ranges; it is not a dedicated permission for Power Query or connection refreshes. A query may need to change table structure or cells outside the permitted range.
Will Refresh All work on a protected worksheet?
It depends on what each connection refreshes and where its results are written. Data > Refresh All can refresh several items, so one protected destination may cause a problem even if other connections succeed. Test each output and review connection settings.
Can a PivotTable refresh while its sheet is protected?
The Use PivotTable reports option allows PivotTable use on a protected sheet, but it is not a guarantee for every source-refresh combination. Try the needed refresh and interactions in a copy, or refresh while unprotected.
Can Excel for the web refresh a protected workbook?
Some workbook connections can refresh in Excel for the web, while support varies by source and feature. The web version cannot run VBA, so a macro-based unprotect-refresh-reprotect routine requires desktop Excel. Check the source’s web support and test the workbook in the intended environment.
Related
- Excel Power Query Connection Only Explained
- Stampin’ Up Inventory Spreadsheet: What to Track
- Spreadsheet Free Budget Planner: A Simple Excel Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Highlight a Box in ExcelTo highlight a box in Excel, select the cell or rectangular range, then choose Home > Fill Color…
- Excel Merge Cells Without Losing Data: Safe MethodsExcel’s Merge & Center command does not combine the contents of two cells: it keeps the upper-left cell’s…
- How to Copy an Image in Excel: Clear Steps and TipsTo copy an image in Excel for Microsoft 365 on Windows, click the image’s edge to select it,…
- How to Add an MLA Header in Word (Step by Step)To add an MLA header in Word, insert an automatic page number at the top right of the…
- Excel Shortcut for Filling a Cell YellowIn Excel for Microsoft 365 on Windows, press Alt, then H, then H to open the Fill Color…
- Excel Merge Zones: 11 Ways to Combine Cells and DataFor a visual heading, select the cells and choose Home > Merge & Center; to combine information without…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.