How to Lock and Protect Individual Cells in Excel + Bonus Tips for Quick Setup

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video I explain how to lock & protect individual cells or ranges for editing. This is useful when you only want users to be able to edit specific cells and protect other cells that might contain formulas.
Download the example Excel file: www.excelcampus.com/tips/lock...
Locking cells is a great technique for files that you share with others to fill out, like quote sheets, invoices, inventory lists, budgets, forecasts, etc.
Locking individual cells for editing is a two step process:
1. Lock or unlock individual cells/ranges.
2. Protect the worksheet.
By default, all cells in Excel are Locked. So we typically need to unlock the cells that will be edited or modified by users.
In the video I also share some Bonus Tips to help make it easier for both you and your users. This includes:
- How to prevent the user from selecting locked cells, for faster data entry.
- How to add the Lock button to the Quick Access Toolbar (QAT) to view the locked state and change it quickly.
- How to use cell formatting (fill color) to denote locked and unlocked cells.
Please leave a comment below with any questions or suggestions. Thanks! 🙂
Read the full article and download the example Excel file here: www.excelcampus.com/tips/lock...
Additional Resources:
View Two Sheets Side-by-Side in the Same Excel File: • How To View Two Sheets...
How to Create Drop Down Lists in Cells - The Complete Excel Guide: • How to Create Drop Dow...
00:00 Introduction
00:25 Lock and Protect Individual Cells
03:58 Bonus Tips

[@Evolve816]

Out of the 8 videos I've watched and multiple resources on the internet, you are the only one that I've found so far that has explained this correctly. No one ever mentions the defaults within Excel, and the order of operations from most is not detailed in the best way. THANK YOU MY GOOD WISE SIR!

[@askingwhy123]

This video not only taught me exactly what I needed to know in under three minutes, it also restored some of my faith in humanity! You've got a friend in Germany!

[@alanrichardson8923]

At last! A straightforward explanation for locking and protected cells in Excel. Thanks Jon I will be back to Excel Campus again!

[@sklee1644]

I watched many times, but finally I got it work for me. Thanks for sharing. I wish I could find you two years ago.

[@michaelmontgomery8095]

Worth watching. Clear, concise, good job. Thanks from Cape Town!

[@ajlong404]

This was perfect. And your microphone sounds terrific.

[@ororos772]

Thanks for the tips

[@syammaddukuri7016]

thanks ..,got the real one after watching somany videos ....

[@muthyalamma1]

I really got what exactly i am looking for! Other channels not covered the topic the way you covered in detailed manner. Love you lot 🥰😍

[@ExcelCampus]

Thanks for your feedback, Manju! :)

[@maureendowling2183]

Very helpful thanks

[@billchow7572]

Clear and concise. Good

[@TBeau57]

I finally got them to work after watching this video.

[@ExcelCampus]

That's great, T! 😀

[@MsMusicalways]

Thank you!!!

[@mukwera9131]

Many thanks !!! I have watched a couple of videos on this subject and yours is the only one that makes sense - the secret to unlocking this mystery was your comment, "This is a 2 step process" - Once again - thankyou. I "get it" now.

[@Kunanakuyalstainde]

I knew how to protect cells prior to seeing your video, but your explanation is the best, very simple and straight forward for anyone to pick up and run with. Kudos!

[@ketsyprophete]

Thank you!! This video is so easy to follow and understand!

[@poonama4]

Thank you so much - it was really helpful.

[@weekender8835]

Spot on. Clear and concise. Look forward to other videos from you.

[@rustynail8692]

thanks for sharing knowledge for free.

[@voiceInDetroit]

great job. no wasted time. You taught me a lot in a short video.

[@narayanmhaskar]

EXCELLENT AND VERY USEFUL

[@SamBeast596]

Thanks from Purdue!

[@vangiehorvath2677]

Simple and easy to follow, thank you so much.

[@russelllarge2091]

Very useful and could follow easy.
Thanks

[@tesfayedesalegn5340]

Thank you.

[@viveksharma9560]

Excellent explained

[@supersql8406]

Exactly what I was looking for!! Thx u!!

[@haydeeportillo3426]

Very easily explained.

[@ExcelCampus]

Thanks for your feedback! 😀

[@blike22]

You're an absolute legend. Thanks champ.

[@ExcelCampus]

No problem, blike22! 👍

[@sairarcher]

Good piece of information! Very helpful.

[@speaktruth2830]

Hi Jon, This video was very much helpful and beautifully explained. cheers !!!

[@dardangjoshi6283]

Very helpful. Thank you!

[@kangtri3683]

Thank You...

[@wayneedmondson1065]

Hi Jon.. thanks for the great review on Lock and Protect. Always insightful and useful tips from your channel. Thumbs up!

[@lorieadams4828]

Thank you for your video! This was very helpful and easy to follow and implement while watching!

[@layengphan3634]

Clear instructions and certain of great help !

[@krishnamurthygp6022]

Simple n wonderfully explained.. cheers jon😊👍

[@ExcelCampus]

Glad you liked it, @krishnamurthygp6022 ! 😀

[@azaz741]

Thanks Jon ,for the lesson it was really useful , i will put it on practice...

[@ExcelCampus]

Thanks AZ! Happy to hear you will be putting it to good use. 🙂

[@PokerFather04]

always great explanations! and examples! you, sir...are my hero! :)

[@jocelynberlin957]

Thank you... it help me a lot

[@maryandbrianbaulkhamhills8747]

Echo the comments below. Great presentation and easy to follow. Thanks heaps

[@nephneph1869]

Thank you so much.. this is very informative. Will subscribe.

[@yulinliu850]

Very helpful tips! Thanks!

[@letstalkaboutoccupationalh8764]

Thanks for sharing

[@ExcelCampus]

Thanks for watching! 😀

[@bestscenes1469]

I just Love your vids. Thanks

[@avinashattri9494]

Thank you 💯💯

[@russelvarias2369]

thank you so much for this

[@richardamey9947]

Really useful; thanks for sharing.

[@rajamanikamr4779]

I like very much for your all interesting lessons

[@mikevanwyngaardt3852]

Very user friendly, thank you

[@alexandramerino9793]

You are awesome!🎉

[@ExcelCampus]

Thanks, Alex! 😀

[@drsteele4749]

Thanks, Jon. Good info. Long ago I put the QAT below the ribbon, where it displays slicker and many more icons than will fit up top.

[@aqibishfaq1313]

Very helpful video thanks for sharing your knowledge

[@crz6591]

Wow, thank you so much for this...

[@mujunikilligye5072]

Your definitely amazing. Nice content man.

[@bijuc.h4371]

thanks, simple explanation done

[@mohammadalali6546]

really amazing, simple, brief, and effective explanation, just a question, any videos for the most important uses?

[@BeauKnowsMultifamily]

bad ass, love this feature.

[@rickkeev4919]

Jon, another good video. I would remind folks to not lose their password as the warning states. Additionally, I would have also include the Go To special process to select all formula cells at 1 time to protect.

[@steelhub1306]

thank you very much

[@chinabono8995]

Very good!!!

[@icequeenvanilla]

Tnx for video ☺️

[@unitedworldcare9497]

Thank you Sir

[@jesin100]

Hello John, thanks for putting this video across, really helpful. But I do have a small question. How can one apply a filter to a table that has few columns locked in it. Is there a way to do it? Looking forward to your reponse.

[@jtweber33]

Your videos are always very thorough, and so well explained. Thank you for your work! I do have a specific scenario that wanted to see if you have a solution for: on a co-authored shared file, I would like to customize protection options for specific users. I have a shared file where I want everyone to be able to edit the specified unlocked cells, but only allow specific users to filter or sort the data. Or, if possible, I want to only allow specific users to filter & sort when they're in private view and not in the default view. If you have a solution for that scenario that would be much appreciated!

[@johnadams5586]

Thank you Jon very much :(

[@samipiyash9322]

Good tutorial

[@ExcelCampus]

Thanks Sami! 🙂

[@rajeshsinghjadham9899]

good video sir

[@bOyal1000]

Thanks

[@mikeknopfler2928]

clear as a bell

[@arqnagauri]

thanks bro

[@ExcelCampus]

You're welcome, Muhammad! 😀

[@adisuissa6067]

Hey, this video was super helpful, one question if anyone can help. after locking the cells and protecting the sheet if i want to search (ctrl F) for a company in the spreadsheet it wont let me find the company becasue the formulated cells is there a way around this?
Appreciate it!

[@pottiepotgieter1592]

Is there a way to protect a cell but allow people to use only a drop down list from that cell?

[@nitinc2373]

I have an Excelsheet,In this the Range is ("A1:G10"),I want when I Edit in cell A4 that cell should be locked for Editing & Deleting,but the user can edit in B4, after eding in B4 this cell should be locked for Editing & deleting and so on,how to do this.?

[@lisajack9217]

Great tutorial. One question though. Within the cells that people can "edit" - the cells that are "unlocked" - I want those cells to be able to be merged and centered but that option is not available on the home screen after I protect the sheet. Is there a way that you can merge and center cells within the unlocked cells? If so, how?

[@f.el-blmhc1308]

Hi, How can I only lock from editing a few cells without having to unlock all of the other cells in a spreadsheet that needs to be constantly updated with new information? With the steps in this video I will have to manually continue to unlock cells as new information is added to the spreadsheet?

[@CarlitoCulpable]

Hi, do you know how to remove handles or adjustments points on a text box in Excel? Please help

[@ojomichael6213]

Very good lessons thanks!! But if the content is copy to a new sheets are the cell still locked.?

[@madhavdamani9799]

No. If you copy it in a separate tab, one would be able to see all the locked and hidden cells as well. Not sure if there is any trick to take care of this drawback.

[@Zipo214]

Can you lock and protect individual cells dynamically? So if I have a cell that requires a number to be entered before the next set of cells, but I dont want someone to enter data into those cells until they finish entering in the first cell, but I also want the color to change as I go.

[@davidlatin9896]

The Lock Cell icon set up in Quick Access Toolbar only indicates locked cells when the sheet is Unprotected! Is this a bug? It would be nice if it indicates locked cells for Protected sheets.

[@DeputyBurbank]

Great thanks. I don't get why they didn't design it where you can just select cells and lock them without all this protect sheet stuff.

[@AMP1111911]

Can the user add lines if they need to if the page is protected?

[@nitinc2373]

I have an Excelsheet,In this the Range is ("A1:G10"),I want when I Edit in cell A4 that cell should be locked for Editing & Deleting,but the user can edit in B4,ah=fter eding in B4 this cell should be locked for Editing & deleting and so on,how to do this.?

[@cbcity8732]

👌

[@JM-dn1hj]

How do you protect the cells/ sheet from mobile iOS/Android users?
I protected my sheets/cells and in excel they could not be accessed, BUT a colleague accessed them using his mobile!

[@anenlightenedmisfit6262]

Can anybody tell me what is the difference between this example, - "$A$1" vs "$A1" vs "A$1"?
I know they are for locking cells rows and columns but they are not the same. Still messing up with my calculations.

[@harigokul4450]

How to use the pivot option on protecting worksheets? I have tried, but the pivot option not visible after protecting the sheet with a password. pls, help me out.

[@GangadharMK]

Sir.,
I Have trying to lock the cells that has formulas but I have not be able to..
Below are the Steps being followed..
1.Click Ctrl+1
2.Uncheck Lock options
3.Go to special and click On Formulas
4.Locked Option is selected
5.Protect Sheet
But I am Unable to

[@nbforme]

Is there a way to select all locked or all unlocked cells to quickly format them?

[@jesuspaalisbojr7320]

How to lock the formula in the excel.