Grab the file I used in the video from here 👉 pages.xelplus.com/lock-unlock-cells-file
@giyah60382 жыл бұрын
Not only did I learn how to lock/unlock cells, but also learned how to select only shaded cells. Another really easy and helpful tutorial. Thank you!
@zayaneats99302 жыл бұрын
Gi Yeh...❤️❤️❤️❤️❤️❤️
@Jaque12345Jaque2 жыл бұрын
Same here. Super helpful!!
@فخامتيتكفيني-ض8ق2 жыл бұрын
خخخهههههههههههههههخههههخهخههههههخخهت
@wakelingchicken2 жыл бұрын
Hi Leila My Top Tip if you're doing a lot of work locking / unlocking cells is to put the "Lock Cell" button into the quick access toolbar. It then acts as a visual indicator as to whether the highlighted cell is locked or unlocked and allows you to quickly change the setting without going into the Ctrl-1 formatting dialogue. I hope that's helpful for a few people :) Andy
@LeilaGharani2 жыл бұрын
I like that! Thanks for sharing Andy.
@card1_diy2 жыл бұрын
That got me confused too. Im gonna practice
@tiongah76902 жыл бұрын
that needs macro right?
@wakelingchicken2 жыл бұрын
No@@tiongah7690 , it doesn't need a macro. In the desktop app, above the menu bar, in the title bar top left, you should see the 'quick access toolbar', and on the right of that is a drop down icon that allows you to customise that toolbar. You simply need to add the 'Lock Cell' button from the 'Home Tab'. Maybe Leila will include this tip in a future video for you :)
@keijo4562 жыл бұрын
@@wakelingchicken Thank you for the tip!
@danielcarr29752 жыл бұрын
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 👏
@lorikmor2 жыл бұрын
I can’t like this enough. This is one of the most counterintuitive concepts in Excel. Thanks for breaking it down so simply.
@rlibby404 Жыл бұрын
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.
@broderp3 ай бұрын
I hope you are paid very well in some capacity for for your awesome tutorials.....You're methods are to the point, easy to understand and are exactly what I need. Thank you!
@LeilaGharani3 ай бұрын
I appreciate that!
@TheLikkebaardАй бұрын
Best video I saw ever explaining a(nother) confusing aspect of Excel, your explanation makes it so easy to follow!!
@DinoDelight2 жыл бұрын
I use locked cells loads, but always untick the "select lock cell option" that way users can't even select the locked cells this avoids that annoying error pop up. And if you use filters, make sure that option is unticked that way users can use the filter even though the cell will be locked
@asianseyesRpretty22 жыл бұрын
What is lock cells loads?
@balakumaran752 жыл бұрын
Very nice suggestion
@radhaganesan33362 ай бұрын
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!!
@TigerTedd2 жыл бұрын
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.
@LeilaGharani2 жыл бұрын
Thanks for the tip!
@twinkle5472 жыл бұрын
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
@larwhelan21 Жыл бұрын
Great tip 👌
@lyaw8086 Жыл бұрын
@@twinkle547 k
@siyabulelatshali10122 жыл бұрын
I literally just googled this. Your video's are always relevant. Thank you.
@LeilaGharani2 жыл бұрын
Happy to help!
@JayBee-hb2pm2 жыл бұрын
Everything you post about Excel is so relevant to my job role. I just love how you come up with things that make our lives so much easier Leila. Thank you for sharing yet another great tip
@TarunSharma-ll8ge2 жыл бұрын
I make excel calculators a lot and I use this feature but this color coding will save a lot of my time as I used to manually untick the 'locked' for each cell. Thanks for this video.
@silviopontesx2 жыл бұрын
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!
@DeltaSanjula2 жыл бұрын
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!!
@sulemanhassanali Жыл бұрын
You are saving my days! Additional learning how to select specific formatting.
@suecampanelli3112 жыл бұрын
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!
@LeilaGharani2 жыл бұрын
Great to hear! Thanks Sue.
@vincentcollin64902 жыл бұрын
We can add that when protecting the sheet, we can uncheck the box "Select locked cells". And therefore leave only the unlocked cells accessible.😉 Thank you for this lesson.
@michaelcorvin25 Жыл бұрын
Thank you for explaining things in a very logical and easy manner! I love your tutorial videos on EXCEL!
@mcd226302 жыл бұрын
Your timing is impeccable, I will be using these techniques later today.
@LeilaGharani2 жыл бұрын
Perfect!
@keijo4562 жыл бұрын
Wow! This is just what I need. Perfect timing! I didn't know about find and replace works with cell formats. Great video as always Leila.
@LeilaGharani2 жыл бұрын
You're very welcome!
@patihrizal98802 жыл бұрын
Yeah what a timing
@roywilson95802 жыл бұрын
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.
@DennisYeadon2 жыл бұрын
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
@gcecg2 жыл бұрын
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!
@fungkiem8 ай бұрын
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!
@LeilaGharani8 ай бұрын
Glad I could help!
@unkchaos20072 жыл бұрын
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.
@dgjanes9172 жыл бұрын
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.
@pambackal2 жыл бұрын
You always have something new to surprise me! Search by cell color format is new, yet another very useful tutorial. Thanks a lot...
@MarceColucci2 жыл бұрын
This was brilliant! I've been looking for a way to do this for ages and this explained it so clearly and easily!! thank you Leila.
@jayshreebhakta99372 жыл бұрын
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!!
@jeffrothman28333 ай бұрын
Excellent! This has confused me for years but you made the process crystal clear. Very well presented.
@LeilaGharani3 ай бұрын
Glad you liked it!
@unnikrishnansanthosh2 жыл бұрын
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
@LeilaGharani2 жыл бұрын
Glad it was helpful!
@colormile-vid88882 жыл бұрын
OMG Leila, I was using this lock/unlock yesterday and finally understood it! It took me awhile to truly get it. However I ran into an issue with shapes objects in my macro. How do I unprotect it so it works. Thanks again and as always I learn everyday from your video tutorials.
@DinoDelight2 жыл бұрын
Prob a little to late, but at the start of the macro, un protect it then at the end of the macro re- protect it ActiveSheet.Protect ActiveSheet.Unprotect
@ThomTydeman5 ай бұрын
Your videos are terrific. Your content is always clear and concise, and you always impart additional tips and tricks that delight your subscribers.
@ivanbork41752 жыл бұрын
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
@thushanthaperera51462 жыл бұрын
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.
@laurituttle4718 Жыл бұрын
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!
@Whiskey.T.Foxtrot2 жыл бұрын
Thanks! My problem with locking is with providing clients with a complex spreadsheet where they need to add or delete rows of data in a table. With locking enabled they can't do that. With locking disabled they invariably overwrite a formula and crush the model.
@nairkrishnadas2 жыл бұрын
Hi Leila, Each of your tips is fantastic. I always learn something new whenever I watch your videos. Thanks a lot
@michaelmwakawele7147 Жыл бұрын
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?
@RussellWardАй бұрын
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.
@sachinrv12 жыл бұрын
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 :)
@LeilaGharani2 жыл бұрын
Glad I could help, Sachin!
@Festus1712 жыл бұрын
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!
@donaldmcintosh65452 жыл бұрын
Hi Leila - Great tip, thanks. It has always been one of my bug-bears when handing out excel files to my customers.
@silverlicious20862 жыл бұрын
Always a good idea to protect certain cells. Especially those that have complex formulas. Have a nice day Leila.
@lukmanayodeji95632 жыл бұрын
You are really amazing @Leila. I will always be glued to your channel. Triple thump up for you my Mentor. Thanks for your Tutorials. Alhamdulillah, they had really transformed me to Pro Excel User.
@reedschmelzle37102 жыл бұрын
Insanely Professional Video! Amazing clarity of information and easy to follow along. I only watched this video once to learn this function. Thank you
@chh88602 жыл бұрын
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.
@LeilaGharani2 жыл бұрын
Awesome, thank you!
@oedipamaas86602 жыл бұрын
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. 🤩
@juliad3684 ай бұрын
Really clear explanations thank you ! Watched several other videos before yours and could not understand the explanations.
@LeilaGharani4 ай бұрын
Glad it helped!
@mimicat30652 жыл бұрын
Aaaaaaww! Gosh.. so simple.. It took me quite a lot of select cells, unlocking and protect/unprotect.. Wow.. you explained it the easiest way.. Super thank you with video.. i love it.. Mabuhay! 🇵🇭🇵🇭🇵🇭🇵🇭🇵🇭🇵🇭 👏👏👏👏👏👏
@bhanubethala542 жыл бұрын
Selected colour cells was really nice shortcut, it's very good to learn new things from you Ma'am. Thank a lot 🙏
@LeilaGharani2 жыл бұрын
My pleasure 😊
@spoondi12 жыл бұрын
I normally use to select cells & unlock but the ctrl +F & replace option is very useful tip. Thanks
@Freemarkets12362 жыл бұрын
I did know about the lock/unlock feature but no the find by format and being able to select all. Great new tip. Thanks
@LeilaGharani2 жыл бұрын
Glad you found something useful.
@ahumblemuslim30092 жыл бұрын
I'm using Excel on daily basis and your lessons are really helpful, thank you miss.
@ScottLititz2 жыл бұрын
#facepalm. Being in finance, I regularly use lock/unlock. But I had forgotten about Find using formats. With all the powerful things Excel has, it's good to be reintroduced to the oldies. Thank you Leila.
@LeilaGharani2 жыл бұрын
My pleasure :)
@kishangzp Жыл бұрын
very helpful tips... I used to be thiniking I know so much in excel and power bi... but now I have realised ... there are lots of things that I don't know, and there will be lots of things that I will never know... :)
@rajanbabubhagavatham1452 Жыл бұрын
Madam ! Your videos on excel are fantastic and easily understandable. A good KZbinr on Excel. Could you please explain how to lock rows and columns after hiding them not to open by others.
@patweyandt12372 жыл бұрын
You are the BEST!! Your videos and tutorials are so easy to follow and understand. Thank you.
@suhaibkhan3222 жыл бұрын
Lela, I did it many times and worked, but I did not know how had done 👍😀 but now I really appreciate you and understand...
@abdulsaidmkamara73052 жыл бұрын
I Think the text below the locked function is what add to the confusion of this particular function of excel.Great Stuff Leila !!
@LeilaGharani2 жыл бұрын
Glad you like it!
@dekebell330710 ай бұрын
You're an amazing resource, thanks so much for sharing! I ALWAYS learn more than just the lesson you're teaching.
@BikesarelifeIndia Жыл бұрын
Finally I have learned to use locking/protecting cells. Thank you very much. I appreciate it. 👍🏻
@LeilaGharani Жыл бұрын
You’re welcome 😊
@michaelstamos5510 Жыл бұрын
Leila - you are such a champion - thank you for your amazing guidance
@Cracktune2 жыл бұрын
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
@davidstonebarger933011 ай бұрын
I'd been reading the Excel help pages but was still confused until I watched this. Thanks so much!
@LeilaGharani11 ай бұрын
Glad it was helpful.
@mamamtl2 жыл бұрын
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!
@LeilaGharani2 жыл бұрын
You are so welcome!
@augusto7274 ай бұрын
Fantastic! Thank you for your help. I was bedeviled by erasing my formulas in cells that I am not supposed to touch. Thank you so much.
@tomwelt18252 жыл бұрын
I love the 'Find' the Fill color to select applicable cells.
@campopaulana2 жыл бұрын
Amazing!!! thank you so much. It was very confusing to get the right locks before this video.
@marianb22742 жыл бұрын
This is perfectly timed for something I am creating for work. What a life-saving (and time-saving) tip! Thank you!!
@mcmielnik2 жыл бұрын
I knew about locked cells, but by chance I've learn about "how to find coloured cells!" Thanks!
@samrathna68852 жыл бұрын
Hi Leila I had the same question about tick ✅ in lock and you’ve clarified it very well. Thanks heaps. You’re awesome 👏🏻
@LeilaGharani2 жыл бұрын
You’re welcome 😊
@nataliameyer494710 ай бұрын
Agreed with you that locking & unlocking a bit confusing . Nevermind , many thanks for simplifying it for me
@LeilaGharani10 ай бұрын
Glad it helped
@daitan963 Жыл бұрын
whenever i need help in excel your tutorial is the best to option...thank you!
@dilleshragolu88872 жыл бұрын
You just read my mind.... Thanks ❤️
@LeilaGharani2 жыл бұрын
My pleasure 😊
@humansizedaperture2 жыл бұрын
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.
@davidfriesen9606Ай бұрын
Wow thank you so much for this explanation you saved me. May God bless you. Take care
@patrickrolland7496 Жыл бұрын
Thanks so much for that! I had exactly the same confusion you had in the beginning with the 'lock' definition
@VishwasKadrolli2 жыл бұрын
I struggled a lot to ensure the sheet is locked and other dont input values other than cells meant to, but your video makes it so simple, I am gonna use it all through out my work. Thanks for the tip!
@ahmedrifayi8257 Жыл бұрын
Yes , I know many formulas in excel but this protection was confusing like you mentioned but you cleared it now Thanks 😊
@ambarilhamchannel2 жыл бұрын
A great sharing, you are not only tell us how to protect cell but also give us short cut menu to use that
@TP0145632 жыл бұрын
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!
@tanis2162 Жыл бұрын
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!
@AngelEstebanVlog2 жыл бұрын
You are the best! I couldn't figure that out today, and you solved my problem in an instant : )
@LeilaGharani2 жыл бұрын
Happy to help, Angel!
@antoinettehall6645 Жыл бұрын
Perfect!!! You answered my EXACT questions when I needed to create a timesheet template. Thank you for sharing. 👍🏾💪🏾👏🏾😁
@LeilaGharani Жыл бұрын
Glad it was helpful!
@sodbuster4411 Жыл бұрын
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.
@mbahdjojo2 жыл бұрын
I dont know my problem is just simple like that ... Thanks Leila, you are the best
@Michael_NV2 жыл бұрын
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. 🙂
@jumpshot6924 Жыл бұрын
you're such an amazing teacher. kudos you deserve a billion subscriber
@LeilaGharani Жыл бұрын
Thank you! 😃
@JeremyMcMahan2 жыл бұрын
I too, have found this confusing. Thank you for the understandable description and examples!
@mattschoular88442 жыл бұрын
Thanks Leila. The Find and Replace trick is a great idea. I use Locked Cells /Protect Sheet / Protect Workbook for muti user templates often. Employee Expense forms, Customer Credit Apps etc...
@LeilaGharani2 жыл бұрын
👍
@jessicaarend2172 жыл бұрын
This is the most helpful video I have found on this topic. Thank you so very much!
@mohammedhassan28082 жыл бұрын
Yeah, it was totally confusing by MS Excel, You done it easy for us through your finding. Appreciate, keep on going
@sueblakey1858 Жыл бұрын
This was of great help to me in creating a quiz and locking certain cells, thank you.
@notreallyhere11362 жыл бұрын
Perfect timing, this is next on my to do list for a project at work, thank you
@jonminer98912 жыл бұрын
Hi, Leila. It was a great lesson. Thanks for sharing! Stay healthy!
@chrism90372 жыл бұрын
This is great Leila. I have sometimes found this confusing in the past, very helpful!
@LeilaGharani2 жыл бұрын
Thanks, Chris!
@UNIVFranco2 жыл бұрын
Your videos have helped me so much at my work, thank you!
@LeilaGharani2 жыл бұрын
You are so welcome!
@kroyle53282 жыл бұрын
Didn't know you could select formatted cells, that will save me loss of time. Thanks great video.