This blog covers locking Excel cells, including individual cells or full worksheets, to restrict editing to maintain integrity. Through clear explanations and step-by-step examples, you’ll understand the concepts and practical applications of cell locking to preserve accuracy across your worksheets.
Table of Contents
Want to learn Excel from the basics to the expert level? Check out the Excel course provided by Intellipaat!
How to Lock All Cells in Excel
Let’s find out how you can lock the entire cell in an Excel sheet.
- Step 1: Select all the cells in the spreadsheet. You can do this by selecting the entire sheet using the selector triangle to the left of column A and above row 1 or by pressing Ctrl + A on your keyboard. This will select and highlight all the cells on the active sheet.
- Step 2: With all the cells selected, go to the Home tab on the ribbon. In the cell group, click the Format button. This will open up a menu of formatting options.
- Step 3: From the Format menu, click on the Format Cells option, which will open a dialog box as shown below.
- Step 4: Click on the Protection tab in the top right and check the Locked box to lock the cells. A disclaimer will appear that you also need to protect the worksheet for locking to take effect.
- Step 5: With the cell range still selected, go to the Home tab and click Format. In the dialog box, choose Protect Sheet.
- Step 6: In the Protect Sheet dialog box, enter a new password so only those with the password can edit the locked cells. Keep the two boxes checked, as shown in the image, and click OK.
- Step 7: Re-enter the new password when prompted, and click OK again.
- Step 8: The cells are now protected. Attempting to edit the locked cells without the password will display an error, as shown in the image:
Get 100% Hike!
Master Most in Demand Skills Now!
Unlock the Locked Cells
Now, let’s find out how to unlock locked cells.
- Step 1: To unlock previously locked cells, keep the selected cell range. Go to the Home tab and click Format. In the dialog box, choose Unprotect Sheet to unlock the sheet, as shown in the image below:
- Step 2: It will prompt you to enter the password to unlock the cells.
- Step 3: Once you have entered the correct password, you can edit the previously locked cells.
Lock Specific Cells in Excel
If you only need to lock certain cells rather than the entire sheet, follow these steps to lock specific cells:
- Step 1: To lock specific cells rather than the entire sheet, first select the particular cells you want to lock, as shown in the image below:
- Step 2: Once you have selected the specific cells to lock, right click on those selected cells. From the pop-up menu, choose Format Cells.
- Step 3: Once the Format Cells dialog box opens, go to the Protection tab. Check the box for Locked. This will lock only the cells you have selected rather than the entire sheet.
- Step 4: As a reminder, locking cells alone has no effect unless you also protect the entire sheet. To do this, go to Home tab > Format > Protect Sheet > Set a Password > Click OK.
Protecting the sheet is required to activate cell locking and prevent users from unlocking those cells you have selected to lock.
Follow these steps to lock the formulas in an Excel worksheet, which helps protect the integrity of the formulas by preventing unwanted changes.
- Step 1: Select the cells containing formulas you want to lock. For example, if cells B1:B10 contain the formulas, select that entire range.
- Step 2: Go to the Home tab at the top of the spreadsheet.
- Step 3: Click on the Format drop-down menu and select Format Cells from the options. This will open the Format Cells dialog box.
- Step 4: To protect the formulas in a sheet, navigate to the Format Cells dialog box. Within this box, there is a Protection tab. Check the two boxes in the Protection tab that allow you to lock cells and hide the formulas.
- Step 5: Once you check the boxes, click OK to hide them from users.
- Step 6: However, remember that locking and hiding formulas have no effect unless you also protect the sheet. To do this, go to the Home tab, select Format, click on Protect Sheet, set a password, and then click OK.
Benefits of Locking Cells in Excel
Locking cells in Excel can have numerous benefits, depending on your requirements. Let’s look into some of the advantages of locking cells.
- Prevent Accidental Changes: Locking cells can prevent unauthorized or accidental changes to important data or formulas. For example, if you lock cells containing formulas, it protects the integrity of the formulas.
- Multiple User Access: If multiple people access a shared workbook, locking certain cells allows you to restrict edits by certain users. This enables smooth collaboration.
- Protect Data: Integrity-sensitive data like financial figures or personal data can be locked to prevent tampering or unintentional changes that corrupt the data. Locking preserves data integrity.
- Focus User Input: By only keeping input cells unlocked, you can guide users to only enter data in desired locations rather than arbitrarily throughout. This facilitates workbook design.
FAQs
How do you unlock cells in a protected Excel sheet?
To unlock cells in a protected Excel sheet, first unprotect the sheet by going to Review > Unprotect Sheet and entering the password if one was set. Then, select the locked cells you want to unlock, go to Format Cells > Protection, and uncheck the locked checkbox. Don’t forget to re-enable sheet protection after.
Why won’t my locked cells stay locked in Excel?
If you have locked cells that seem to allow editing even when protection is turned on, you likely have not activated sheet protection. After locking cells, go to the Review tab and select Protect Sheet. In the Protect Sheet dialog box, choose what editing you want to permit and enter a password if desired before clicking OK.
What do locking cells in Excel do?
Locking cells in Excel protects them from being edited when protection is turned on for a worksheet. This allows certain cells, like headers or formulas, to stay static while other cells can be edited by anyone accessing the spreadsheet.
How do you lock a specific cell in a formula?
To lock a single cell referenced in a formula, add a $ sign in front of the column and row references you want to remain constant. For example, to lock cell C2 as an absolute reference, you’d update the formula to reference it as $C$2. The $sign fixes that cell reference. As you copy or drag the formula cell, it will keep referencing exactly cell C2 instead of changing that reference.
Can locked cells still be formatted in a protected Excel sheet?
Yes, locked cells in a protected Excel sheet can still be formatted if the user has the formatting permissions when sheet protection is enabled. However, locked cells cannot be moved, deleted, or edited unless those permissions were also given before protecting the sheet.