Lock Cells in Excel

What Is Lock Cells In Excel?

The Lock Cells in Excel function enables users to lock the information within a cell(s). It protects the data from getting forged, edited, or deleted by other users. If we want to make changes in the data, first, we need to unlock cells in Excel spreadsheet.

Let us consider the below table listing employees’ names and their IDs. We need to ensure that the data does not get altered or forged. 

Intro Example

Go to the Home > Cells > Format > Format Cells (or press the shortcut keys Ctrl + 1) > Protection > click Locked > OK.

Now, select cells to be locked.

Go to Home > Cells > Format > select Protect Sheet from the drop-down list > set a Password > OK.

Intro Example 1

Thus, we have locked the employees’ names and IDs using lock cells in excel with passwords.

Key Takeaways
  • Lock cells in Excel prevent crucial data from getting deleted, copied, or edited by unauthorized persons accidentally or intentionally.
  • We can lock both single and multiple cells in excel.
  • The cells locked in a sheet cannot be reformatted or deleted, and their content cannot be edited. However, the locked cells can be selected, but we can only change that using the Protection tab.
  • There is no shortcut key to lock cells, but one can make it using VBA.

How To Lock Cells In Excel?

We can lock cells in excel spreadsheet using the following steps:

  1. First, go to the Home tab in Excel.

  2. Choose Format from the Cells

  3. Select the Format Cells option from the drop-down list, or press the shortcut keys Ctrl + 1.


    The Format Cells window appears.

  4. Select the Protection tab, and click the Locked

  5. Press OK.

  6. Select the cell(s) to be locked

  7. Once again, go to the Home tab and select Format from the Cells.

  8. Click on the Protect Sheet option from the drop-down list.

  9. Set a password.

  10. Click OK.

We can lock cells in excel using the steps mentioned above. Let us understand the method to lock cells with an example.

The following image shows cell A2 having a text string. But, first, we need to follow the steps below to understand how to lock a single cell in Excel.

Lock Cells in Excel Basic Example

Step 1: Press Ctrl + A to select the whole sheet.

Step 2: Go to the Home tab and click the Format option under the Cells group. Select Format Cells from the drop-down list, or press the shortcut keys CTRL + 1.

Step 3: The Format Cells window appears.

Format Cells

Step 4: Select the Protection tab, and check the Locked checkbox.

Step 5: Click OK.

Step 6: We need to select cell A2 to lock it.

Step 7: Go to the Home tab and click the Format option from the Cells group. Select Format Cells from the drop-down list, or press the shortcut keys CTRL + 1.

Step 8: The Format Cells window pops up.

Step 9: Go to the Protection tab, and select the Locked option.

Step 10: Click OK.

Please Note: In Excel, locking cell(s) works only if the worksheet is protected. Therefore, let us learn how to protect the worksheet to ensure cell(s) are locked:

Step 1: Go to the “Home tab” and click the “Format” option in the “Cells” group. Select “Protect Sheet” from the drop-down list, as shown in the following image.

 Basic Example.1

Step 2: A window called “Protect Sheet” opens.

Step 3: Set the Password to unprotect the sheet. Check the Select locked cells and Select unlocked cells boxes.

Step 4: Click OK.”

Lock Cells in Excel Basic Example.2

Step 5: A window called “Confirm Password” opens.

Step 6: Re-enter the set Password to proceed.

Step 7: Click OK.”

Basic Example.3

Step 8: The selected cell A2 is now locked. If anyone tries to make changes in the locked cell, an error message will pop up, as shown in the following image.

Lock Cells in Excel Basic Example.4
  • Since no other cell(s) except the selected cell A2 is locked, we can change the whole sheet except cell A2. To make changes in cell A2, we must unprotect the sheet and enter the password.

Examples

Let us understand how to lock cells in Excel spreadsheet in detail with the following examples:

Example #1 – Lock an Excel Cell

The next image depicts the data having items and the monthly sales. We want to perform the following tasks in Excel to understand the mechanism of the lock cells in Excel sheet.

In the table, the data is reflected as below:

  • Column A: Contains Items
  • Column B: Contains JAN sales
  • Column C: Contains FEB sales
  • Column D: Contains MAR sales
Lock Cells in Excel Example.1

The steps to perform the given tasks are listed as follows:

Step 1: Press CTRL + A to select the whole sheet.

Step 2: Go to the “Home” tab and click the “Format” option in the “Cells” group. Select “Format Cells” from the drop-down list, or click the shortcut key CTRL + 1.

Example.1.1

Step 3: A window called “Format Cells” opens.

Format Cells

Step 4: Go to the Protectiontab, and uncheck the Locked checkbox.

Step 5: Click OK.”

Step 6: Next, select the cells that you wish to lock. In this example, we are locking cells B1:B4.

Step 7: Go to the “Home” tab and click the “Format” option in the “Cells” group. Select “Protect Sheet” from the drop-down list.

Step 8: A window called “Protect Sheet” opens.

Example.1.2

Step 9: Set the Password to unprotect the sheet. Click the checkboxes of locked and unlocked cells.

Step 10: Click OK.”

Step 11: A window called “Confirm Password” opens.

Step 12: Re-enter the set Password to proceed.

Step 13: Click OK.”

Step 14: The selected cells B1:B4 are locked. If anyone tries to make changes in the locked cells, the error box of Microsoft Excel will pop up, as shown in the following image.

Lock Cells in Excel Basic Example.4

Step 15: No other cell except the selected cells B1:B4 is locked so that we can change the whole sheet except cells range B1:B4, as shown in the following image.

Lock Cells in Excel Example.1.3
  • To make changes in cells B1:B4, we need first to unprotect the cells and then enter the lock cells in excel with a password.

Example #2 – Lock Cells in Excel other than Input Cells

Consider the following image that consists of input and output values. We need to use the steps below to understand lock cells in Excel sheet other than input cells.

Lock Cells in Excel Example 2

Step 1: Select the Home tab. From the Styles group, right-click on the Input style from Data and Model category.

Then, click the Modify… option as highlighted below.

Lock Cells in Excel Example 2.1

Step 2: The Style window appears.

Step 3: Select the Protection: Locked checkbox and click OK.

Example 2.2

Step 4: From the Home tab and click on the Format option available in the Cells group.

Select Format Cells from the drop-down list, or press the shortcut keys Ctrl + 1.

Step 5: Again, the Format Cells window pops up.

Step 6: Choose the Protection tab, and uncheck the Locked option.

Step 7: Click OK.

Lock Cells in Excel Example 2.3

Step 8: The status of the Protection: Locked option changes to Protection: No Protection in the Style window as highlighted below.

Example 2.4

Step 9: We will select input cells B1, C1, and D1. Click Input style in the Style group under the Home tab.

Step 10: We can observe that the cells in the sheet are unprotected and editable.

Step 11: Once again, select the Home tab and click the Format option from the Cells group. Select Protect Sheet from the drop-down list.

Step 12: The Protect Sheet window appears.

Step 13: Type a password in the Password to unprotect sheet dialog box. Check the Select locked cells and Select unlocked cells from the available options as shown below.

Step 14: Click OK.

Step 15: The selected input cells B1, C1, and D1 are locked. As shown in the following image, a Microsoft Excel error box pops up if anyone tries to make changes in the locked cells.

Lock Cells in Excel Basic Example.4
  • Thus, we have locked the selected input cells B1, C1, and D1.

Example #3 – Lock All Formulas in Excel

The following image shows values V1 and V2, denoted in columns A and B, respectively. The output of the two values reflects in column C, and the formulas used to calculate the output are in column D. We need to use the below steps to lock all formulas in Excel.

Step 1: Press CTRL + A to select the whole sheet.

Step 2: Go to the Home tab and select the Format option from the Cells group.

Select Format Cells from the drop-down list, or use the shortcut keys CTRL + 1 as shown in the following image.

Example 3

Step 3: The Format Cells window pops up.

Step 4: Click on the Protection tab, and uncheck the Locked option.

Step 5: Select OK.

Lock Cells in Excel Example 2.3

Step 6: Click the Home tab and select the Find & Select option from the Editing group. Click the Go To Special… option from the drop-down list, as highlighted in the following image.

Lock Cells in Excel Example 3.1

Step 7: The Go To Special window appears.

Step 8: Select the Formulas option.

Step 9: Click OK.

Example 3.2

Step 10: Again, click on the Home tab and select the Format option under the Cells group. Choose the Format Cells option from the drop-down list, or press the shortcut keys Ctrl + 1. The Format Cells window pops up.

Step 11: Choose the Protection tab, and select the Locked checkbox.

Step 12: Click OK.

Example 3.3

Step 13: Go to the Home tab and click on the Format option from the Cells group.

Select the Protect Sheet option from the drop-down list.

Step 14: The Protect Sheet appears.

Step 15: Type a password in the Password to unprotect sheet dialog box. Choose the Select locked cells and Select unlocked cells options.

Step 16: Click OK.

Example 3.4

Step 17: The Confirm Password window pops up.

Step 18: Type the password in the Re-enter the set password window to proceed.

Step 19: Click OK.

Step 20: The selected formula cells are now locked. A Microsoft Excel error box pops up If anyone tries to make changes in the locked cells, as shown in the below image.

Lock Cells in Excel Basic Example.4
  • Since we have locked only the selected formula cells in the range C2:C4, we can change the whole sheet, except for the cell range C2:C4.

Example #4 – Lock a Single Excel Formula

Consider the below table with the list of fruits (in column A) and the sales (in column B). Column C denotes the maximum sales calculated using the MAX excek formula. We need to use the below steps to Lock a Single Excel Formula.

 Example 4

Step 1: Press CTRL + A to select the whole sheet.

Step 2: Go to the Home tab and click on the Format option under the Cells group.

Select the Format Cells option from the drop-down list, or press the shortcut keys Ctrl + 1 as shown in the following image.

Lock Cells in Excel Example 4.1

Step 3: The Format Cells window appears.

Step 4: Go to the Protection tab, and uncheck the Locked option.

Step 5: Click OK.

Lock Cells in Excel Example 2.3

Step 6: Once again, select the Home tab and click on the Find & Select option under the Editing group.

Select Go To Special… option from the drop-down list.

Step 7: The Go To Special window pops up.

Step 8: Select the Formulas option.

Step 9: Click OK.

Example 3.2

Step 10: Again, click on the Home tab and select the Format option from the Cells group. Select Format Cells from the drop-down list, or press the shortcut keys Ctrl + 1.

The Format Cells window appears.

Step 11: Choose the Protection tab, and select the Locked option.

Step 12: Click OK.

Example 3.3

Step 13: Select the Home tab and click on the Format option from the Cells group.

Select Protect Sheet from the drop-down list.

Step 14: The Protect Sheet window pops up.

Step 15: Type a password in the Password to unprotect sheet dialog box. Choose the Select locked cells and Select unlocked cells options.

Step 16: Click OK.

Lock Cells in Excel Example 4.2

Step 17: The Confirm Password window pops up.

Step 18: Type the password in the Re-enter the set password window to proceed.

Step 19: Click OK.

Step 20: The selected formula cells are now locked. As shown in the image, a Microsoft Excel error box pops up if anyone tries to make changes in the locked cells.

Step 19: Click OK.

Lock Cells in Excel Basic Example.4
  • Since we have locked only the selected formula cell C2, we can change the whole sheet except cell C2.

Important Things To Note

  • To enable the lock cells option, we need to uncheck the Select locked cells checkbox in the Protect Sheet window, which is available under the Home Tab in the Format option from the Cells.
  • If we select the range containing the locked and unlocked cells, we can see the Locked checkbox in the Format Cells window under the Home Tab in the Format option from the Cells.
  • If we try to edit locked cells, an error box pops up.

Frequently Asked Questions (FAQs)

When to lock cells in Excel?

We should Lock Cells in Excel sheet when we don’t want other people to make changes, such as copying or deleting the data. In simple terms, the Lock option is used when we want to protect data from any unauthorized persons. 

How to lock a column in Excel?


Let us consider an example to understand how to Lock a column in Excel.

For example, the below table shows the list of marks (in column B) obtained by students (in column A).

 FAQ1

We need to use the below steps to Lock a column in Excel.

Step 1: Press Ctrl + A to select the whole sheet.

Step 2: Go to the Home tab and click on the Format option from the Cells group.
Select the Format Cells option from the drop-down list, or press the shortcut keys CTRL + 1.

Step 3: The Format Cells window appears.

Step 4: Choose the Protection tab, and uncheck the Locked option.

Step 5: Click OK.

Step 6: We should select the cells that we need to lock. In this example, we have to lock column A. So, we select column A.

Step 7: Again, click on the Home tab and select the Format option from the Cells group. Select the Format Cells option from the drop-down list, or press the shortcut keys Ctrl + 1.

Step 8: The Format Cells window pops up.

Step 9: Choose the Protection tab, and select the Locked option.

Step 10: Click OK.

Step 11: Once again, go to the Home tab and click on the Format option under the Cells group.
Select Protect Sheet from the drop-down list.

Step 12: The Protect Sheet window pops up.

Step 13: Type a password in the Password to unprotect sheet dialog box. Choose the Select locked cells and Select unlocked cells options.

Step 14: Click OK.

FAQ1.1

Step 15: The Confirm Password window pops up.

Step 16: Type the password in the Re-enter the set password window to proceed.

Step 17: Click OK.

Step 18: The selected column A is now locked. As shown in the image, a Microsoft Excel error box pops up if anyone tries to make changes in the locked cells.

Lock Cells in Excel Basic Example.4

Since we have locked only the selected column A, we can make changes in the whole sheet except column A. 

Why won’t cells lock in Excel?

By default, all cells will be locked when we protect cells in a sheet or workbook. This means we cannot reform or delete them, and their content can’t be edited. The locked cells can be selected by default, but we can change that in the protection options.

Download Template

This article must be helpful to understand the Lock Cells in Excel with its examples. You can download the template here to use it instantly.

This article has been a guide to Lock Cells in Excel. Here we learn to lock single and multiple cells and formulas with examples and a downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.