Lock Cells & Protect Excel Worksheet – EVEN by Cell Color!

Lock Cells & Protect Excel Worksheet – EVEN by Cell Color!


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)

100 Replies to “Lock Cells & Protect Excel Worksheet – EVEN by Cell Color!”

  1. Hi liela Gharani, very nice video, I like your all videos👍👍, sorry madem if you don't mind I want ask one small question, how to hide formula without sheet lock

  2. I found a macro to lock specific colored cells. But I can’t teach anyone to use it. Your idea might be easier to digest.

  3. I thought I was an advance Excel user until I found this channel. There are so many good tricks and the explanation and video editing is so perfect.
    THANK YOU

  4. Hi Leila.. great tricks.. always something new and innovative from your channel. Just for fun, I recorded a macro and cleaned it up to accomplish a similar goal (go to next yellow fill cell) without the need of protecting/unprotecting cells/worksheets, as follows:
    Sub Find_Next_Yellow_Cell()

    Application.FindFormat.Clear
    'to clear any prior find formats
    Application.FindFormat.Interior.Color = 65535 'standard yellow fill

    Cells.Find(What:="", _

    After:=ActiveCell, _

    LookIn:=xlFormulas, _

    LookAt:=xlPart, _

    SearchOrder:=xlByRows, _

    SearchDirection:=xlNext, _

    MatchCase:=False, _

    SearchFormat:=True) _

    .Activate

    End Sub

    This could be assigned to a key combo or button for easy advancement to the next cell. One advantage of the VBA method is the flexibility to change colors or other cell attributes on which to find and activate. Depends on the user's comfort and or familiarity with VBA. If not comfortable, they should take your course :))
    I did this just for the exercise of thinking in VBA. Still love your method in the video. Thanks and Thumbs up!

  5. Hi Leila, great video like always , is there a way to protect just some cells without protecting the entire sheet ? I have a combination of BBA Bottoms and Macros that doesn't work when I protect the entire sheet, but I need to protect some cells. Thanks.

  6. Thanks Leila. This is a great trick for in unprotecting specific cells after you've set your sheet up and is a great reminder of the power of Find! If you know ahead of time that you want to do this, I typically create a custom cell style which not only sets the background and font colours, but also the cell protection status. One can then apply that cell style whenever you would have coloured a cell yellow.

  7. 2 question, do you prefer using VBA or just formulas?… I have to take account of the skill levels of IT just in case IT gets lost in reading VBA codes lol, I have to make it simple for them also. Is 365 backwards compatible with older versions, read some articles blogs… companies are having issues with it somehow… thanks again… awesome teacher!!!

  8. Thank you for this tip! I create tools where others can only edit yellow cells. This will save me a lot of time!

  9. please make video on my following query please
    How to we can protect excel sheet completely?
    Because there are so many ways available on internet to unlock protected sheets (including copy paste and vba code method)
    Or there is no way through which can protect a sheet with full assurance

  10. Hi Leila,

    Thanks for providing learnings in simplified manner.

    Your videos are convenient & easy to understand.

    I was having a doubt could it be possible for you to make a video on it, it would be really appreciated.

    Question:

    *Suppose there is data in sheet 1 as x1 and y1 and I need only data of x1 data which is present in column a,d,e,h,m. I want data to be transfered from sheet 1 to sheet 2 in same excel without using VBA with single click ( I don't want to copy data from each column from sheet 1 and then go to sheet 2 to paste the data) or any simplified way you could suggest to perform the task?

    Thanks
    Rohan

  11. Lovely. I found it did not work if the cells to find are conditionally formatted, but that's a different problem for another day!

  12. This did n't work initially for me. Then I started again from the scratch. It's working now. This is really cool!!! and would be of use in future. I can shine as an excel expert in my office with your awesome excel tips.

  13. Awesome! Perfect timing as i'm currently building similar worksheet. This will save me a lot of time! Tqvm!

  14. Nice one. Thanks. However, my data capture sheet is coloured based on the value imputed in the preceding cell. In this case, the cell colour is conditional. It may be yellow or not based on the value of sales (for example). Is there any way of applying similar trick if the cells lock/unlock is conditional?

  15. Hi Leila.
    Congrats for your MVP.
    Fantastic series on excel. I learn many many new concepts.

    If you make video on excel Forms i will be very great full to you. Thanks

  16. Good Day! i am very fascinated in the simplicity of your explanation… I am looking for more. Actually , i dont have any formal lessons about excel. i just watch random vids online whenever needed… i hope you could help me in having the techniques in charts… is there a way that we could paste the chart and automatically updates its data reference in the same worksheet? do you already have a video for that? i need the link… thanks and God bless! 🙂

  17. Superb gharani. I have one , how to count the number of working days in month based on month name not on starting date & end date with year slicer

  18. Dear Madam,
    You are beautiful and your intelligence too. Nice presentation with beautiful dresses, appreciate your sincerity and dedication; really.
    Anyway, I need suggestion, which formula to be used for below.
    I have 3 different tables, Quotation, Sales Order and Sales Invoices. Some sales person has no Sales Order too.
    I need 2 drop down box, 1 to select Sales Person and 2nd to their relevant tables, appear as selected.
    Hope you will give me appropriate suggestion in detail. I'm not that much advance in Excel, so please give detailed explanation.
    Thank you very much in advance

  19. Hi Leila, Thanks for sharing this feature, I follow all your videos regularly.

    1) I use office 365 business premium for MAC, I only have a MAC environment, When i try the find feature on my version of excel, I do not get the find FORMAT based search. Am I doing something wrong? Or do I belong to the underprivileged and I don't get all features despite paying the subscription fee to microsoft?

    I do have most of your courses on UDEMY as well from my separate email account.

  20. The question below is nothing related to this topic, but I am badly in need a solution to my below query.
    Can I get the vba code for the below formula please?

    =IF(A2,UPPER(LEFT(D2,3)&LEFT(C2,3)&TEXT(ROWS(A$2:A2),"0000")),"")

    I need to extract first three digit/number from column D2 & C2 and as it goes down it should increase as 0001, 0002, 0003… so forth. Eg. B113X40001, B114X60002 (The first six digit vary depending on what the D2 & C2 column has in it)

    Thanks

  21. Need some assistance with Excel formula, in col A, I have months. In col B I have revenue spent against the months. I want to give bonus in Col c whenever total spend is 50000. If Jan has 10k, Feb has 40k then total becomes 50k and I want to add 500 in col c,

  22. Hello Leila, do you know how to make a dynamic sankey diagram ? I think it can be done modifying the funnel chart somehow. Thank you for your time!

  23. Thank you so much for all your videos. Deeply appreciated. I have a question about restricting format in a unlocked cell. I want to be able to enter text and numbers in a cell while the rest of the sheet is locked. But i dont want to allow format change in the cell. That usually happens when you copy paste.
    Do you have any idea in how to approach this?
    Thanks
    Lars

  24. Queen of Excel, the more I watch your excel tutorials, the more I feel I know very little in excel. Please keep up the good work. I can't wait to have another new release from u.

  25. Is it possible to use a dynamic range of yellow cells. I mean, the cell color change according to contain (for example the cell is yellow if it's empty) and each time yellow cells should be unlocked automatically

  26. A really funny tips…

    Then we can continue the protection lesson: first with the protection of the workbook, second with the protection of the sheet and finally with the validation data… Goal: guidance of the user to a correct encoding.

    Your tips will replace my "very old tips" for the zone protection (using the validation data to protect cells".
    Thanks a lot!

  27. HI LEILA.
    I HOPE YOU ARE OKAY. I NEED A HELP. CAN YOU PLEASE TELL ME HOW TO IGNORE A CELL (THAT IS NOT BLANK) DURING CONCATENATE.

  28. Excuse me, I wanna make an interface of a single row to users with a submit button , and in every single entry with submit, this row is added to the database as a new row (sheet protected).
    How can I make that button and the equation of new entry to database
    My boss wants to use access but i believe in excel

  29. I request you to guide me how to append data below existing data. Supposing I have 5000 email Ids in one file and another file has 3000 email Ids. I want 8000 to appear in one single file. I have watched a lot of videos but they all talk about power query type data. I want to add / apoend / copy below the existing data by importing from another worksheet to make a larger file. HC ARORA

  30. Great Help
    Thank You Save My Lot Of Time
    Mis Leilaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    😘

  31. Hello Leila!
    Great work and your videos are awesome!! Thanks a lot. I have two questions if you can help me with that. Is there a way to conditionally lock a cell without using VBA? I mean I want the user to enter a value in a cell and then based on that value or text I want to lock another cell in the sheet. Secondly, is there a way to lock a cell AFTER a value is entered in it without using VBA?

Leave a Reply

Your email address will not be published. Required fields are marked *