welcome to Excel campus my name is John
and in this video I'm going to explain
how to lock individual cells for editing
in Excel so here we have a quote sheet
that your team or co-workers might be
filling out and we only want them to be
able to edit specific cells in the sheet
some of the cells contain formulas and
we don't want them to accidentally
delete these cells or edit them and this
is actually a two-step process in Excel
the first step is that we're going to
lock or unlock individual cells and then
we are going to protect the worksheet so
we'll first take a look at this cell
here it does contain a formula so we
want to make sure this cell is locked so
we're going to right-click the cell and
choose format cells and that will bring
up the format cells window and on the
protection tab here we have a check box
that says locked so this means this cell
is locked now it also notes down here
that locking cells are hiding formulas
has no effect until you protect the
worksheet so that's going to be step two
so for now this is fine this cell is
locked you might want to check the other
cells that contain formulas to make sure
those are locked but by default all
cells within Excel will be locked this
checkbox will be checked for all cells
in Excel so we'll go ahead and hit OK
here and what that means is that we need
to unlock these cells that we want the
user to be able to edit so this range
here we can also select multiple cells
we'll select this range here we want the
user to be able to edit this and input
into these cells so we can just right
click this again choose format cells and
here on the protection tab we want to
uncheck the box the locked box so now
these cells are unlocked and we'll go
ahead and hit OK here and then you'd
want to repeat that process for the
other cells on the sheet that you want
the user to be able to edit and once
you've done all that we'll move to step
two which is protecting the sheet so to
do that we can just right click on the
sheet tab down here and then we'll
choose protect sheet and that will open
up this window where we can insert a
password this is optional you can also
leave this blank I'll go ahead and add a
password for now so you can see how that
works it's just going to add one two
three there we want to leave this
checked the
is to protect the worksheet and then we
have some options down here on what the
user can do with this worksheet now by
default these two top check boxes here
are checked allows the user to select
lock cells and also select the unlocked
cells so we'll just leave that as is for
now and we'll go ahead and hit OK and
then because we entered a password we're
also going to be prompted to confirm our
password and re-enter it so just enter 1
2 3 again you can make the password
whatever you'd like and then hit OK
so the sheet is now protected the
unlocked cells we can still edit so I
can select the cell here maybe this
should be 35 instead I'll change that
and hit enter that all works if I go
over to this cell here that's locked and
I try and edit that I'll just hit any
key on the keyboard you can see that the
we get a warning here that says the cell
or chart you're trying to change is on a
protected sheet and then it gives you
instructions on how to unprotect it and
change it so we can go ahead and hit OK
there and this will just let the user
know that they cannot edit these locked
cells now when you do want to make
changes to the sheet they'll just
unprotect it so it will again
right-click the sheet tab and go to
unprotect sheet since we entered a
password we're going to be prompted for
that password if you leave the password
field blank initially then you will not
see this box but I'll go ahead and enter
our password and hit OK and now the
sheet is unprotected and again we can go
here we could edit this formula or edit
this cell that contains a formula and do
all kinds of changes to the sheet if
you're enjoying this video please click
that big red subscribe button below the
video to subscribe to our Channel and
also click the notification bell icon
there to get notified when new videos
are published so that's the basics of
locking and unlocking cells but I do
want to leave you with three bonus tips
that should help make this process a
little easier so the first is that we're
going to go down here right-click on the
tab again and protect sheet and here on
our options we have this option to
select locked cells I'm going to uncheck
this so the user will not be able to
select the locked cells then go ahead
and hit OK now this time I will not add
a password so I'll just leave that blank
hit okay here so the sheet is now
protected and if I try and click this
cell right here select the cell I can't
select it so this just makes it easier
for the user to input data because they
will only be able to select the cells
that are unlocked and so if I'm just
going through this line here I'm right
arrowing inputting data or hitting tab
on the keyboard when I get to this cell
here in a hit right arrow or tab it now
jumps down to the next unlock cell so
again that just makes it a little easier
for the user to input their data they
don't really need to see these cells or
select these cells that contain the
formulas anyway and the sheet can also
be protected and unprotected from the
review tab on the ribbon up here we can
see it right here so the sheets
currently protected and we can just
click this button here to unprotect the
sheet and then the button text will
change back to protect sheet and that
leads to my next tip about the lock
button the lock button we got to it from
the right-click menu here going right
click format cells keyboard shortcut for
this window is control 1 on the keyboard
but we can also get to this from the
Home tab on the ribbon so Home tab here
in this format drop-down you'll see we
have a locked cell button right here so
we can click this and if we click that
that'll lock this cell and again we go
home tab format cells will now see that
that button is enabled there there's a
gray box around it then that lets us
know that the cell is locked now we can
also add this button to the quick access
toolbar so if I just right click the
button here and choose add to quick
access toolbar that will put the button
right up here in my quick access toolbar
and again when I select any cell I can
then see it's locked or unlocked State
so I'll select the cell here we can see
that that's enabled you can see kind of
the dark green there means that cells
locked if we select this cell here it's
no longer an abled there's no dark green
around it and that means that cell is
unlocked so if you're going through your
sheet and locking and unlocking cells
that just makes it a lot easier I can
just select this range of cells here you
can see it's locked so I'll just quickly
quick click that button there and now
those cells are unlocked and then my
final tip is to change the
formatting of the locked or unlocked
cells so this quote cheap does a fairly
good job of that we can see that the
locked cells here the ones that contain
formulas do have this light gray fill
color so that lets us know and lets the
user know that these cells might
potentially be locked and they do not
need to edit them and if you're
wondering where I got this quote cheap
from this is actually in the template
library if you go file tab and then new
here you can just search for templates I
just searched for quote hit enter and
you can see just a few different quote
sheets here I believe I chose this one
just select that and hit create and that
will create a new file here with a blank
quote sheet you can go set it up with
the locking and unlocking and protect it
and set it up for your company and then
you'll be good to go and you don't have
to recreate this entire sheet so I hope
those tips helped you of course if you
have any questions please leave a
comment below if you enjoyed that video
there are a few simple things you can do
to help me out if you are watching this
video on youtube click the like button
below the video and leave a comment with
any questions or feedback and please
don't forget to subscribe to my free
email newsletter to get more tips and
tricks that will help you learn Excel
thanks again for watching and I'll see
you soon