When you ask Microsoft 365 Copilot to explain a formula in an Excel worksheet that is protected, Copilot typically responds with an error message or no output at all. This problem occurs because Copilot requires read access to the cell content, and sheet protection blocks that access for all users except those with explicit permissions. In this article, you will learn the exact technical reason for this failure and a step-by-step fix to restore Copilot’s formula explanation feature without compromising overall sheet security.
Key Takeaways: Fix Copilot Formula Explanation on Protected Sheets
- Review > Protect Sheet > Select unlocked cells: Copilot must have this permission to read formula text from a locked cell.
- Home > Format > Lock Cell toggle: Use this to mark specific cells as unlocked so Copilot can explain formulas in those cells while the sheet stays protected.
- File > Info > Protect Workbook > Encrypt with Password: If the workbook itself is encrypted, Copilot cannot read any content at all; remove encryption for Copilot to work.
Why Copilot Cannot Explain a Formula in a Protected Sheet
Excel sheet protection works by restricting what users and add-ins can do in a worksheet. When you protect a sheet, all cells are locked by default. Locked cells cannot be selected, edited, or read by external processes unless the protection settings explicitly allow it. Copilot relies on the same Excel Object Model that other add-ins use. If the sheet protection does not grant the “Select locked cells” permission, Copilot cannot access the cell’s formula text. Without reading the formula, Copilot cannot explain it.
There are two layers of protection that can block Copilot:
- Worksheet-level protection: Applied via Review > Protect Sheet. By default, this locks all cells and prevents selection of locked cells. Copilot needs the “Select locked cells” checkbox enabled to read formulas.
- Workbook-level protection: Applied via File > Info > Protect Workbook > Encrypt with Password. This encrypts the entire file. Copilot cannot decrypt the file and therefore cannot read any formula in any cell.
The fix focuses on worksheet-level protection because that is the most common scenario. Workbook encryption is rare for shared business files, but it is a hard blocker.
Steps to Allow Copilot to Explain Formulas in a Protected Sheet
The following steps modify the protection settings of the sheet without removing protection entirely. You need edit permissions on the workbook to perform these steps. If you are not the owner, ask the owner to apply these changes.
Method 1: Adjust Sheet Protection to Allow Copilot Access
This method keeps the sheet protected but grants Copilot the specific permission it needs.
- Unprotect the sheet
Go to the Review tab on the ribbon and click Unprotect Sheet. If a password was set, enter it. The sheet is now unprotected and all cells are editable. - Open the Protect Sheet dialog
Click Review > Protect Sheet again. Do not click Protect yet. The Protect Sheet dialog box appears with a list of checkboxes. - Enable Select locked cells
In the list under “Allow all users of this worksheet to:”, check the box next to “Select locked cells”. This permission allows Copilot to read the formula text in locked cells. - Set a password and protect
Enter a password in the “Password to unprotect sheet” field if you want to prevent others from changing the protection. Click OK. Confirm the password. The sheet is now protected, and Copilot can explain formulas in locked cells.
Method 2: Unlock Specific Cells for Copilot Access
Use this method if you want to keep locked cells completely unselectable for users but allow Copilot to explain formulas in only a few cells. This requires unlocking those specific cells before protecting the sheet.
- Unprotect the sheet
Review > Unprotect Sheet. Enter the password if prompted. - Select the cells with formulas to explain
Click and drag to select the cells that contain formulas you want Copilot to explain. Hold Ctrl to select non-adjacent cells. - Unlock the selected cells
Right-click the selected area and choose Format Cells. Go to the Protection tab. Uncheck the box labeled “Locked”. Click OK. These cells are now unlocked and will remain editable even after protection is applied. - Protect the sheet with default settings
Review > Protect Sheet. Leave the default checkboxes as they are. Do not check “Select locked cells”. Enter a password if desired and click OK. Now only the unlocked cells are selectable, and Copilot can explain formulas in those cells. Locked cells remain unselectable for all users.
If Copilot Still Cannot Explain Formulas After the Fix
Copilot Returns “Cannot access cell content”
This error typically indicates that the workbook is encrypted at the file level. Go to File > Info > Protect Workbook. If you see “Encrypt with Password” highlighted, the file is encrypted. Click Remove Password. Enter the password to decrypt. Save the file. Copilot will then be able to read all formulas.
Copilot Explains a Formula But the Result Is Incorrect
If Copilot provides an explanation but the formula references cells outside the protected sheet, Copilot may not have access to those cells. Ensure that all referenced sheets are either unprotected or have the same protection settings that allow selection of locked cells. Alternatively, move the formula to a single sheet.
Copilot Does Not Show the Explain Formula Option
The Explain Formula feature in Copilot is available only in the Excel desktop app and Excel for the web. It is not available in Excel for mobile or in the Excel add-in for Teams. Open the workbook in the desktop app or in a browser to see the option.
Protected Sheet with Copilot Access vs Standard Protected Sheet: Key Differences
| Item | Protected Sheet with Copilot Access | Standard Protected Sheet |
|---|---|---|
| Select locked cells permission | Enabled | Disabled by default |
| Copilot formula explanation | Works for all locked cells | Blocked |
| User ability to select locked cells | Allowed | Blocked |
| User ability to edit locked cells | Blocked | Blocked |
| Best use case | Collaborative review with Copilot | Read-only distribution to users |
You can now configure sheet protection to allow Copilot to explain formulas without removing security. Start by applying Method 1 to your most-used workbook. For sensitive cells that must remain unselectable, use Method 2 to unlock only the cells containing formulas you want explained. As an advanced tip, combine both methods: protect the sheet with “Select locked cells” enabled, then use conditional formatting to highlight cells with formulas so users know where to click for an explanation.