To lock cells in Excel, select the cells, open Format Cells, check Locked, then go to Review > Protect Sheet. The Locked setting only takes effect after you protect the worksheet. To keep some cells editable, unlock those cells before protecting the sheet.
What You’ll Need
- A workbook open in Excel for Microsoft 365 on Windows. The steps are similar in desktop Excel for Mac, though some shortcuts and dialog layouts differ.
- Permission to change the worksheet’s protection settings. If the sheet is already protected, you may need its password to change the cell settings.
- Optionally, a password you can store safely. A sheet-protection password helps prevent casual changes, but it is not a substitute for encrypting a sensitive workbook.
Step-by-Step
- Decide what should be editable. Make a quick list of cells people should be allowed to change, such as blank entry fields, and cells that should stay protected, such as formulas, headings, or lookup values. Excel marks cells as locked by default, but that setting does not restrict editing until you protect the sheet. If the goal is to protect every cell, you can usually skip ahead to protecting the sheet.
- Save a working copy. Use File > Save As to create a copy before changing protection, especially if this workbook is shared or important. Protection settings can affect how people enter data, sort, filter, and format the sheet. A separate copy gives you a safe place to check the setup before replacing or sharing the original.
- Unlock the cells people may edit. If only certain cells should remain editable, first select the whole worksheet by clicking the Select All button at the intersection of the row numbers and column letters. On Windows, press Ctrl+1 to open Format Cells; on Mac, press Command+1. Open the Protection tab, clear the Locked checkbox, and select OK. This makes the whole sheet editable for now; protection will be added later.
- Unlock specific entry cells. Select the cells or ranges that users should be able to change. To select separate ranges, hold Ctrl while selecting them on Windows; on Mac, hold Command. Open Format Cells again, choose Protection, clear Locked, and select OK. If the cells are already unlocked, there is no harm in checking their settings. Leave formula cells and other content you want to protect locked.
- Lock the cells that must not change. Select the cells you want to protect, open Format Cells, and on the Protection tab make sure Locked is checked. Select OK. For a sheet with many formulas, you can select formula cells using Home > Find & Select > Go To Special, choose Formulas, and select OK. Then apply the Locked setting to that selection. Check the selection before changing its settings so you do not accidentally lock entry fields as well.
- Protect the worksheet. Go to Review > Protect Sheet. In the dialog, you can enter a password or leave it blank. Choose the actions that users should still be allowed to perform, such as selecting unlocked cells, using AutoFilter, or sorting, where those options are available. For a basic input form, allow selecting unlocked cells and leave other permissions off unless the sheet needs them. Select OK. If you entered a password, Excel may ask you to confirm it; enter it again exactly.
- Test both protected and editable cells. Try changing one cell that should be locked and one cell that should be editable. Excel should block the first change and allow the second. Also test any important workflow, such as entering data in a form or filtering a table. If users should not even land on locked cells when navigating, review the Select locked cells option in the protection dialog; this affects selection, not whether the cells are protected.
- Save and share the workbook. Save after testing. Tell collaborators which cells they can edit and whether a password is required to remove protection. If you later need to change which cells are editable, go to Review > Unprotect Sheet, enter the password if prompted, adjust the Locked settings, and protect the sheet again.
If you want every cell protected, you do not need to unlock the whole sheet and relock individual cells. Since cells are locked by default, go straight to Review > Protect Sheet, choose the allowed actions, and test the result. The unlock-first approach is for sheets where selected input cells must remain editable.
Common Mistakes
- Setting Locked but not protecting the sheet. This is the most common reason a locked cell can still be edited. Locked is only a cell property; Review > Protect Sheet activates it.
- Unlocking the wrong range. If the whole sheet was unlocked to prepare it, make sure you relock the cells that should be protected before turning on sheet protection. Test a formula cell and an input cell separately.
- Expecting a password to encrypt the file. Worksheet protection is intended to control editing, not to secure confidential data against determined access. For file access security, use Excel’s workbook encryption option under File > Info > Protect Workbook > Encrypt with Password, and keep the password secure.
- Protecting the workbook instead of the worksheet. Protect Sheet controls editing on the current sheet. Protect Workbook is a different feature, commonly used to restrict structural changes such as adding, deleting, or moving sheets.
- Blocking a task users still need. Protection can prevent sorting, filtering, formatting, or selecting cells, depending on the options chosen. Set only the permissions the worksheet requires, then test them while protection is on.
Tips
- Make entry cells obvious. Use a consistent fill color, borders, or a short instruction such as Enter information in shaded cells. Formatting does not control protection, but it helps people identify editable areas.
- Use a clear pattern for formulas. For example, unlock only the cells where people enter quantities and leave calculated totals locked. When formulas are scattered, Go To Special can help select formula cells, but inspect the selection before applying protection settings.
- Consider hiding formulas separately. If you want formulas not to appear in the formula bar, select the formula cells, open Format Cells > Protection, and check Hidden as well as Locked. Then protect the sheet. Hidden formulas are not concealed until sheet protection is active, and this is not strong secrecy for sensitive calculations.
- Choose protection permissions deliberately. If users must use an existing filter, allow Use AutoFilter when protecting the sheet. Excel may require the filter to be set up before protection. Test sorting and filtering on the protected sheet because the available actions depend on the sheet and the options selected.
- Keep a record of passwords. Store the protection password in an approved password manager or another secure place. Excel cannot reliably recover a forgotten worksheet-protection password through the normal interface.
- Use desktop Excel for the most precise setup. Excel for the web has sheet-protection controls under Review, but its interface and available options can differ from desktop Excel. If you cannot find a setting or need detailed control over permitted actions, open the workbook in the desktop app.
Troubleshooting
- You can still edit a cell that should be locked: Check Review > Protect Sheet. If the sheet is not protected, turn protection on. If it is protected, unprotect it, select the cell, open Format Cells > Protection, and confirm Locked is checked. Protect the sheet again and retest.
- You cannot edit a cell that should be an input: Unprotect the sheet, select the cell, and clear Locked under Format Cells > Protection. Then protect the sheet again. Confirm the cell is not part of a range you intentionally kept locked.
- Protect Sheet is unavailable or settings cannot be changed: The worksheet may already be protected, or the workbook may be in a state that limits editing. Check Review for Unprotect Sheet and use the password if requested. If the file is read-only or opened with restricted permissions, save an editable copy or ask the owner for access.
- Users cannot sort or filter: Unprotect the sheet, set up the filter if needed, and protect it again with the relevant sorting or AutoFilter permission enabled. The exact options depend on the desktop or web interface, so test the actual actions users need.
- You forgot the password: Excel’s normal worksheet-protection controls require the password to unprotect a sheet. Look for a legitimate saved copy or ask the workbook owner. Do not rely on protection as the only copy of important data or as a secure storage method.
Frequently Asked Questions
Do I have to lock cells before protecting a sheet?
Cells are locked by default, so you can protect the sheet without changing cell settings if every cell should be protected. If some cells must remain editable, unlock those cells before you protect the sheet.
Can I lock only certain cells and leave the rest editable?
Yes. Select the whole worksheet and clear Locked, then select the cells to protect and check Locked. Finish by choosing Review > Protect Sheet. Test a cell from each group.
Can people edit unlocked cells when the sheet is protected?
Yes. Unlocked cells remain editable while the worksheet is protected, as long as the workbook is not also restricted in a way that prevents those edits.
Does locking cells prevent someone from viewing the data?
No. Worksheet protection is an editing control, not a confidentiality or encryption feature. Use file encryption and appropriate access controls for sensitive information.
Can I lock cells in Excel for Mac or Excel for the web?
Yes, though the controls can look different. In Excel for Mac, Format Cells is commonly opened with Command+1, and sheet protection is on the Review tab. Excel for the web provides protection controls under Review; use desktop Excel if you need options not shown in the web interface.
What is the difference between Locked and Hidden?
Locked prevents editing after the sheet is protected. Hidden suppresses a cell’s formula in the formula bar after protection is applied. Neither setting takes effect as intended until the worksheet is protected.
Related
- Clean Excel Workbook: A Practical Guide to Cleaner Data
- Clean Excel Duplicates: Find, Review, and Remove Them
- Clean Excel Data With Power Automate: A Practical Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Multiple Freeze Panes in Excel: What You Can and Can’t DoYou can freeze multiple rows and columns in Excel at the same time, but you cannot create several…
- Microsoft PowerPoint Slide Numbers: How to Add ThemTo add slide numbers in Microsoft PowerPoint for Microsoft 365 on Windows, select Insert > Slide Number, check…
- How to Archive Email in the Outlook App on Any DeviceTo archive an email in Outlook, select the message and choose Archive (usually the box-shaped icon). Outlook removes…
- Freeze a Group of Rows in Excel: Step-by-Step GuideTo keep several rows visible while you scroll in Excel for Microsoft 365 on Windows, select the first…
- Loan Amortization Schedule in Google Sheets: How to BuildTo make a loan amortization schedule in Google Sheets, enter the loan amount, annual interest rate, and term,…
- How to Apply a Filter in Google Sheets: Narrow DataTo filter data in Google Sheets in a web browser, select your table, choose Data > Create a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.