I abandoned hope for good Excel tutorials many years ago, they are usually the most terrible and unreliable videos out there, but you, you really did it.
My top tip for this, instead of the find and replace option, is to use Cell Styles to unlock cells. There’s a cell style called Input. Unfortunately I think MS have dropped the ball slightly on this as it would make sense to have this cell style default as unlocked. But it doesn’t, so you have to right click on it and modify, then tick that you want to include protection in the cell style, and then got to format, and unlock the style in the same way, you can also change its colour here. In this way, when you’re creating your spreadsheet, whenever you come across a cell you know should be unlocked, you can just give it the ‘input’ cell style, rather than making it blue, or yellow or whatever, and it will automatically be unlocked. You can also then use cell styles to quickly change the colour or borders of all your input cells if you need to. I’ve had it many times where I’ve made a big spreadsheet with lots of fairly random input cells, and I’ve lost track of what’s an input cell and what isn’t. And you have to go back through later and unlock them all. Best practice is to highlight then in some colour, even if that’s just default yellow for now. But using this method means you’re highlighting them, unlocking them and leaving the door open to quickly recolour them later if you want to. I just wanted to share that, as I was very pleased with myself when I worked it out. I hope that helps someone.
Hi @Edward Can you know how to lock entire worksheet if it is in unprotected mode with Method not with Event (VBA Code) it will be great help if any body can help me on this Struggling alot and also I am not looking related to Password specifically
Thank you for teaching us; I watched many of your videos, I admire your sensitivity and the simplicity and fluidity of how you delve into the topic! What a monument to knowledge! Thank you so much!
Best practice would be 1) Unlock the entire sheet (Ctrl +1) -> protection-> unlock 2) Select the columns or cells which you wanted to lock (Ctrl +1) -> protection-> lock 3) Protect sheet now (would suggest to always use a password for security) This works!!
Working in a rural Australia has many challenges, especially when finding quality learning resources. Subscribing to your channel is like access to the ultimate office encyclopedia. Thank you for unscrambling the chaos that sometimes is excel!!
Agree, the Locked feature was confusing to understand at first. It’s been a few years since I’ve needed to use it and this tip was refreshing. Also, great tip about find coloured cells! Thanks Leila!
Thanks for the great video. I've been using the lock cells feature for some time. An added bonus is that when you navigate the protected worksheet using the enter key, all the locked cells are automatically skipped. It's much quicker to move just among the cells that are editable. Cheers!
I totally agree, it took a lot of research to discover how to use the locked check box. That said cell level locking is something that I have frequently used to protect formulas and other critical contents against accidentally being changed.
Leila you are super amazing. I used to manually select the colored cells I wanted my employees to edit. Never used this FIND FORMAT feature. This is way to handy. Thanks a ton for sharing this trick. I have been following all your videos for couple of years now. You are like "one stop" for advanced Excel needs. Kudos :)
Use it a lot, normally with the option to only select unlocked cells, but especially the find with format is a cool way to save time I have not seen before, thanks
I knew how to lock and unlock, but learned something new today that you can use Ctrl + F to search the formatting too!! Thanks Leila, you are the best!!
Thank you! The LOCKED did confuse me before I watched your video. In the past, I never was able to lock cells because I thought the check mark should work. With your method, everything works well now. Thanks!
I just learned how to select shaded fields, that's pretty cool. I did learn about the lock cells and lock worksheet years prior on my own. I use it sparingly but it is mainly for shared files. It also does have it's limitations. If it's for just data entry, excellent! Includes copy pasting from other excel documents, then like on of the commenters on here says about it, it can relock cell or if you choose to not allow formatting, may not paste at all. It is great so far for just data entry, just be careful of copy/pasting.
If you save as a macro enabled workbook you gain access to the get.cell formula (no vba required). You can use that to check what the current format of the cell is and compare it against what you expect it to be. What I do is, if the format has changed it gives the user an error message in the calculated cells. All the calculated cells will say "PASTE ERROR" instead of the expected calculations. That way they know right away that they pasted in to a cell when they shouldn't have and can just hit undo and re-input the value without impacting the formatting.
I learned how to lock/unlock cells before. But I forgot. I was definitely doing it the hard way! This mini tutorial should save a lot of time. Thank you Leila! I learn how to solve a lot of excel problems watching your videos. 🤩
I learnt a new method of locking selected cells easily and fast with this video. I knew how to lock n unlock but was not fast enough to do it with a selected cells.
So, I did have some issues. I had to format the cells BEFORE I locked them. Selecting cells that you want to be locked, right click - format cells - lock (or unlock cells or block of cells). When all of your cells are formatted the way you want, THEN protect the sheet. This will have the cells you want locked, locked... and the ones you want unlocked, unlocked (or where ONLY cells can be adjustable)! Thanks for the tutorial!
Oh my goodness! I can't even calculate the amount of time I have killed doing this the hard way. That is a wonderful tip. I've been looking for a faster way to do this very task for a long time. Thanks so much for sharing!
Madam ! Your videos on excel are fantastic and easily understandable. A good RUclipsr on Excel. Could you please explain how to lock rows and columns after hiding them not to open by others.
I have learnt a lot from your presentations. Your explanations are usually clear and easy to follow. However, sometimes I have questions. Do you mind having a one on one question and answer?
I've used 'Find & Replace' many times ... but not 'Locking' cells. Your lesson is quite timely (again). Your presentation is perfect ... 👍... thank you ... thank you ... thank you.
Great video as always. I make a cell style that is a certain fill color and format it to NOT be locked. That way if I want to change the fill color, I just modify the cell style.
Hi. I actually already knew about locking cells, but I just wanted to thank you for the very informative videos. I adore excel and am quickly becoming the specialist at my job. You have my ideal job. I absolutely adore Excel and if I could do it all day I would!
I think that this is a perfect explanation. Thank you. I always wanted to protect just few cells instead of complete sheet, but I never knew how. Till now. 🙂
Nicely done! You have a teaching gift - making the complex simple. Perhaps you would consider presenting the process a little slower in your next video as I am an Excel rookie.
in college I needed a 100 cs class and it covered excel... I hardly learned anything in that class and got the wrong impression of Excel... Your vids have not only changed my opinion but pushed me further than that class ever could. TLDR: thanks
This guide is what I was thinking about right now, because I have made a worksheet and plan to share it with other colleagues. Thank you Leila ONCE AGAIN.❤️
I have used the lock and unlock function for cells before but this explanation was the best I've seen and definitely helps me. Now I'm going to go change a few things in my current project. 👍👍
Thank you so much! I was just playing around with the lock features to no avail on Wednesday and then just decided not to share my sheet at all. With this video, I’m equipped to go back to the drawing board.
I absolutely agree with you, it still is confusing and sometimes different to use, not mentioning limited options they offer. Changes to locking cells or unlocking them are long overdue MS, if you guys are reading this, please make an update and change it. Great video by the way!
Great video! I've used this feature before and find it very helpful. One problem I still seem to face is how to prevent cutting and moving unlocked cells which changes underlying formulas. Have you had any experience with this? Do you have videos related to this? Thank you.
If you save as a macro enabled workbook you gain access to the get.cell formula (no vba required). You can use that to check what the current format of the cell is and compare it against what you expect it to be. What I do is, if the format has changed it gives the user an error message in the calculated cells. All the calculated cells will say "PASTE ERROR" instead of the expected calculations. That way they know right away that they pasted in to a cell when they shouldn't have and can just hit undo and re-input the value without impacting the formatting.
This is great to know…..thank you and At the find step - I think you could also do find (blue fells) then replace with a blue cell but with unchecked locked box, to cut a step.
Grab the file I used in the video from here 👉 pages.xelplus.com/lock-unlock-cells-file
Whilst I knew how to lock cells, I had no clue I could select cells using the find function by colour - what an amazing time saving tip - awesome 👏
I can’t like this enough. This is one of the most counterintuitive concepts in Excel. Thanks for breaking it down so simply.
I abandoned hope for good Excel tutorials many years ago, they are usually the most terrible and unreliable videos out there, but you, you really did it.
My top tip for this, instead of the find and replace option, is to use Cell Styles to unlock cells.
There’s a cell style called Input. Unfortunately I think MS have dropped the ball slightly on this as it would make sense to have this cell style default as unlocked. But it doesn’t, so you have to right click on it and modify, then tick that you want to include protection in the cell style, and then got to format, and unlock the style in the same way, you can also change its colour here.
In this way, when you’re creating your spreadsheet, whenever you come across a cell you know should be unlocked, you can just give it the ‘input’ cell style, rather than making it blue, or yellow or whatever, and it will automatically be unlocked.
You can also then use cell styles to quickly change the colour or borders of all your input cells if you need to.
I’ve had it many times where I’ve made a big spreadsheet with lots of fairly random input cells, and I’ve lost track of what’s an input cell and what isn’t. And you have to go back through later and unlock them all. Best practice is to highlight then in some colour, even if that’s just default yellow for now. But using this method means you’re highlighting them, unlocking them and leaving the door open to quickly recolour them later if you want to.
I just wanted to share that, as I was very pleased with myself when I worked it out. I hope that helps someone.
Thanks for the tip!
Hi @Edward Can you know how to lock entire worksheet if it is in unprotected mode with Method not with Event (VBA Code) it will be great help if any body can help me on this Struggling alot and also I am not looking related to Password specifically
Great tip 👌
@@twinkle547 k
Thank you for teaching us; I watched many of your videos, I admire your sensitivity and the simplicity and fluidity of how you delve into the topic! What a monument to knowledge! Thank you so much!
Best practice would be
1) Unlock the entire sheet (Ctrl +1) -> protection-> unlock
2) Select the columns or cells which you wanted to lock (Ctrl +1) -> protection-> lock
3) Protect sheet now (would suggest to always use a password for security)
This works!!
You are saving my days! Additional learning how to select specific formatting.
Working in a rural Australia has many challenges, especially when finding quality learning resources. Subscribing to your channel is like access to the ultimate office encyclopedia. Thank you for unscrambling the chaos that sometimes is excel!!
Agree, the Locked feature was confusing to understand at first. It’s been a few years since I’ve needed to use it and this tip was refreshing. Also, great tip about find coloured cells! Thanks Leila!
Great to hear! Thanks Sue.
I have used this feature many times. What I liked about the tutorial is the method your used to select all the cells you needed to unlock. Thanks
Thank you for explaining things in a very logical and easy manner! I love your tutorial videos on EXCEL!
Thanks for the great video. I've been using the lock cells feature for some time. An added bonus is that when you navigate the protected worksheet using the enter key, all the locked cells are automatically skipped. It's much quicker to move just among the cells that are editable. Cheers!
Your timing is impeccable, I will be using these techniques later today.
Perfect!
I totally agree, it took a lot of research to discover how to use the locked check box. That said cell level locking is something that I have frequently used to protect formulas and other critical contents against accidentally being changed.
Thanks a lot Leila, i have tried few times to do the same and as you said got confused with lock cells option. This tutorial clarified it
Glad it was helpful!
Leila you are super amazing. I used to manually select the colored cells I wanted my employees to edit. Never used this FIND FORMAT feature. This is way to handy. Thanks a ton for sharing this trick. I have been following all your videos for couple of years now. You are like "one stop" for advanced Excel needs. Kudos :)
Glad I could help, Sachin!
Use it a lot, normally with the option to only select unlocked cells, but especially the find with format is a cool way to save time I have not seen before, thanks
I knew how to lock and unlock, but learned something new today that you can use Ctrl + F to search the formatting too!! Thanks Leila, you are the best!!
You always have something new to surprise me! Search by cell color format is new, yet another very useful tutorial. Thanks a lot...
Your videos are terrific. Your content is always clear and concise, and you always impart additional tips and tricks that delight your subscribers.
Thank you!
The LOCKED did confuse me before I watched your video.
In the past, I never was able to lock cells because I thought the check mark should work.
With your method, everything works well now. Thanks!
Glad I could help!
I just learned how to select shaded fields, that's pretty cool. I did learn about the lock cells and lock worksheet years prior on my own. I use it sparingly but it is mainly for shared files. It also does have it's limitations. If it's for just data entry, excellent! Includes copy pasting from other excel documents, then like on of the commenters on here says about it, it can relock cell or if you choose to not allow formatting, may not paste at all. It is great so far for just data entry, just be careful of copy/pasting.
If you save as a macro enabled workbook you gain access to the get.cell formula (no vba required). You can use that to check what the current format of the cell is and compare it against what you expect it to be. What I do is, if the format has changed it gives the user an error message in the calculated cells. All the calculated cells will say "PASTE ERROR" instead of the expected calculations. That way they know right away that they pasted in to a cell when they shouldn't have and can just hit undo and re-input the value without impacting the formatting.
I learned how to lock/unlock cells before. But I forgot. I was definitely doing it the hard way! This mini tutorial should save a lot of time. Thank you Leila! I learn how to solve a lot of excel problems watching your videos. 🤩
I'm using Excel on daily basis and your lessons are really helpful, thank you miss.
Excellent! This has confused me for years but you made the process crystal clear. Very well presented.
Glad you liked it!
Hi Leila, Each of your tips is fantastic. I always learn something new whenever I watch your videos. Thanks a lot
I learnt a new method of locking selected cells easily and fast with this video. I knew how to lock n unlock but was not fast enough to do it with a selected cells.
I normally use to select cells & unlock but the ctrl +F & replace option is very useful tip. Thanks
I did know about the lock/unlock feature but no the find by format and being able to select all. Great new tip. Thanks
Glad you found something useful.
Insanely Professional Video! Amazing clarity of information and easy to follow along. I only watched this video once to learn this function. Thank you
You are the BEST!! Your videos and tutorials are so easy to follow and understand. Thank you.
This is perfectly timed for something I am creating for work. What a life-saving (and time-saving) tip! Thank you!!
So, I did have some issues. I had to format the cells BEFORE I locked them. Selecting cells that you want to be locked, right click - format cells - lock (or unlock cells or block of cells). When all of your cells are formatted the way you want, THEN protect the sheet. This will have the cells you want locked, locked... and the ones you want unlocked, unlocked (or where ONLY cells can be adjustable)! Thanks for the tutorial!
whenever i need help in excel your tutorial is the best to option...thank you!
Lela, I did it many times and worked, but I did not know how had done 👍😀 but now I really appreciate you and understand...
Oh my goodness! I can't even calculate the amount of time I have killed doing this the hard way. That is a wonderful tip. I've been looking for a faster way to do this very task for a long time. Thanks so much for sharing!
Madam ! Your videos on excel are fantastic and easily understandable. A good RUclipsr on Excel.
Could you please explain how to lock rows and columns after hiding them not to open by others.
Selected colour cells was really nice shortcut, it's very good to learn new things from you Ma'am. Thank a lot 🙏
My pleasure 😊
I have learnt a lot from your presentations. Your explanations are usually clear and easy to follow. However, sometimes I have questions. Do you mind having a one on one question and answer?
You explain things well. Thank you.
Hi Leila
I had the same question about tick ✅ in lock and you’ve clarified it very well. Thanks heaps. You’re awesome 👏🏻
You’re welcome 😊
I've used 'Find & Replace' many times ... but not 'Locking' cells. Your lesson is quite timely (again).
Your presentation is perfect ... 👍... thank you ... thank you ... thank you.
Awesome, thank you!
I love the 'Find' the Fill color to select applicable cells.
Great video as always. I make a cell style that is a certain fill color and format it to NOT be locked. That way if I want to change the fill color, I just modify the cell style.
Hi. I actually already knew about locking cells, but I just wanted to thank you for the very informative videos. I adore excel and am quickly becoming the specialist at my job. You have my ideal job. I absolutely adore Excel and if I could do it all day I would!
You are so welcome!
That was a really smart move! Thanks for sharing!
I think that this is a perfect explanation. Thank you. I always wanted to protect just few cells instead of complete sheet, but I never knew how. Till now. 🙂
I Think the text below the locked function is what add to the confusion of this particular function of excel.Great Stuff Leila !!
Glad you like it!
Your videos have helped me so much at my work, thank you!
You are so welcome!
Nicely done! You have a teaching gift - making the complex simple. Perhaps you would consider presenting the process a little slower in your next video as I am an Excel rookie.
I never gave this a thought. Thanks for sharing!
Perfect timing, this is next on my to do list for a project at work, thank you
Finally I have learned to use locking/protecting cells. Thank you very much. I appreciate it. 👍🏻
You’re welcome 😊
As always, you do a great job in explaining the topic. I'm with you, the whole lock/unlock is a bit confusing but I'm in the know now thanks to you!
Thanks so much for that! I had exactly the same confusion you had in the beginning with the 'lock' definition
This is the most helpful video I have found on this topic. Thank you so very much!
I dont know my problem is just simple like that ... Thanks Leila, you are the best
in college I needed a 100 cs class and it covered excel... I hardly learned anything in that class and got the wrong impression of Excel... Your vids have not only changed my opinion but pushed me further than that class ever could.
TLDR: thanks
You just read my mind....
Thanks ❤️
My pleasure 😊
you made it simple and easy. Thanks for sharing the knowledge.
You're welcome!
Thanks for sharing your knowledge. Really appreciate the help.
Great job. So simple. You are excellent.
This guide is what I was thinking about right now, because I have made a worksheet and plan to share it with other colleagues. Thank you Leila ONCE AGAIN.❤️
I have used the lock and unlock function for cells before but this explanation was the best I've seen and definitely helps me. Now I'm going to go change a few things in my current project. 👍👍
Thank you, you explained it very well.
you're such an amazing teacher. kudos you deserve a billion subscriber
Thank you! 😃
Thank you so much! I was just playing around with the lock features to no avail on Wednesday and then just decided not to share my sheet at all. With this video, I’m equipped to go back to the drawing board.
I too, have found this confusing. Thank you for the understandable description and examples!
I knew about locked cells, but by chance I've learn about "how to find coloured cells!" Thanks!
This is amazing and will be so helpful at work tomorrow. Thank you so much!
I absolutely agree with you, it still is confusing and sometimes different to use, not mentioning limited options they offer. Changes to locking cells or unlocking them are long overdue MS, if you guys are reading this, please make an update and change it. Great video by the way!
as always Leilia, thank you for this.
I found Excel's descriptions for these functions totally confusing. Thanks to your tutorial I understand now, what I need to do at my work!
Locking of cells finally unlocked :) Thanks a lot Leila.
Brilliant! I need this in my life
This will save me so much time. I knew the video was gunna be genius!
Thank you, was looking for this for long time.
Glad I could help
Thank you for making it super easy to follow. I fell inspired to and interactive chart now.
Thanks-- this was really clear and helpful. You're a great teacher.
Wow! I love you! This is so helpful!
Exactly what I needed!!
You are such an awesome person.
Thank you so much
This is great Leila. I have sometimes found this confusing in the past, very helpful!
Thanks, Chris!
Amazing! I always learn something new in your videos! Cheers!
Exactly what i have been looking for. Thanks alot
Happy to help!
Thanks for the tutorial Leila !
Our pleasure!
I'd been reading the Excel help pages but was still confused until I watched this. Thanks so much!
Glad it was helpful.
Thank you so much for the prefect guidance
Great video! I've used this feature before and find it very helpful. One problem I still seem to face is how to prevent cutting and moving unlocked cells which changes underlying formulas. Have you had any experience with this? Do you have videos related to this? Thank you.
If you save as a macro enabled workbook you gain access to the get.cell formula (no vba required). You can use that to check what the current format of the cell is and compare it against what you expect it to be. What I do is, if the format has changed it gives the user an error message in the calculated cells. All the calculated cells will say "PASTE ERROR" instead of the expected calculations. That way they know right away that they pasted in to a cell when they shouldn't have and can just hit undo and re-input the value without impacting the formatting.
@@dgjanes917 Nice. I'll have to look into that and try that out next time I make/edit a database set up for someone. Thank you!
It was really helpful. Thanks for sharing.
wonderful! great explanation, thanks!
This is great to know…..thank you and At the find step - I think you could also do find (blue fells) then replace with a blue cell but with unchecked locked box, to cut a step.
You are so helpful Leila
Yeah, it was totally confusing by MS Excel, You done it easy for us through your finding. Appreciate, keep on going
Thank you for explaining this perfectly!
You have saved me so much time and effort with your videos. Thank you so much. (Also your courses are brilliant!)
Well explained, Thank You and I am grateful to you.
You are very welcome
Thank you Leila for another informative lesson
I don’t always have to set everything for clients again thanks so much