Excel’s Protect Sheet options control what people can do on a worksheet after it is protected, such as selecting cells, formatting rows, sorting, or using an existing filter. In Microsoft 365 for Windows, choose Review > Protect Sheet, select the actions to allow, and optionally set a password; protection mainly prevents accidental changes, not deliberate access to workbook data.
What worksheet protection does
Worksheet protection works with each cell’s Locked property. Most cells are marked locked by default, but that setting has no effect until you protect the sheet. Once protection is active, Excel restricts editing or other actions according to the choices in the Protect Sheet dialog. Cells you unlock beforehand remain available for entry, subject to the other permissions you grant.
This makes protection useful for a form, a forecast template, or a shared report. For example, a budget sheet might let colleagues enter quantities in a few input cells while keeping formulas and headings from accidental edits. It does not make the worksheet read-only in every sense: users may still be able to select cells, copy visible information, or perform any action you permitted.
Protection is applied one worksheet at a time. A workbook with several tabs may therefore have some protected sheets and some unprotected ones. Protecting a sheet is different from protecting the workbook’s structure or encrypting the file; those options address different risks.
Where to find the options
In Excel for Microsoft 365 on Windows, open the worksheet and select Review > Protect Sheet. The dialog includes a password field and a list of allowed actions. The first checkbox, usually worded “Protect worksheet and contents of locked cells,” turns on protection. Clear that checkbox to leave the sheet unprotected.
To protect a sheet, follow these steps:
- Decide which cells should accept input, and unlock those cells before turning on protection.
- Select Review > Protect Sheet.
- Optionally enter a password. If you set one, keep it somewhere safe; Excel will ask for it when someone tries to remove protection.
- Check only the permissions users need, then select OK. If you entered a password, confirm it when prompted.
- Try the sheet as a user would: enter data in an input cell, then check that a formula cell or other restricted action is blocked.
On Excel for Mac, use the Review tab and choose Protect Sheet; the dialog and available choices can differ slightly by version. Excel for the web also has sheet-protection controls, but its interface and management capabilities are not identical to the Windows desktop version. If you need to configure detailed permissions, use the desktop app and verify the result in the version your audience uses.
What each Protect Sheet checkbox allows
The dialog’s checkboxes grant specific actions. A checked option is allowed; an unchecked option is blocked on the protected sheet, subject to Excel’s other rules and the workbook’s design.
- Select locked cells: Lets users select cells marked locked. Selection alone does not let them change the contents.
- Select unlocked cells: Lets users move to and select cells that are available for entry. This is usually useful on an input form.
- Format cells: Allows changes to cell formatting, such as number formats, fonts, borders, and fills.
- Format columns: Allows column formatting actions, including changing column width or hiding columns.
- Format rows: Allows row formatting actions, including changing row height or hiding rows.
- Insert columns: Permits users to insert worksheet columns.
- Insert rows: Permits users to insert worksheet rows.
- Insert hyperlinks: Permits adding hyperlinks to cells.
- Delete columns: Permits deleting columns. This does not automatically mean users can delete every column: cell locks and the selection being deleted can affect what Excel allows.
- Delete rows: Permits deleting rows, with similar restrictions around locked cells.
- Sort: Permits sorting, but Excel may prevent a sort if the range to be sorted contains locked cells. Test the intended sort on a protected copy.
- Use AutoFilter: Permits users to change criteria in an existing AutoFilter. Set up the filter before protecting the sheet; this permission does not generally let users add or remove the filter itself.
- Use PivotTable & PivotChart reports: Permits use of PivotTable and PivotChart reports on the protected sheet. It does not grant access to restricted source data or override connection and refresh requirements.
- Edit objects: Permits changes to objects such as charts, shapes, and controls. Leave it unchecked if those items should not be moved or altered.
- Edit scenarios: Permits changes to scenarios created with Excel’s Scenario Manager.
The exact wording or availability of an item can vary with Excel version and worksheet contents. The permission list is not a universal set of features: allowing an action does not guarantee it will work if another restriction, such as a locked cell in a sort range, prevents it.
How to choose permissions for a real worksheet
Start with the tasks a reader must complete, rather than checking options simply because they sound convenient. A sales-entry form might need unlocked input cells and permission to select unlocked cells, but not formatting, row deletion, or object editing. A filtered report might need selection and AutoFilter use, while keeping the underlying values and layout protected.
Consider these common setups:
- Input form: Unlock only the fields people should fill in. Allow selection of unlocked cells, and usually leave formatting and row or column changes disabled.
- Review worksheet: Allow selection of locked cells if readers need to inspect formulas or copy values, while withholding permission to edit them.
- Filtered list: Create the AutoFilter first, then allow Use AutoFilter. If users must sort, allow Sort and confirm that the intended range can be sorted while protected.
- Presentation sheet: Keep formatting, object editing, and structural changes disabled so charts and layout are less likely to be altered.
It is often better to grant the fewest permissions that support the workflow. Extra permissions can make a sheet easier to use, but also increase the chance of changes to its layout or contents. Test the specific actions people need before distributing the workbook.
Unlock input cells before protecting
To make only selected cells editable, select those cells and open Home > Format > Format Cells. In the Format Cells dialog, open the Protection tab, clear Locked, and select OK. The Windows shortcut Ctrl+1 opens Format Cells; on Mac, use Command+1. After you protect the sheet, those unlocked cells can be edited while locked cells are governed by the permissions you chose.
For a worksheet with many input areas, group the intended cells before changing the Locked setting. Then protect the sheet and test both an allowed cell and a cell that should remain restricted. A frequent mistake is to protect the sheet first and assume that a blank-looking cell will be editable. Its appearance does not determine its Locked property.
For more controlled workflows, Excel’s Review > Allow Edit Ranges feature can define ranges that users may edit on a protected sheet. Depending on the setup and Excel environment, access can be controlled with a range password or permissions. This is useful when several people need different editable areas, but it requires careful setup and should be tested with the accounts that will use the workbook.
Limitations with sorting, filters, and formulas
Some permissions have practical conditions. For example, checking Sort does not override the fact that Excel may refuse to sort a range containing locked cells. A protected sheet with an existing filter can allow users to change filter criteria, but the filter should be in place before protection. If a workflow depends on sorting or filtering, build a representative sample, protect it, and test the action rather than relying only on the checkbox label.
Formulas are also not automatically concealed just because a sheet is protected. A user who can select a formula cell may be able to see its formula in the formula bar. To hide a formula from display in Excel, select the formula cells, open Format Cells > Protection, check Hidden, and then protect the sheet. This hides the formula in the formula bar while the sheet is protected; it is not encryption and should not be treated as a way to safeguard confidential logic or data.
Protection is not encryption or workbook structure protection
Excel’s worksheet protection is intended mainly to prevent accidental edits and limit routine actions. It is not a robust security boundary for sensitive information. Do not rely on it to keep a determined recipient from accessing data in a workbook. If someone should not see particular information, do not include that information in a file shared with them.
Other protections serve different purposes:
- Protect Sheet restricts actions on one worksheet, based on cell locks and allowed actions.
- Protect Workbook can protect workbook structure, such as preventing sheets from being added, deleted, moved, or renamed. It does not replace sheet protection.
- Encrypt with Password protects access to the workbook file. In Windows desktop Excel, this is available through File > Info > Protect Workbook > Encrypt with Password. Keep the password secure; losing it can prevent access to the file.
Choose the protection that matches the problem. A shared template that needs to preserve formulas may need sheet protection. A workbook whose tabs should not be rearranged may also need structure protection. A file containing sensitive information needs access controls appropriate to that information, not merely a protected worksheet.
Common problems and checks
If a user can still change a cell, check whether the sheet is actually protected and whether that cell was unlocked. If users cannot type in intended input cells, inspect the Locked setting and confirm that Select unlocked cells is allowed where needed. If a sorting or filter action fails, check whether the filter was created before protection and whether the relevant range contains locked cells.
If Excel asks for a password to unprotect the sheet, it is the password set for that sheet. Excel does not provide a simple recovery option if it is forgotten, so record it securely and retain an unprotected backup when appropriate. Also remember that protection may be removed by someone who knows the password; it does not identify users or create an audit trail of edits.
Before sharing, test the file in a copy and use the same Excel environment your recipients are likely to use. Differences between desktop, Mac, and web versions can matter for managing protection and for particular worksheet features. Clear instructions beside input areas can also prevent confusion about which cells are meant to be edited.
Frequently Asked Questions
Can I protect a sheet but leave some cells editable?
Yes. Clear the Locked setting for the intended cells in Format Cells, then protect the worksheet. Leave other cells locked, and allow users to select unlocked cells if they need to move between input fields.
Does Protect Sheet stop someone from viewing my formulas?
No, not by itself. You can mark formula cells Hidden and then protect the sheet to hide formulas from the formula bar, but worksheet protection is not encryption and is not suitable for securing confidential information.
Why can’t users sort a protected sheet when Sort is checked?
The permission allows sorting only when Excel’s other conditions are satisfied. A sort can be blocked when the range contains locked cells. Test the exact protected range and consider whether users need a different sheet design or an unprotected copy for sorting.
Can users filter a protected worksheet?
They can usually change criteria in an existing AutoFilter if Use AutoFilter is allowed. Set up the filter before protecting the sheet; the option does not generally grant permission to create or remove the filter.
What if I forget the sheet-protection password?
Excel does not offer a straightforward password-recovery process for removing sheet protection. Keep a secure record of passwords and maintain an appropriate backup. Do not confuse a worksheet password with the password used to encrypt a workbook file.
Related
- Uniform Inventory Spreadsheet: Build a Useful Tracker
- Excel Power Query for Data Cleaning: A Practical Guide
- Uni Budget Spreadsheet: Build a Student Budget in Excel
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 Save an Excel File in Google SheetsTo save an Excel workbook in Google Sheets, upload the file to Google Drive, open it with Google…
- Excel Insert Row Shortcut on macOS: Keys and OptionsIn Excel for Mac, select the row where the new row should go, then press Control+Shift+= (the plus…
- How to Add Color to a Dropdown List in Excel with RulesTo add color to a dropdown selection in Excel, keep the dropdown list as it is and use…
- Excel Data Validation List: Create a Yes/No DropdownYes—Excel can use Data Validation to create a Yes/No dropdown in a cell. Select the cells, open Data…
- Highlight Duplicates in Google Sheets: Both MatchesTo highlight both the original and repeated values in Google Sheets, select the cells, open Format > Conditional…
- Excel Solver Simplex: When to Use It and How It WorksExcel Solver’s Simplex LP method finds the best value for a linear objective while respecting linear constraints. Choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.