How to lock cells in Excel

In Microsoft Excel, ensuring the integrity of your data often involves restricting access to specific cells. Cell locking serves as a valuable feature, allowing users to protect critical information from unintended changes. This is especially useful in applications where you want to distribute the workbook to larger audiences. Cell locking in Excel is particularly beneficial in scenarios where certain cells contain formulas or critical data that, if altered, could compromise the entire dataset's accuracy. For example, in a financial model, cells containing key formulas for calculations should be locked to prevent accidental modifications. Similarly, in a data report shared across departments, locking cells ensures that the essential data remains unaltered while allowing users to interact with other parts of the workbook.

This feature is also invaluable when distributing workbooks to a broader audience. In such cases, you might want to allow users to view the data and maybe interact with certain elements (like filters or pivot tables) while preventing them from modifying core data and formulas. Locking cells can be selectively applied to only those areas of the workbook that need protection, leaving other parts freely accessible for user input and interaction.

Moreover, the cell locking feature is customizable. It allows you to lock all cells in a worksheet by default and then selectively unlock those that you want to be editable. This approach ensures a fail-safe mechanism where the default state is the highest level of protection, and accessibility is granted on a need-to-use basis.

In this guide, we will explore two methods for locking cells in Excel: utilizing the "Protect Sheet" option and the "Protect Workbook" option.

Method 1: Locking Cells with Protect Sheet option

You can set a cell's Locked status from the Format Cells dialog. The Format Cells dialog is where you can change the number formatting, alignment, or color of a cell, as well as its protection properties. Follow the steps below to set a cell or cells as locked.

1. Select Cells to Lock:
   • Highlight the cells or range of cells that you want to lock.
2. Right-Click and Format Cells:
   • Right-click on the selected cells, and choose "Format Cells."
3. Go to the Protection Tab:
   • In the "Format Cells" dialog box, go to the "Protection" tab.
4. Check the "Locked" Option:
   • Check the "Locked" option. This step only sets the cell locking property but doesn't enforce it until you protect the sheet.
5. Protect the Sheet:
   • Go to the "Review" tab on the Excel ribbon.
   • Click on "Protect Sheet."
   • Set a password if you want to restrict access to the protected sheet, or you can leave it blank for no password.
   • Choose other options based on your preferences.
   • Click "OK."

Setting a cell as locked makes it ready to be locked when its parent worksheet becomes protected. After setting which cells are to be locked, let's now protect the entire worksheet.

Excel's protection commands reside under the Changes section of the Review tab in the Ribbon. Use Protect Sheet icon to lock cells in the active worksheet.

Upon clicking Protect Sheet icon, you will see the Protect Sheet dialog. In the dialog, you can set a password to unlock and and also define what users can do once protection is enabled. By default, Select locked cells and Select unlocked cells actions are selected. If you leave only these two options selected, the users will only be able to select cells, regardless of the cell's locked or unlocked state.

Clicking the OK button completes the locking and protection process. You need to unprotect the sheet for any further modifications in the future.

Unprotecting a Sheet

The unprotecting process is fairly simple. You need to use the Protect sheet button again. However, this button will be labeled as Unprotect Sheet when you're on a protected sheet.

Remember, this process is essential if you're planning to adjust the cells or ranges that were locked by the process explained above on how to lock cells in Excel.

Method 2: Locking Cells with Protect Workbook option

This method is useful if you want to protect the entire workbook, including all sheets.

1. Select Cells to Lock:
   • Highlight the cells or range of cells that you want to lock.
2. Right-Click and Format Cells:
   • Right-click on the selected cells, and choose "Format Cells."
3. Go to the Protection Tab:
   • In the "Format Cells" dialog box, go to the "Protection" tab.
4. Check the "Locked" Option:
   • Check the "Locked" option.
5. Protect the Workbook:
   • Go to the "Review" tab on the Excel ribbon.
   • Click on "Protect Workbook."
   • Set a password if you want to restrict access to the entire workbook.
   • Choose other options based on your preferences.
   • Click "OK."

Tips for Protecting Excel Sheets

• Unlocking Cells: If you need to edit locked cells, you'll have to unprotect the sheet or workbook first. Right-click on the sheet tab and choose "Unprotect Sheet" or "Unprotect Workbook" (based on the method you used).
• Password Protection: If you set a password, make sure to remember it. if you forget the password you've set, Excel does not offer a built-in recovery mechanism. This lack of a recovery option underscores the importance of remembering your password or keeping it stored securely. The absence of a recovery feature is by design, intended to ensure the highest level of security. Once a password is applied to an Excel file, the content is encrypted. This encryption is what keeps your data secure, but it also means that without the correct password, the encryption cannot be bypassed through regular means. As a result, forgetting a password can lead to permanent inaccessibility of the file's content, which can be particularly problematic for critical business data or personal information.
• Cell Locking Effectiveness: Note that cell locking is not foolproof security and can be bypassed by users who are determined to do so. It provides a basic level of protection but may not prevent advanced users from making changes. If stronger security is needed, consider using other methods or tools.