Lock Cells in Excel

What Is Lock Cells In Excel?

The Lock Cells in Excel function enables users to lock 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.

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.

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

What Is Lock Cells In Excel?

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:

First, go to the Home tab in Excel.
Choose Format from the Cells
Select the Format Cells option from the drop-down list, or press the shortcut keys Ctrl + 1.

The Format Cells window appears.
Select the Protection tab, and click the Locked
Press OK.
Select the cell(s) to be locked
Once again, go to the Home tab and select Format from the Cells.
Click on the Protect Sheet option from the drop-down list.
Set a password.
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.

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

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.

The Format Cells window appears.

Step 2: Select the Protection tab, and check the Locked checkbox. Click OK.

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

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 4: The Format Cells window pops up. Go to the Protection tab, and select the Locked option.

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.

Step 2: A window called “Protect Sheet” opens. Set the Password to unprotect the sheet. Check the Select locked cells and Select unlocked cells boxes.

Click “OK.”

Step 3: A window called “Confirm Password” opens. Re-enter the set Password to proceed. Click “OK.”

Step 4: 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.

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.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

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

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

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

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.

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

Step 3: Go to the “Protection” tab, and uncheck the “Locked” checkbox.

Click “OK.”

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

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

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

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

Click “OK.”

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

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

Step 10: Click “OK.”

Step 11: 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.

Step 12: 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.

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.

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.

Step 2: The Style window appears.

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

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.

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

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.

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.

Step 3: The Format Cells window pops up.

Click on the Protection tab, and uncheck the Locked option.

Select OK.

Step 4: 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.

Step 5: The Go To Special window appears.

Select the Formulas option.

Click OK.

Step 6: 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.

Choose the Protection tab, and select the Locked checkbox.

Click OK.

Step 7: 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 8: The Protect Sheet appears.

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

Click OK.

Step 9: The Confirm Password window pops up. Type the password in the Re-enter the set password window to proceed.

Click OK.

Step 10: 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.

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 Excel formula. We need to use the below steps to Lock a Single Excel Formula.

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.

Step 3: The Format Cells window appears. Go to the Protection tab, and uncheck the Locked option.

Click OK.

Step 4: 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.
The Go To Special window pops up.
Select the Formulas option.
Click OK

Step 5: 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 6: Choose the Protection tab, and select the Locked option. Click OK.

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

Select Protect Sheet from the drop-down list.

Step 8: The Protect Sheet window pops up.

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

Click OK.

Step 9: The Confirm Password window pops up.

Step 10: Type the password in the Re-enter the set password window to proceed. Click OK.

Step 11: 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.

Click OK.

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.

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.

Lock Cells in Excel Download

Lock Cells in Excel