One simple command that elevates Excel Sheet protection to a whole new level. Imagine wanting to protect a Worksheet in Excel but with a few requirements.

• all coworkers can view but they must not be allowed to edit the Worksheet
• four special coworkers (John, Paul, George and Richard) all have their ranges that they and they alone can edit

With the Protect Sheet command, you could only either unlock cells or leave them locked.

The first of our two conditions above already means that you have to lock them, to avoid that, maybe one could use the general options while saving, set the Password to modify and only give the fabulous four that password.

That would prolong the opening of the file in Excel, but still, the second condition above would not be met and could not be met only by using the Protect Sheet, Protect Workbook and setting Password to Open or Password to modify. That’s where Allow users to edit ranges comes in.

In Excel we select the range of cells, that we wish to let someone format, and select REVIEW/Allow users to edit ranges and you will get to the Allow users to edit ranges dialog box

In the Allow users to edit ranges dialog box you select New, give this range a title, the Refers to Cells: will be filled out automatically by the initial selection and the most important thing, you must set a Range Password for this Range. In a classical domain environment you can also use the Permissions button and automatically let Users or Groups from the domain to edit this Range.

After pressing OK you will have to retype your password and the Range is set. You can change this settings at any time by selecting the Modify button.

You must do this for all the desired ranges.

It’s also worth mentioning that we left all the cells locked! After this set up, if you want for this to take effect, you must protect the Worksheet. Until you do, all this settings have no effect. But after you either right click on the sheet name and select Protect Sheet or find the same command on the REVIEW tab set the password (which has no connection to the previously set passwords), and allow users to select the locked cells(!).

So now John opens this workbook, all he has to do is to either double click into his range or start typing into that range. He will get the Unlock Range dialog box where he inputs the password that was given to him.

After he does so, he can edit that range in any way he desires.

All the other cells and Ranges remain locked! So as promised, this takes the Sheet protection to a whole new level!