Clicky

How to Lock in Excel [ Workbook, Worksheets, Rows, Columns & Cells ]

how to lock in excel

One of the ways we secure ourselves at home is by locking our doors.

The level of protection employed by individuals may vary from person to person.

While occupants of fenced apartments may decide to lock their entrance gates, others without a perimeter fence would simply lock up their entrance doors.

This scenario is also applicable in excel.

To restrict access/entry in excel, a lock action is carried out.

This means the locked cell, column, and/or worksheet cannot be reformatted, deleted, or its content edited.   

In this tutorial, we will learn how to carry out the ‘lock’ activity in excel.

We will adopt a decremental approach by first learning how to lock an excel workbook, worksheet, then a designated column/row, and finally a cell.

It is not just OK to learn how the lock activity works without teaching you how to unlock these cells.

Hence, you will also see how to unlock your cells

Let’s define some terms that you may find in the course of this study.

  • Worksheet – This is a single page in excel. In current versions of Excel, a worksheet contains 1,048,576 rows, 16,384 columns, and a total of 17,179,869,184 cells
  • Workbook – This is an excel file and it contains one or more worksheets
  • Columns – This is a vertical grid line in excel
  • Rows – This is a horizontal grid line in excel

For this tutorial, we will be utilizing the data set below.

The table below shows the performance of seven (7) students in a Mathematics, English, Science, and Art course.

Our task will be to:

  • Lock the entire workbook
  • Lock the entire Worksheet
  • Lock columns and rows
  • Lock specific cells
  • Unlock the workbook
  • Unlock the Worksheet
  • Unlock a range in the worksheet

Let’s get started!

Locking a Workbook

Locking a Workbook prevents unauthorized users from viewing, editing, and moving the structure of the worksheets in your workbook.

You can use an encrypted password to protect your workbook.

In our example, after locking our workbook, only users with the encrypted passwords can access/open the excel file.

To lock a workbook, follow the processes outlined below.

Step 1: Click on FILE 

locking a workbook

Step 2: Select Info 

Step 3:  Select the Protect Workbook box

project workbook excel

Step 4: Select ‘Encrypt with Password’ 

encrypt password excel

Step 5: A password dialogue box pops up

Step 6:  Input your desired password

password dialogue box excel

NOTE: 

  • Ensure you use a password that you can easily remember because a forgotten password cannot be recovered.
  • There is no limitation to the password combination concerning characters, numbers, case 
  • These passwords are case sensitive

Step 7: Click OK

Step 8: A password confirmation request box pops up and you are expected to confirm your password. 

Step 9: Re-enter your desired password

Step 10: Click OK

No user without your password will be able to access the content of your workbook. 

Locking a Worksheet 

Locking a worksheet helps to prevent the accidental or deliberate deleting, moving, or editing of data in the sheet.

On a worksheet, you can control how and what actions a user can perform within.

For example, you may decide to lock only some columns, some cells, the entire worksheet, or even format the sheet to allow only some activities.

Let’s begin with locking the entire worksheet.

Locking an Entire Worksheet

This protection ensures that all cells, ranges, formulas, etc. embedded in a worksheet do not get tampered with.

To lock an entire worksheet simply follow the steps below:

Step 1: Click on the ‘Review’ tab 

Step 2: Select ‘Protect Sheet’ on the Ribbon

Step 3:  A Protect Sheet dialogue box pops up. This box contains an ‘Allow all users of this worksheet to’ list

encrypt password excel

Step 4: Check the boxes of the desired option you wish to grant other users’ privilege to.

(Please see table 2 for details of what each privilege option allows users to do) 

Note: Step 5, Step 7, & Step 8 are completely optional steps as you can lock your worksheet without a password.

Step 5:  Input your desired password in the password input field

Step 6: Click OK

Step 7: A password confirmation request box pops up and you are expected to confirm your password.

confirm password excel

Step 8: Re-enter your desired password 

Step 9: Click OK

Confirming that an Entire worksheet is Locked

To confirm that the Lock Worksheet action was successful, you will notice that the Protect Sheet’ on the ribbon changes to Unprotect Sheet’.

In addition, any attempt to type into any of the cells will pop up a warning notification

unprotected sheet excel

Locking a Column / Row in a Worksheet

This allows Users to lock only selected Columns or Rows in a worksheet.

To do this, firstly, you must ensure that the worksheet is not protected before you commence.

After confirmation, follow the steps below:

Step 1: Select / Highlight the columns/ and rows you wish to lock

Note: To highlight rows and columns that are adjacent, simply highlight the heading of the first column or row and drag the cursor until the desired selection is complete. In our example, only Column B (‘Names of Student’) is highlighted. For rows and columns that are non-continuous, simply hold down the Ctrl key and use your cursor to highlight the rows/columns individually until your desired selection is complete

Step 2: Right-click on any part of the highlight in the sheet and select the ‘Format’ option.

For quick access you can use Ctrl+1 (for windows), andCommand+1 (for Mac).  

column and row excel

 

Step 3: Click on the Protection tab

Step 4: Check the Locked box and click ok

locked box

Step 5: Click on the ‘Review’ tab 

Step 6: Select ‘Protect Sheet’ on the Ribbon

Step 7:  A Protect Sheet dialogue box pops up. This box contains an ‘Allow all users of this worksheet to’ list

Step 8: Check the boxes of the desired option you wish to grant other users’ privilege to. 

Step 9:  Input your desired password in the password input field (Optional)

Step 10: Click OK

Step 11: A password confirmation request box pops up and you are expected to confirm your password. (Optional)

Step 12: Re-enter your desired password (Optional)

Step 13: Click OK

You will notice at this point that any attempt to input data in any of the locked columns or rows will pop up a Warning Notification box

Locking a Cell in a Worksheet

This allows Users to lock only selected Cells in a worksheet.

To do this, firstly, you must ensure that the worksheet is not in the protected mode before you commence.

After confirmation, follow the steps below:

Step 1: Select / Highlight the cells you wish to lock

Note: To highlight cells positioned end-to-end (adjacent cells), simply highlight the first cell and drag the cursor until the desired selection is complete. For cells that are non-contiguous, they can be highlighted by, pressing Ctrl+Left-Click. Until the selection is complete.  In our example, Cells C4, C8, E4, F7 are highlighted

Step 2:  Click on the Home tabs

Step 3: In the Alignment group, click the small arrow to open the Format Cells popup window.

formal cells excel

locked box excel

Step 4: Click on the Protection tab

Step 5: Check the Locked box and click Ok

locked box excel

Step 6: Click on the ‘Review’ tab 

Step 7: Select ‘Protect Sheet’ on the Ribbon

Step 8:  A Protect Sheet dialogue box pops up. This box contains an ‘Allow all users of this worksheet to’ list

Step 9: Check the boxes of the desired option you wish to grant other users’ privilege to. 

Step 10:  Input your desired password in the password input field (Optional)

Step 11: Click OK

Step 12: A password confirmation request box pops up and you are expected to confirm your password. (Optional)

Step 13: Re-enter your desired password (Optional)

Step 14: Click OK

Just like we observed in the locking a Column / Row section, any attempt to edit the data in any of the locked cells will pop up a warning notification.

Unlocking a Workbook

To unlock a protected workbook, simply input the right encrypted password and click OK. 

Unlocking a Worksheet

To unlock a locked worksheet, follow the steps below:

Step 1:  Click on the Review tab

Step 2: Select the ‘Unprotect Sheet’ Option

unprotect sheet

Step 3: If requested, enter the password (This prompt will only appear if the sheet was protected with a password)

Unlocking a Range in Worksheet

Unlocking a range of cells in a protected sheet allows other users to only edit permitted ranges.

In our example, if we wanted all subject teachers to update the student scores without having access to editing the Name of Student, and Average columns, the range C3:F9 will be selected.

This range can also be passworded. 

So let me show you how this will be achieved.

Step 1: Highlight the worksheet by clicking the Highlight arrow

Note: Ensure that the Locked box is checked. To do this Right-click > Select Format > Click on the Protection Tab > Check the Locked box

unlocking a range in worksheet

Step 2: Click on the Review tab

Step 3: Select the ‘Allow Edit Range’ option

allow edit range

Note that the ‘Allow Edit Ranges’ option is only activated when the sheet is not Protected.

Step 4:  An ‘Allow Users to Edit Ranges’ dialogue box pops up

allow edit ranges

 Step 5: Click on

  • The NEW button to add a new range for editing
  • The MODIFY button to edit the current range
  • The DELETE button to delete an already created editable range 

In this tutorial, we will select the NEW option because we want to create a new editable range

Step 6: A ‘New Range’ dialogue box appears

Step 7: Input Title

Step 8: Select the editable range. (In our example the editable range is C3:F9)

Step 9: Assign the password if desired

Step 10: Click on Permission if you will like to add more rules to the range (optional)

Step 11: Click OK on the New Range Dialogue Box

allow edit ranges

Step 12: The ‘Allow Edit Ranges’ reappears with the new range created

Step 13: Select the Range title followed by the ‘Protect Sheet’ button

protect sheet

Step 14: A Protect Sheet dialogue box pops up. This box contains an ‘Allow all users of this worksheet to’ list

Step 15: Check the boxes of the desired option you wish to grant other users’ privilege to.

Note: Step 17, to Step 20 are completely optional steps as you can lock your worksheet without a password.

Step 16:  Input your desired password in the password input field

Step 17: Click OK

Step 18: A password confirmation request box pops up and you are expected to confirm your password.

Step 19: Reenter your desired password

Step 20: Click OK

You will observe that only the range selected can be edited by other users

Conclusion

From this tutorial we have been able to learn how to lock and unlock workbooks, worksheets, Columns, Rows, and cells. 

It is also important to emphasize that you must be careful to always remember your password because once these passwords are forgotten, they cannot be recovered.

The best practice is always to keep a record of all workbooks, worksheets, etc. passwords.

Avatar photo
Denise is a Financial Controller with experience in Office Software (Excel & Word), Accounting, Bookkeeping, Business Incorporation/LLC, Taxes and IRS Issues and more. She Graduated from Portland State University in Business Management and went onto Seattle University to complete her Graduate (Masters) Degree in Organizational Systems Renewal. She contributes to Computing.net with her knowledge of Business Software, in particularly Microsoft Excel and Google Sheets.