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
Step 2: Select Info
Step 3: Select the Protect Workbook box
Step 4: Select ‘Encrypt with Password’
Step 5: A password dialogue box pops up
Step 6: Input your desired password
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
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.
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
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), and, Command+1 (for Mac).
Step 3: Click on the Protection tab
Step 4: Check the Locked box and click ok
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.
Step 4: Click on the Protection tab
Step 5: Check the Locked box and click Ok
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
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
Step 2: Click on the Review tab
Step 3: Select the ‘Allow Edit Range’ option
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
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
Step 12: The ‘Allow Edit Ranges’ reappears with the new range created
Step 13: Select the Range title followed by the ‘Protect Sheet’ button
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.
