MS Excel has some very unique features for limiting the security rights of users. For example, for limiting the user’s ability to insert/delete/edit column and rows, or change formatting, etc.
Establishing worksheet protection settings is a great way to maintain the integrity of your data and formulas on a specific worksheet. Take the Excel sheet below as an example.
The goal is to add read-only security to the product information (i.e. the D2:E5 range) so users could not edit the content. These three steps need to be performed in order to make the content of these cells read-only and prohibit any kind of alterations to the data:
STEP 1] Locking the cells
All cells in MS Excel are automatically locked. This could be verified by right-clicking on any cell selecting “Format Cells” and going to the “protection” Tab, as illustrated in the screen shot below:
First, I will unlock the entire tab and then lock the cells that I want to protect (D2:E5 range). This is for the purpose of demonstrating that the security features have taken effect on the locked range and not the entire spreadsheet.
STEP 2] Go to the “Review” tab of the ribbon and click on “Protect Sheet”. The screen below appears:
You will notice that most items in the “Allow all users of this worksheet to” are unchecked with the exception of the first two (i.e. “Select Locked Cells”, and “Select Unlocked Cells”).
This section provides the list of permissions that we could grant to unlocked cells (i.e. in the case of this example all cells other than those in the D2:E5 range). I will check all the boxes to grant full permission to the unlocked cells for this demonstration.
STEP 3] Input a password for the sheet and click “OK”.
Reenter the password.
Now let’s test the sheet to ensure that the procedure above has taken effect correctly.
Contents of the D2:E5 range are read-only and non-alterable and double clicking the content or attempting to edit would result in the warning shown below.
Whereas, the remainder of the spreadsheet is editable, as an example here is an example of content typed into a random cell not in the D2:E5 range.