How to Lock Cells 🔒in Excel to Protect your Formulas & Only Allow Input where Needed
Vložit
- čas přidán 2. 06. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Learn how to lock cells in Excel for creating foolproof templates! This tutorial is ideal for those who want to ensure data integrity in shared documents.
⬇️ Download the workbook here: pages.xelplus.com/lock-unlock...
✅ What You'll Learn:
▪️ Setting up cell protection: A guide to using Excel's Review tab for sheet protection.
▪️ Password protection: How to add and remove passwords for extra security.
▪️ Formatting cells for protection: Detailed instructions on selecting and locking cells.
▪️ Customizing cell locking: Techniques for locking only specific cells or ranges.
▪️ Utilizing 'Find and Replace' for efficiency: A unique method to select cells based on color for protection.
▪️ Practical tips: Insights on protecting your formulas while allowing inputs in designated areas.
Ever created this perfect shared Excel template only to come back to a total disaster? Collaborating on shared spreadsheets can be painful. But it doesn't have to be that way! In this video I'll show you how you can lock and unlock any cell, or a whole range of cells, to keep your work protected. This way you'd be able to protect your formula cells and allow people to only input in specific ranges or cells. I'll also show you a cool Excel trick which allows you to multi-select cells based on their color and then lock those cells only.
00:00 How to protect cells in Excel
03:20 How to Multi-Select cells based on Color
05:29 Wrap Up
🎬 LINKS to more Excel Basic videos: • Excel Introduction - L...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
Grab the file I used in the video from here 👉 pages.xelplus.com/lock-unlock-cells-file
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!
Gi Yeh...❤️❤️❤️❤️❤️❤️
Same here. Super helpful!!
خخخهههههههههههههههخههههخهخههههههخخهت
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.
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
I like that! Thanks for sharing Andy.
That got me confused too. Im gonna practice
that needs macro right?
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 :)
@@wakelingchicken Thank you for the tip!
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 👏
Great tip for managing Excel protection
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
What is lock cells loads?
Very nice suggestion
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
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!
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.
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!!
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 for sharing your knowledge. Really appreciate the help.
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.❤️
Thank you for explaining things in a very logical and easy manner! I love your tutorial videos on EXCEL!
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.
PERFECT DELIVERY-CLEAR AND CONCISE
You explain things well. Thank you.
I never gave this a thought. Thanks for sharing!
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!
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
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.
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!
I literally just googled this. Your video's are always relevant. Thank you.
Happy to help!
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. 👍👍
Your videos are terrific. Your content is always clear and concise, and you always impart additional tips and tricks that delight your subscribers.
Thanks for the excellent content. Learned everything I needed to know in minutes!
Yay! That's great to hear!
"Lock your cells so they don't delete your formulas." Yeah, they, exactly! THEY gotta stop doing that to me! Also THEY need to stop drinking all my caffeine by 10am!
This is perfectly and super useful, I have always wanted to know how to do precisely this and as a bonus you showed me a cool way to select a bunch of cells that aren't contiguous! Thanks again Leila!!! :)
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.
You are saving my days! Additional learning how to select specific formatting.
This is perfectly timed for something I am creating for work. What a life-saving (and time-saving) tip! Thank you!!
This women is a wizard in Excel.
You always have something new to surprise me! Search by cell color format is new, yet another very useful tutorial. Thanks a lot...
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. 🤩
You saved my day! Excellent work!🙂
Thank you. Very helpful and concise.
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
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!
🇵🇭🇵🇭🇵🇭🇵🇭🇵🇭🇵🇭
👏👏👏👏👏👏
Exactly the info i was looking for! thank you!
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
as always Leilia, thank you for this.
Great video. Right to the point. Thank you so much!! Exactly what I needed.
Your expertise are great..Thank you for your time and videos
Hi, Leila. It was a great lesson. Thanks for sharing! Stay healthy!
Very nicely presented. Thank you. I liked the formatting by colour.
As always easy and clear explanation. Thanks
You're an amazing resource, thanks so much for sharing! I ALWAYS learn more than just the lesson you're teaching.
Yes. This is super helpful. Thank you so much Leila.
You just read my mind....
Thanks ❤️
My pleasure 😊
Hi Leila, Each of your tips is fantastic. I always learn something new whenever I watch your videos. Thanks a lot
Leila - you are such a champion - thank you for your amazing guidance
I love the 'Find' the Fill color to select applicable cells.
That was a really smart move! Thanks for sharing!
You are the BEST!! Your videos and tutorials are so easy to follow and understand. Thank you.
Yes , I know many formulas in excel but this protection was confusing like you mentioned but you cleared it now
Thanks 😊
Really clear explanations thank you ! Watched several other videos before yours and could not understand the explanations.
Glad it helped!
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!
You always simplify stuff. Love it
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... :)
Very useful. Thank you!
Thanks-- this was really clear and helpful. You're a great teacher.
I was really looking for this Leila. Thank you😊
Always a good idea to protect certain cells. Especially those that have complex formulas. Have a nice day Leila.
whenever i need help in excel your tutorial is the best to option...thank you!
Thank you for explaining this perfectly!
Madam ! Your videos on excel are fantastic and easily understandable. A good CZcamsr on Excel.
Could you please explain how to lock rows and columns after hiding them not to open by others.
You have saved me so much time and effort with your videos. Thank you so much. (Also your courses are brilliant!)
Your timing is impeccable, I will be using these techniques later today.
Perfect!
Amazing! I always learn something new in your videos! Cheers!
Good explanations. I used it since last century 😊 Sometime, I protect with no password, to give opportunity to people to change the structure if they prefer, for example changing the order of the columns, but having the protection prevent any unintentional change. Of course, it is situation where I don't need to aggregate results after.
Perfect timing, this is next on my to do list for a project at work, thank you
Such a big help. Thank you so much for this.
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!
quick and simple explanation, thank you.
Thank you, again, for your tips. Youre a life saver
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.
Locking of cells finally unlocked :) Thanks a lot Leila.
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
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!!
Great Help. Thank you, Leila.
This is amazing and will be so helpful at work tomorrow. Thank you so much!
Leila ♥️
Thanks 😎👍
Just what I was looking for my workbook.
Insanely Professional Video! Amazing clarity of information and easy to follow along. I only watched this video once to learn this function. Thank you
This is the most helpful video I have found on this topic. Thank you so very much!
Great video, thank you.
Thank you soooo much! Amazing info!
wonderful! great explanation, thanks!
Thank you so much for the prefect guidance
I'm using Excel on daily basis and your lessons are really helpful, thank you miss.
What a top tip! I just learned how to select the colored cells. Great
Thank you, you explained it very well.
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.
Thank you so much fo r clear guide! I love it!
Great tutorial: Short, concise and understanding.
I knew about locked cells, but by chance I've learn about "how to find coloured cells!" Thanks!
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!
Thank you for making it super easy to follow. I fell inspired to and interactive chart now.