How to Lock Cells in Excel to Protect Data

Locking cells in Excel prevents accidental or unauthorized edits to your data, formulas, and formatting. By default, all cells are locked, but the lock only takes effect when you protect the worksheet. This guide walks you through the process, from selecting the cells you want to keep editable to applying sheet protection with or without a password.

What You’ll Need

• A computer with Microsoft Excel (2010 or later, including Microsoft 365).
• The workbook file you want to protect.
• Optional: a password you want to use for unprotecting the sheet.

Step-by-Step

1. Open your workbook and navigate to the worksheet containing the cells you want to lock.
2. Select all cells by clicking the triangle at the top-left corner (where the row numbers and column letters meet) or pressing Ctrl+A (Windows) / Cmd+A (Mac).
3. Open the Format Cells dialog by right-clicking any selected cell and choosing Format Cells, or press Ctrl+1 / Cmd+1.
4. Go to the Protection tab and uncheck the Locked checkbox. Click OK. This unlocks every cell in the sheet, so you can later lock only the ones you want.
5. Select the cells you want to lock (e.g., cells containing formulas, headers, or critical data). You can select multiple non-adjacent cells by holding Ctrl while clicking.
6. Right-click the selection and choose Format Cells again. On the Protection tab, check the Locked checkbox. Click OK.
7. Now go to the Review tab on the ribbon and click Protect Sheet.
8. In the dialog, you can set a password (optional). If you set one, you’ll be asked to confirm it. Leave the password blank if you want anyone to be able to unprotect the sheet without a password.
9. Choose what users are allowed to do. By default, Select locked cells and Select unlocked cells are enabled. If you want users to be able to edit only the unlocked cells, keep those checked and uncheck everything else you don’t want them to do.
10. Click OK. If you set a password, confirm it in the pop-up. Your locked cells are now protected.

Common Mistakes

• Not unlocking all cells first: If you skip step 3-4, every cell remains locked, and protecting the sheet locks everything, leaving no editable cells.
• Forgetting to protect the sheet: Locking cells without protecting the sheet does nothing. The lock only takes effect after you click Protect Sheet.
• Using a password you forget: Excel does not offer a way to recover a lost sheet protection password. Write it down in a safe place.
• Protecting the wrong sheet: Sheet protection applies only to the active worksheet. If you have multiple sheets, you need to protect each one separately.
• Allowing too many permissions: If you leave “Format cellsâ€� or “Delete columnsâ€� checked, users can still alter the locked cells indirectly (e.g., deleting a column that contains locked cells).

Tips

• Use Allow Users to Edit Ranges (on the Review tab) if you want different users to have edit rights to different ranges, even with a protected sheet.
• Lock cells containing formulas to prevent accidental overwriting. You can quickly select all formula cells by pressing Ctrl+G (Go To Special) → Formulas.
• Protect the workbook (Review → Protect Workbook) to prevent users from adding, moving, or deleting sheets.
• If you need to edit a locked cell later, unprotect the sheet first (Review → Unprotect Sheet), make your changes, then re-protect it.
• Test your protection by trying to edit a locked cell after protecting. It should show a message saying the cell is read-only.

Troubleshooting

I protected the sheet, but I can still edit everything. This usually means you didn’t lock any cells. Go to Format Cells → Protection and ensure the Locked checkbox is checked for the cells you want to protect. Then re-protect the sheet.

I can’t select or edit any cells after protecting. The “Select locked cellsâ€� and “Select unlocked cellsâ€� permissions are probably unchecked. Unprotect the sheet, go to Review → Protect Sheet, and check those options.

I forgot the password. Excel does not provide a built-in way to recover a lost sheet protection password. You might try using a third-party tool, but there is no guarantee it will work. To avoid this, use a password you can remember or keep a copy of the password in a secure location.

Locked cells are still editable. Check if the worksheet is actually protected. Look at the Review tab; if it says “Unprotect Sheet,â€� then the sheet is protected. If it says “Protect Sheet,â€� then it’s not. Also, make sure you didn’t accidentally check “Edit objectsâ€� or “Edit scenariosâ€� in the protection dialog, which can allow indirect edits.

Protection doesn’t work when I share the workbook. If the workbook is shared (Review → Share Workbook), sheet protection is limited. You may need to unshare the workbook or use the newer co-authoring features in Microsoft 365.

Frequently Asked Questions

Can I lock only specific cells without unlocking the whole sheet first? No. By default, all cells are locked. To make only some cells locked, you must first unlock all cells, then lock the specific ones. This is the standard workflow.

Does locking cells prevent users from copying them? No. Locking cells only prevents editing. Users can still select and copy locked cells unless you uncheck “Select locked cells� in the protection dialog. If you want to prevent copying, you need to use more advanced methods like VBA or Information Rights Management.

Can I set a different password for different ranges? Yes, using “Allow Users to Edit Ranges� on the Review tab. You can define ranges and assign each a password. Users who know the password can edit that range without unprotecting the sheet.

Will locking cells protect my formulas from being seen? No. Locking cells only prevents changes. To hide formulas, you need to check the “Hidden� checkbox in the same Protection tab of Format Cells. Then, when the sheet is protected, the formula bar will not show the formula for those cells.

How do I unlock cells after protecting the sheet? Go to Review → Unprotect Sheet. If a password was set, enter it. Then you can change the locked status of any cells.

Can I lock cells in Excel online (web version)? Yes, the web version supports sheet protection, but the steps are slightly different. Select the cells, right-click, choose Format Cells → Protection, and then use Review → Protect Sheet. Some advanced options may not be available in the web version.

Related

• Test Geolocation in Chrome DevTools
• How to Test Spreadsheet Calculations and Editing Behavior
• How to Use ChatGPT in Windows Terminal