Excel for Mac: Protect Hidden Sheets Without Disabling Power Query
Question details
The user needs to protect hidden Excel calculation sheets and intellectual property on Mac while allowing users to enter inputs and run Power Query.
- Product
- Microsoft Excel for Mac with Power Query
- Device & OS
- macOS (version not provided)
- Scenario
- A workbook contains hidden calculation logic, and users must be able to edit designated source values, launch Power Query, and upload or refresh source files.
- Observed behavior
- Excel sheet and workbook protection cannot fully secure hidden calculation logic while preserving every Power Query capability. Protection is best used to control editing and navigation rather than as strong intellectual-property security.
Confirm which cells or named ranges users must edit and verify that Power Query is available in the installed Excel for Mac version. Identify the calculation sheets, query input cells, and source-file locations before applying protection.
Protect designated input cells while keeping Power Query workflows available
Use this approach when users need to run queries and upload source files but should not edit calculation formulas or hidden worksheets.
Connect each query to user-editable cells or named ranges instead of requiring users to edit the query definition. Then protect the calculation area and hide the supporting sheets. This limits accidental changes, but workbook protection should not be treated as a complete method for hiding intellectual property.
On the worksheet used for user inputs, select the cells users may change, open Format Cells, choose the Protection tab, and clear Locked.
Select each input area, open Formulas > Name Manager or Define Name, and assign clear names such as SourceFilePath or StartDate for use by Power Query.
Open Data > Get Data or the available Power Query command, edit the query, and configure its source or filtering logic to read the designated cells or named ranges instead of exposing query-editing tasks to users.
Select the calculation worksheet, choose Format > Sheet > Hide or use the sheet tab context menu, and hide the worksheets containing formulas or supporting logic.
Use Review > Protect Sheet to prevent edits to locked cells, then use Review > Protect Workbook and enable structure protection if available. Leave the specific input cells unlocked so users can continue entering values and refreshing queries.
Separate the user input workbook from the calculation workbook
Use this design when protecting formulas inside one workbook is not sufficient and users only need to provide inputs or source files.
Use WPS Office for Lightweight Excel-Compatible Workbook Editing
WPS Office is a free, lightweight alternative for opening, editing, and sharing common Excel workbooks with a familiar interface. It supports Microsoft Excel formats for smoother migration, but it does not replace every Excel for Mac Power Query capability, so retain Excel where advanced query workflows are required.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the main launcher.
- 2. Open the Excel workbook: Choose Menu > Open, select the XLSX or XLS workbook, and review the hidden sheets, formulas, and editable input areas.
- 3. Protect editable areas: Use the Review tab to locate sheet-protection tools, leave required input cells available for editing, and protect formula areas where supported.
- 4. Save in an Excel-compatible format: Choose File > Save As and select Excel 2007–365 (*.xlsx) when the workbook must be shared with Microsoft Excel users.

Frequently Asked Questions
Can I protect hidden calculation sheets and still let users refresh Power Query?
Usually, you can protect the worksheet and leave designated input cells unlocked, but Power Query actions may require access to specific tables, ranges, connections, or source files. Test the complete refresh workflow in the target Excel for Mac version before distributing the workbook.
How should Power Query read values that users are allowed to change?
Place the permitted values in unlocked cells or an Excel table, assign them names through Formulas > Name Manager, and configure the query to read those named cells or ranges. Users can then change inputs without editing query steps.
Does hiding an Excel sheet prevent users from viewing its formulas?
No. Hiding and protecting sheets mainly control normal editing and navigation. They should not be considered strong intellectual-property protection because determined users may be able to inspect or recover workbook content.
What is the strongest way to protect proprietary Excel calculation logic?
Keep the calculation workbook or service in a restricted location and distribute only an input workbook or controlled output. Use file-level encryption and access permissions when users must not receive the underlying formulas or queries.




