Today, let's take a look at how you can lock cells in Excel. This is practical for
when you want to create a template that you need to send to others to fill out,
and you want to make sure that they only input in the right places
and that they don't delete your formulas. Let me show you how that works.
So I've started to work on this great investment calculation tool, and I want to send it out to the
different departments and different people. I've put a great header here to fill in the blue fields
only, but what do you think is going to happen? Will people stick to that, or will they just
fill in the blue fields, or are they going to go and maybe type in stuff in other places as well?
Well, some people are probably going to do that, right? And I want to avoid that.
I want to make sure they can't type in anywhere else, only in the blue fields.
That's when protection comes into play. To get to protection, just go to the Review tab, and you can
protect your sheet. You can give it a password to protect it. So, guess what? My password is here.
When you click OK, you have to reconfirm your password and click on OK. Now,
everything is protected. So, if I want to go in and type over this one and put a 5,
I can't. I get this error, but everything is protected. So even my blue fields are protected.
I can't input anything in here. So I need to protect this but leave the blue fields open. So,
let me go and unprotect this sheet. To unprotect, I'm going to put my password here. Now I'm
protecting because I want to make changes to this, right? So, as long as this sheet is protected,
you can't do anything. You can't make any adjustments to it. First, you have to unprotect,
make your adjustments, and then protect again. There are different ways you can protect separate
ranges. You could use this feature here, which is "Allow Edit Ranges," and define the ranges that
you want people to edit, or you can use the Format Cells option. This is the version I'm going to use
because I find it to be quite flexible. So, just right-mouse click, go to Format Cells, or use the
shortcut key, control 1, and the last tab here is called "Protection." By default, every cell
has a check mark for locked. This really confused me at the beginning because I didn't know what it
really meant and I didn't really understand what it said here, but basically, this means that once
you actually protect your sheet, any cell that has the check mark will be locked. So you can't input
it. If you want that cell to be adjustable, to be changeable, you need to remove that check mark.
So for example, let's just select more than one cell. I'm going to select this range here
by holding down control, I'm making the selection, and now I'm going to use the shortcut key ctrl 1.
You can also right-mouse click and go to Format Cells and take away that check mark,
and then click on OK. Now that I've made that adjustment, nothing is going to happen unless
I protect the sheet. So now I'm going to go and protect the sheet again. This time I'm not going
to give it a password. Let's just go with OK. So what happens here is if I go to the white cell
here, type in a 5, I can't because it's protected. If I go to here and I want to change this,
I can because the cell isn't locked anymore, but this one right here that's still protected. I
didn't do it for this, I just selected these. So, to do it for all the blue cells here,
I can go and manually select them, but I'm not going to do that. Instead, I'm going to let Excel
select them using this method. Press Ctrl+F to get to the 'Find and Replace' options. You
usually come here when you're looking for text, but you can also look for formatting here. So,
let's go ahead and click on 'Format.' I'm looking for this blue color, so I'm in the 'Fill' options
here and that blue color is this one right here. Click on 'OK' and 'Find All.' This is going to
find all of the cells that have that color. And, to actually see that selection here, I'm going
to press Ctrl+A to select everything. Now, with everything selected, I actually want to go ahead
and unselect this. I'm just holding down 'Control' and clicking on these cells to unselect them. We
don't need this 'Find and Replace' anymore, it's done its work. What I need to do is format these
cells and remove that checkmark for 'Locked.' Remember the shortcut key for that is 'Control+1.'
Let's jump to 'Protection' and for 'Locked,' and we get that little dot in there. It means that
it's a mix. Some cells have a checkmark and some don't have a checkmark. I want to make sure that
all of them don't have a checkmark, so just click, click, remove that checkmark, click on 'OK,'
and now all of these cells are open. After you protect the sheet, so I'm going to
protect. Let's not give it a password. Now, check this out. I can type in anywhere here,
these are all unprotected. But, if I want to go and change this formula and delete it, I can't.
Everything else is protected. Now, in case you have a situation where you want to unprotect
everything except for a few single cells, you can do it the other way around. So, you could
select everything here, press Ctrl+1, remove that checkmark, so everything is unprotected,
and then select those single cells that you want protected and put back the checkmark for them. So,
that's how you can lock cells in Excel. If you've used this before, let me know what
you think of it because at the beginning, I found this whole locking experience quite confusing. So,
comment below, let me know what you think. Thank you for being here. Thank you for watching.
Subscribe if you haven't subscribed yet, and I'm gonna see you in the next video.