How to Protect Cells that Contain Formulas in Excel
ฝัง
- เผยแพร่เมื่อ 6 ต.ค. 2024
- In my experience, many Excel users get frustrated and confused when they try to protect the worksheet cells that contain formulas. In this tutorial, I walk you through the three steps in the process.
We want to allow a user to enter and edit numeric values in some cells, while preventing any changes to the cells that contain formulas.
The key concept to understand: By default, all cells in an Excel Worksheet are "Locked." So, we need to select the input cells first and then "Unlock" them before protecting the worksheet.
I invite you to visit my online shopping website - shop.thecompany... - to preview all of the resources that I offer.
Danny Rocks
The Company Rocks
I congratulate you on your ability to give very clear instructions to solve complicated prblems. you're a legendary tutor. big thanks to you from Iraq.
Thank you. I am pleased that I could help you with this.
I am the first to say that, to me, the whole process of protecting cells in an Excel Worksheet is Counter-intuitive. It, literally, took me several years to undestand the Logic that Excel applies to this process.
Once you understand - and accept - that the default setting for every cell in every Excel worksheet is "locked," then you can easily apply the protection that you want.
Danny Rocks
The Company Rocks
Very clear, exceptional instruction on what can be difficult topic to understand from other sources. Bravo!
I have been searching every website I could find for instructions on how to protect a formula. Your video was so easy to follow. GREAT JOB!! Thanks.
Easily one of the best tutorials in terms of logic, delivery, brevity, and visuals. A+.
Thanx a ton!!¨ You made an "old geezer's" life sooo much easier today. After searching countless tutorials you solved my problem pronto!!!
great video. this is one of what i wanted to learn in excel for years until this day i saw this video and saw how easy it is in regards to sir danny.
Thank you Danny. Your tutoring is so clear. I was losing my sanity trying to do this. Knew it was counter-intuitive but I just couldn't work it out myself. cheers
This has been so helpful now that Co workers can't change our excel dairy formulas...cheers!!
Excellent tutorial. Other videos were confusing and like the fact that you explain in detail.
Thank you Roy!
I am pleased that you enjoyed my Excel Tutorial.
Danny Rocks
The Company Rocks
Thank you Danny; I enjoyed the tutorial and has certainly added value to my assignments.
Danny Rocks! Wellington City, New Zealand here! My tutor explained protecting cells and work sheets, however, I didn't even understand that the input cells required unlocking in order to protect cells with formulas. Yet I watch this short video and I fully and completely understand. It could be that I'm just slow, however, it could be due to you being a great teacher and my tutor not having great teaching ability (given quite a few other students I've spoken to don't grasp the entire lesson) eventhough my tutor has been teaching for nearly 17 years…..Dam! wish I had a tutor like you, who explains things so simply. Now I know how to protect and un protect my Macro Enabled Template. As we say in New Zealand, "Meeeeeean brooooooo!"
Just as you said, it is very counter intuitive. Should have searched this on youtube an hour ago ffs. The office help sucked so much I got even more confused. Thanks for this!
This was SUPER helpful and so easy to follow. You are a total lifesaver!
Thank you! By far the best tutorial on this issue I have found.
Thank you! I am pleased that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
Thank you Christian. I am pleased that you are learning how to use Excel from my video tutorials.
Danny Rocks
The Company Rocks
Thank you!
I am pleased that I could answer your questions with my Excel Tutorial!
Danny Rocks
The Company Rocks
Very smooth tutorial. Very clean. Thanks.
i love man you the only one who answers my questions
Excell-ent! This made confusing language clear and easy to follow. Thanks!
Thank you Danny for a precious class and, a pleasant presentation!
Hi Tara -
Thank you so much for your very kind comments about my video tutorial! I greatly appreciate your feedback.
My philosophy is, "I work had to make it easy for my viewers and clients to lean how to get the most out of MS Excel."
Once again, thank you for adding your feedback.
Danny Rocks
The Company Rocks
Thank you very much for tutorial videos, it helps a lot on my work.
Long live Danny!
Is easy to understand the procedure of it coming from Danny Rocks thanks.
Thank you so much for your help, I think ever other word you said I learned something. I will only google you for all of my excel needs!!!!!!!
Thank you - have been struggling with that all day:) (greetings from London)
thanks to all this great vids. i learned a lot from your uploads. God bless.
Thank you! A very easy explanation and a quick review at the end.
Excelent explanation !!! Very helpful, it solved my problem. Thank you Danny.
thank you, you have the best tutorial videos out!
Hello Jon - Thank you so much for your "ringing testimonial!"
My motto is: " I work hard to make it easy for my viewers to learn MS Excel!"
I am very pleased that you enjoy my Excel Video Tutorials!
Danny Rocks
The Company Rocks
Thank you for your kind words. I am pleased that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
I love you. Thank you so much for this tutorial. Very useful for my job. Have a nice day sir.
Thanks very much for this tutorial, your instructions were so neat and easy to follow. Please, keep uploading helpful tutorials. very much appreciated them.
@acarolinoa
My pleasure. I am happy that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
Thank you Mr.Danny, that was very helpful
Thank you for your kind words.
I, too, struggled for several years while learning how to "protect" cells that contain formulas. I am pleased that my Excel tutorial helped you to understand how to use this very valuable too.
Danny Rocks
The Company Rocks
Brilliant tutorial helped a lot!!! I know a bit more about Excel.
Thank you so much for this one. i was searching for this one long time ago.
Nice job making it clear. Thank you!
Thank you! That was very helpful. Exactly what I needed!
Thanks for this! This solved my problem today and my client will be happy :-)
Excellent Video. Helped me a lot. Thanks
awesome my friend. All the other so called "help guides" did'nt work. Thankyou
THANKS YOU MR. DANNY ROCKS.
what a way to explain!! Great work
Thank you very much. Excellent tutorial!!!
Thank you! Very clear and helpful!
you're a legend, thanks mate.
Here are the steps to follow:
1) Select your "Formula" cells and open the Format Cells Dialog Box.
2) On the Protection Tab, place a check next to the "Hidden" choice. (Also keep the check in place next to "Locked." By, the way, you do exactly the opposite when formatting your "input" cells! - NOT Locked and NOT Hidden.
3) On the Review Tab of the Ribbon, choose "Protect Sheet." Uncheck the label for "Select Locked Cells."
Danny Rocks
The Company Rocks
very well explained tutorial, thank you.
Brilliantly explained! thank you
Very useful. Thank you so much for the video.
Thanks Danny.. It helped a lot.. Clear explanation..!!!!!!!!!!
Thanks a lot it was very helpful for me. You are doing a good job :D
Thank you. You saved my life
Very Good Video!
Thanks Danny!
If I understand your question correctly, then I suggest that you:
1) Select all of the cells that contain the data that you want to "AutoSort."
2) Open the "Format Cells" Dialog box - Keyboard Shortcut is: Ctrl + 1 - and on the "Protection" Tab, uncheck "Lock Cells."
3) For Formula Cells, you do just the opposite - you leave the check in place for "Lock Cells" - the Default Setting!
4) Proceed to the Review Tab on the Ribbon and Protect the Sheet.
Danny Rocks
The Company Rocks
VERY HELPFUL. THANK YOU SO MUCH
Just what i needed, thank you!
Thank you for your kind words.
I apologize for the delay in responding.
I am pleased that my Excel Tutorial helped you to learn more about how to use the program.
Danny Rocks
The Company Rocks
thanks mate, you are helpful, Microsoft are not. They have made this so difficult that they really should just advise customers that they do not offer the ability to protect cells, and those who require this capability should go to Google docs.
Thats what I am gonna do right now.
But thanks for the vid, much appreciated.
Thanks Danny!!
Indeed it was a great help .Keep up the good Work..
thanks Danny fr your valuable tutorials, I',m impressed, keep up this good work
Hello Ram -
Thank you for your kind comments. I am pleased that you found my Excel Tutorial on Protecting Worksheet Cells valuable.
Danny Rocks
The Company Rocks
it was really helpful for me
Hello Michal -
Thanks for adding your comment. I am pleased that I could help you to "stop struggling" as a result of my Excel Tutorial.
Greeting back to you from California!
Danny Rocks
The Company Rocks
Hello Qassin -
Thank you for your very kind words. I strive to be the best teacher that I can possibly be.
I appreciate you taking the time to add your comments.
Danny Rocks
The Company Rocks
it works.... Thanks Danny.. your really Rock!
Wonderful, Thanks Danny.
You have helped me so much with excel, you are great.
What program are you using to do your videos they are amazing!!!!!
Great video. Great explanation.
Thanks very much for this tutorial
2022, THANK YOU FOR THIS!!!
Hi Kristina -
Thank you! I will admit that it took me quite a while to completely understand this concept of protecting cells. It almost seems counter-intuitive.
I wanted to present this video so that anyone - at any level of experience with Excel could learn how to protect (only) the cells that contain formulas while being free to change the input values that feed into the formulas.
Thanks for adding your comment!
Danny Rocks
The Company Rocks
Very good for me and all learners
Thanks! Really helped me out.
Very well explained....
Good Stuff, Thanks Danny !
Mr . Denny like this programme *****
Thanks very much for the video.
Really this method is very useful
Finally! Wow, that was painful. I'd almost want to pass a kidney stone than try and figure that out on my own like I have been all afternoon!
Thank you!
I jumped the gun. This does not work either. I have blank spaces where data is not entered and I also have cells with formulas in them where data is not present. When I follow these steps, it only locks cells with formulas that have values in them and proves to be a hindrance.
Why can't we just highlight the cells we want to protect and protect them like every other time we want to format a range of cells.
I find it very hard to believe that the IT Engineers at Microsoft are this idiotic!
Sometimes when I use Find & Select, it selects only the cells with formulas, other times it selects on cells with numerical values in them without formulas. WTH?
Jerry R Hawkins Do NOT USE Find & Select. Rather, review my video where I demonstrate ho to Use the GO TO SPECIAL Command to "unprotect" the Numeric Constant Cells - there are the cells that I want my end-users to input their values. Meanwhile, I am PROTECTING the cells that contain Formulas and Text Labels.
As I repeat in my video, Protecting Worksheets is "counter-intuitive. Honestly, it took 4 - 5 years to "grasp this concept!
Thanks for commenting on my Excel Video Tutorial!
Danny Rocks
The Company Rocks
Thank you so much loved it ! You are Amazing!
Thank you for adding your comment.
I am pleased that I could help you as a result of my Excel Tutorial.
Danny Rocks
The Company Rocks
Thank you for adding your comment.
Danny Rocks
The Company Rocks
Great job, Thank you
thanks a lot u have solved my problem god bless you
This is a great Tutorial. Thanks
So once you have protected these cells and need to unprotect them to make adjustments, do you have to do the same process every time or can you just go to the review area and select protection again?
Are Excel 2007 and 2010 pretty much the same/compatible?
Thanks for adding your comment.
Yes, the concept of how to "protect" the cells in an Excel Worksheet, by first "un-locking" certain cells really does seemd "backward." Once you understand the logic that Excel is using, everything becomes easier.
Danny Rocks
The Company Rocks
Danny...great tutorial.....I locked the formulas as you taught me.....what do i do if i want to lock the spreadsheet so no additional colums or rows can be created, but still allow workers access to input the numerical data....In other words, I have created a five page spreadsheet that prints on 8.5 X 11 and I don't want anyone to srew up the look of the spreadsheet.
Thank you
great job, well done
great work!!
Good. Thank you very much.
nice video which explained to protect that sheet's data
but that sheet can be deleted by others how to protect an excel sheet which should be visible to all
super..... thanks all for this knowledge.... nice.
Very helpful, Danny. But why can't Microsoft make the procedure much simpler? It seems a very convoluted way to do a conceptually simple job.
Thank you very much
Danny
Good explanation...But when it asks what you will allow the user to do, I have checked the "insert rows" and "delete Rows" then I protect the worksheet, however it will not allow the user to do so. What am I missing?
Nice!
P.s. what kind off software / screenrecorder, is used to make this tutorial?