Protect cells with different passwords to allow levels of authority

Preview
Note: All Excel protection is relatively weak. It is useful to stop casual users but it is not for top secret information.

With the previous protect sheet methods you had 2 states. Either the person knows the password and can change any cell, or they don’t and they can’t change the locked cells. But you can also specify a password per cell or range of cells. This way only certain people can change certain cells.

To follow along go to 19.10

In the example below, we have decided that in row 13 we want most people to be locked out, but certain people can change the way the Selling Price per unit is calculated.

We highlight the cells and under the Review Tab, we click Allow users to Edit Ranges and then click the NEW button.

Different levels of protecting excel spreadsheets

As shown below you now can give it a name, specify more cells to include and then give it its own password.

Different levels of protecting excel spreadsheets

As for when you locked cells, this step does not mean it is protected. You still need to click the Protect the Sheet button and specify what a user is allowed to do.

Different levels of protecting excel spreadsheets

Now if someone tries to change a locked cell, they can’t get to it UNLESS it is one of these specific ranges in which case Excel will ask for the password. If they know it, they can change the cells, otherwise it is blocked. This can be used in conjunction with the normal Protect Sheet and Protect Workbook options.




Lesson tags: vlookup
Back to: POPIA and GDPR Compliance- Built in Excel Tools that will help > Control access to information in Excel Spreadsheets