Hello and welcome to this Excel tips video. I am Sumit Bansal and in this video I'm going
to show you how to protect an entire worksheet, but have a range of cells that are unprotected,
and can be edited.
So here I have this worksheet. And what I want to do is protect this entire worksheet,
except these cells, because I want to share this worksheet with someone who can edit these
cells, but I don't want that person to make changes in the remaining part.
So the first thing I would do is select this range that I want to keep unprotected, go
to the Review tab and click on 'Allow Edit Ranges'. And when I click on this option,
it will show me this dialog box where I can specify a range that can be unlocked with
a password, or it can be unprotected without a password.
In this case, when I click on New, it'll ask me for a Title. I'll keep it Range1 for now,
and it refers to the selected range. And in this case, I can give it a password, or I
can leave it as is. If I don't give it a password, this can be edited by anyone, and if I give
it the password, then someone will have to enter the password and this would become unprotected.
And now I would click okay (without giving a password). And now this has been added.
Now I would click on this button called protect sheet, and I would give a password to protect
the entire sheet.
If you do not want to give a password, you can leave this empty, but in this case, let
me give it a password, and click okay. It'll ask me to reenter the password, so I will
do it again. Click okay, and now this entire work sheet has been protected. If I try and
change this, it is not going to allow me to do it. I would have to first unprotect the
worksheet using the password, and I can edit this after that, but this range is still something
that I can easily edit because this is unprotected as of now.
Now, another use case of this could be when I have this sheet, and I want to share this
worksheet with Alex and Mark, and I only want Alex to be able to edit this part, and Mark
to be able to edit this range,. I don't want them to be able to edit anything else.
So in this case I would select this range, go to the allow edit ranges option, and here
click on new. And let me call this 'For Alex'. This is the right range which is selected.
In case it's not, you can click on this icon here and select the range. And let me give
it a password and click okay, and I have to type the password again. Similarly, I would
have to do this for the second range, let me call this ' For Mark'. I would click here,
select this range, and I would give it a password, and reenter the password. The password did
not match, so let me do it again. And now, these two have been created.
Now I can come here, click on Protect Sheet, I can give a password for the sheet as well.
And now this sheet has been protected. Now, if I send this to Alex and she wants to make
some changes here, she'll have to first double click on it and it'll ask for the password.
When she enters the password here, this would now be unprotected and she can make any changes
she wants. But if she comes here to Mark's range here, she will not know the password.
Or she goes anywhere else in this worksheet and try to make any changes she would not
be able to do that.
So this is how you can protect an entire worksheet, but have a specific range that is unprotected,
or have a different password so that you can send it to people and only they are able to
access it.
That's it in this video. I hope you found this useful. Also, if you're liking these
videos, please subscribe to the YouTube channel, and click on the bell icon so that you're
notified whenever I come up with a new Excel tips video. Thank you and have a nice day.