How to Allow Excel Table Editing While Restricting Power Query Changes
Question details
The user needs to let team members edit worksheet tables and refresh imported data while preventing them from modifying Power Query definitions or workbook connections.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An Excel workbook with Power Query-imported tables is shared with users who should update cell data and refresh results but not change the underlying query logic.
- Observed behavior
- Workbook protection can restrict query changes while still allowing data entry and refresh, depending on workbook design and Excel version.
Open the workbook in Excel and identify whether the queries are stored in the Data Model or as connection-only queries. Then unlock the worksheet cells or ranges that users are allowed to edit before applying workbook protection.
Protect the Workbook Structure to Restrict Power Query Changes
Use this method when the workbook is shared in Microsoft Excel and you want to keep data cells editable while locking query-related workbook structure.
Workbook protection with the Structure option protects sheets and connections from being moved, deleted, hidden, or changed, and helps prevent standard users from altering query-related components. It is not a substitute for per-user file permissions on a network share.
Open the workbook in Microsoft Excel, click the Review tab, and review the current workbook protection status.
Click Protect Workbook, select Structure, enter a password, confirm the password, and click OK.
Select the worksheet ranges users may edit, right-click and choose Format Cells > Protection, then clear Locked before applying sheet protection.
Sign in with a standard Windows user account, edit a table cell, refresh the imported table, and open Queries & Connections to confirm query changes are blocked.
Use Worksheet Protection and File Permissions for Stronger Query Control
Use this defense-in-depth method when workbook protection alone does not fully lock Power Query access or when the file is stored on a shared drive.
Use WPS Office Spreadsheet as a Free Excel Alternative for Safe Table Editing
WPS Office Spreadsheet is a free, lightweight Microsoft Excel alternative that opens and saves .xlsx workbooks, preserves existing queries and connections, and provides a familiar ribbon interface for routine table editing and data refresh tasks. It is ideal for users who only need to update data without modifying Power Query logic or paying for Microsoft Office.
- 1. Download WPS Office: Go to wps.com and download WPS Office for Windows, macOS, or mobile.
- 2. Open the Excel workbook: Launch WPS Spreadsheet and open the .xlsx file that contains the imported tables and queries.
- 3. Edit table data: Make the required changes to unlocked table cells while leaving workbook connections and query definitions untouched.
- 4. Apply workbook protection if needed: Use Review > Protect Workbook in WPS Spreadsheet to protect sheet structure and control access.
Frequently Asked Questions
Does protecting an Excel workbook structure completely prevent users from editing Power Query queries?
Workbook protection can restrict many query-related changes, but the result depends on the Excel version and workbook design. Always test with a standard Windows user account and add file permissions for stronger control.
Can users still refresh imported tables after I protect the workbook?
Yes, in most Excel environments users can refresh existing queries and imported tables if they have the necessary data connection permissions and the query itself is not separately blocked.
Can WPS Office edit an Excel workbook that contains Power Query queries?
WPS Office can open and edit .xlsx worksheet data and preserves existing query connections. However, full Power Query editing and advanced query restriction controls remain available in Microsoft Excel.
What should I unlock before protecting a worksheet?
Select the cells or table ranges users need to edit, right-click and choose Format Cells > Protection, then clear Locked before applying Review > Protect Sheet.
Where do I test permissions for a standard user?
Log on with a non-administrator Windows account, open the workbook, attempt an allowed data edit and a restricted query change, and verify the protection behaves as expected.
