How to edit A Cell Without Clicking
Lock Cells & Protect Excel Worksheet - EVEN by Cell Color!
0:00 / 0:00
Quality:
Sharing buttons:
Today, let's take a look at how
we can lock and unlock specific areas on an Excel sheet.
So we're going to take a look at the basics,
but the case that we're dealing with today has a twist.
This was a question from one of my students, Anya,
who asked, "I'd like to leave yellow cells unprotected.
"Is there a way without a VBA macro to get this done?
"This way, I can move from one unprotected yellow cell
"to the next one with the Tab key,
"and it would save me a lot of time entering data."
Can we do this without VBA?
(mellow hip-hop music)
First off, let's cover the basics for protecting
and unprotecting your worksheet.
You do that in the Review tab.
Under Protect here, you can protect the entire sheet
by clicking on this.
You probably would want to give this a password,
and you have to confirm that password.
Everything is fully protected,
so if you try to input anywhere in this sheet,
you get this popup that it's protected,
and the user can only unprotect it
if they know the password.
Now in addition to this,
you have the ability to unprotect some of the cells
and leave the other ones protected.
We can do it in different ways.
One method is to use Allow Edit Ranges.
Now this gives you some additional options
so you can set different passwords for different ranges,
but in case we just want a simple solution,
all we have to do is to select the cells
that we don't want protected, right mouse click,
go to Format Cells,
or use the shortcut key, Control + One.
Under Protection here, you need to take away
the check mark beside Locked.
Once you do this,
nothing happens until you protect the worksheet,
so at this point, nothing is protected.
I can input everywhere.
So I can input here, and I can input here.
This kicks in the moment you protect the sheet,
so now I'm going to go click on Protect.
This time, I'm just not going to give it a password
and click on OK.
So when I attempt to input something here,
it's still protected, I can't input,
but I should be able to input here, and I can.
And of course, you can select different areas
and take away that tick mark
to have the different areas unprotected
once the sheet is protected.
So let me unprotect this
and just put back that check mark for all the cells.
So when you get this symbol in there,
it means there is a mix.
Some cells are protected, some cells aren't.
I'm just going to put back the check mark
to go back to the original default state.
Now let's come to the complex case.
My aim is to protect all the cells here
except the yellow cells.
These cells are going to be my input cells,
and normally, when you have an Excel table
and you're inputting data,
so let's say I put 23, I press Tab,
that takes me to the next cell.
Tab takes me to the next cell.
When I protect these gray cells
and I leave the yellow ones unprotected,
this means when I press Tab inside a yellow cell,
it's going to jump to the next unprotected cell,
which is going to be this yellow cell
and then this yellow cell.
What I want to do is to unprotect all the yellow cells.
The time-consuming way of doing this
is to manually select all these yellow cells,
so hold down the Control key, select them,
and then, take away that check mark,
but this is going to cost me a lot of time
if I have to do this on a large area like this one.
The faster way of doing this is this.
We're going to highlight the range
where we're going to be inputting data,
and we're going to use the Find feature.
So let's press Control + F.
Under Options here, we get the ability
to add a specific format.
Select Choose Format From Cell
and go to one of these yellow cells.
Now just keep in mind that
when you take the cell formatting in this way,
it doesn't just take into account the color,
but the entire formatting of the cell,
so also the number formatting that's behind this.
Click on Find All.
It finds everything here,
and I want to highlight it in my spreadsheet,
so I'm going to press Control + A
to highlight these results,
which is going to end up highlighting them
on the sheet right here,
and let's just close our dialog box.
Now what I'm going to do is use the shortcut key Control + 1
to go back to Format Cells,
go to Protection, take away the check mark beside Locked.
Click on OK.
Now let's protect the sheet.
This time, I'm not going to give it a password,
and let's test.
Let's input a number here and press Tab.
Jumps to the next yellow cell.
Tab, Tab.
This way, I can input data so much easier.
So this was my approach.
If you can think of any other methods,
share it with me in the comments below.
This was our locking and unlocking Thursday fun.
If you liked it, give it a thumbs up,
and if you haven't subscribed to this channel
and you want to improve your Excel skills,
consider subscribing.
(upbeat percussive music)
![How to disable double clicking a cell to edit on a list view in ServiceNow [Paris]](https://i.ytimg.com/vi/woTmNZ3nRk0/mqdefault.jpg)
