Bottom Line: Learn how to lock individual cells or ranges in Excel so that users cannot change the formulas or contents of protected cells. Plus a few bonus tips to save time with the setup.
Skill Level: Beginner
Download the Excel File
You can download the file that I use in the video tutorial by clicking below.
Protecting Your Work from Unwanted Changes
If you share your spreadsheets with other users, you've probably found that there are specific cells you don't want them to modify. This is especially true for cells that contain formulas and special formatting.
The great news is that you can lock or unlock any cell, or a whole range of cells, to keep your work protected. It's easy to do, and it involves two basic steps:
- Locking/unlocking the cells.
- Protecting the worksheet.
Here's how to prevent users from changing some cells.
Step 1: Lock and Unlock Specific Cells or Ranges
Right-click on the cell or range you want to change, and choose Format Cells from the menu that appears.
This will bring up the Format Cells window (keyboard shortcut for this window is Ctrl + 1.). Choose the tab that says Protection.
Next, make sure that the Locked option is checked.
Locked is the default setting for all cells in a new worksheet/workbook.
Once we protect the worksheet (in the next step) those locked cells will not be able to be altered by users.
If you want users to be able to edit a particular cell or range, uncheck the Locked box so they are unlocked. Since cells are locked by default, most of the job will be going through the sheet and unlocking cells that can be edited by users.
I share some shortcuts to make this process faster in the Bonus section below.
Step 2: Protect the Worksheet
Now that you've locked/unlocked the cells that you want users to be able to edit, you want to protect the sheet. Once you protect the sheet, users cannot change the locked cells. However, they can still modify the unlocked cells.
To protect the sheet, simply right-click on the tab at the bottom of the sheet, and choose Protect Sheet… from the menu.
This will bring up the Protect Sheet window. If you want your sheet to be password protected, you have the option of entering a password here. Adding a password is optional. Click OK.
If you've chosen to enter a password, then you will be prompted to verify your entry after you've clicked OK.
With the sheet protected, users will be unable to change the cells that are locked. If they try to make changes, they will get an error/warning message that looks like this.
You can unprotect the sheet in the same way that you protected it, by right-clicking on the sheet tab. An alternative way to protect and unprotect sheets is by using the Protect Sheet button in the Review tab of the Ribbon.
The button text displays the opposite of the current state. It says Protect Sheet when the sheet is unprotected, and Unprotect Sheet when it is protected.
It's important to note that all cells can be edited when the sheet is unprotected. After making changes you must protect the sheet again and Save the workbook before sending or sharing with other users.
3 Bonus Tips for Locking Cells and Protecting Sheets
As you can see, it is fairly simple to protect your formulas and formatting from being changed! But I'd like to leave you with three tips to help make it faster & easier for both you and your users.
1. Prevent Locked Cells From Being Selected
This tip will help make it faster and easier for your users to input data in the sheet.
Turning off the Select locked cells option prevents the locked cells from being selected with either the mouse or keyboard (arrow or tab keys). This means users will only be able to select the unlocked cells that they need to edit. They can quickly hit the Tab, Enter, or arrow keys to move to the next editable cell.
To make this change, you just uncheck the option that says “Select locked cells” on the Protect Sheet window.
After pressing OK, you will only be able to select the unlocked cells.
2. Add a button for locking cells to the Quick Access Toolbar
This allows you to quickly see the locked setting for a cell or range.
From the Home tab on the Ribbon, you can open the drop-down menu under the Format button and see the option to Lock Cell.
If you right-click on the Lock Cell option, another menu appears giving you the option to add the button to the Quick Access Toolbar.
When you select this option, the button will be added to the Quick Access Toolbar at the top of the workbook. This button will remain each time you use Excel. You can easily lock and unlock specific cells on your sheet by clicking on this button.
You can also see if the active cell locked or unlocked. The button will have a dark background if the selection is locked.
It's important to note that this only shows the locked state of the active cell. If you have multiple cells selected, the active cell is the cell you selected first and appears with no fill shading.
Mixed Lock State
If you select a range that contains both locked and unlocked cells, you will see a solid box for the Locked checkbox in the Format Cells window. This denotes the mixed state.
You can click the checkbox to lock or unlock ALL cells in the selected range.
3. Use different formatting for locked cells
By changing the formatting of cells that are locked, you give your users a visual clue that those cells are off limits. In this example the locked cells have a gray fill color. The unlocked (editable) cells are white. You can also provide a guide on the sheet or instructions tab.
You might be wondering where I found this template for a quote. I got it from the template library. You can access the library by going to the File tab, choosing New, and using the search word “quote.”
You can find all sorts of useful templates there, including invoices, calendars, to-do lists, budgets, and more.
By locking your cells and protecting your sheet, you can keep your formulas safe from tampering by other users, and prevent mistakes.
I hope this simple tutorial proves helpful to you. Please leave a comment below if you have any tips or questions about locking cells, protecting sheets with passwords, or preventing users from changing cells.
Thank you! 🙂
Loving the Elevate course. Very helpful. Have a question that I suspect I can’t get around, but doesn’t hurt trying. 🙂 I have headers for our columns in a very lengthy spreadsheet. I have locked cells, which works fabulous. I can’t lock the headers, because the participants need to be able to Sort and Filter columns when required. I want to lock the headers, because it affects an automated Power Query report we do. Headers (name of column) can’t be changed, but they do need to sort/filer columns. Is there any way I can do this? Thank you in advance for any help you can provide.
Thanks for the nice feedback. I’m happy to hear you’re enjoying Elevate. 🙂
Great question! Yes, this is possible. It’s a few steps to setup.
You’ll need to use the “Allow Edit Ranges” feature on the Review tab.
Add the entire table as a new range.
Then protect the sheet with the following settings:
Uncheck Select Locked Cells
Check Use AutoFilter
Here is a good article I just found that explains more about it.
I hope that helps. Thanks again and have a nice weekend! 🙂
Great article. Two questions:
What software do you use for the ripped effect in your screenshots?
How does cell protection work with tables, i currently have a table with several columns that have formulas. Whenever i lock the columns, unlock the rest of the sheet and allow inserting rows, the formulas do not populate down in the new row?
I use Snag-it by Techsmith for the screenshots, and it has that ripped effect in the Editor. It’s called Cut Out and they have quite a few different effects/patterns to choose from.
Great question on Tables. This is still a limitation of Excel and the only way around it that I know of is to use a macro to add the rows and copy the formulas down. Here is a suggestion by my good friend Zack Barresse on Excel Uservoice about the issue.
You can add a vote there to help get this fixed.
I hope that helps. Thanks again and have a nice weekend! 🙂
Thank for the response Jon, will have a look at Snag-IT and of course the url provided!
Keep up the good work!
You have described the steps nicely and effectively.
Every week I learn new new steps about MS excel operation through your website and email notifications.
I just wanted to take a minute and say thank you for all you share.
All your videos are very easy to understand and follow. I always come back for more.
Hey Jon – I like your tip of adding the Locked Cells setting to the QAT, that’s a good little time saver.
I’ve written a macro to automatically select all the unlocked cells, it is a useful final check to ensure the right cells are locked or unlocked.
I am VENKANNA.thank you very much sir for lock cell.
But I want to remaining cells working but PROPERTIES not working sir…I want enable remaining cells properties.please sir help me
Thanking you sir
These tips are very important. I prefer to Protect the Worksheet from all tips. Carry on.
Really helpful this tutorial.,,,
If you want to share your excel sheet for the many people, you can use the lock formula in excel. Because, if you share your excel file with the lock formula, other user does not changing anything in your excel file.
I had prepared a workbook with password, but after two years I forgot the password.
How to unlock the workbook. At present I cannot insert a new worksheet or rename the worksheet.
Its excel 2007
Thanks for this tutorial it really helped me.
Thanks. I looked at a couple of tutorials and didn’t manage to follow. This was very clear and I got the job done in a minute!
This is among the VERY BEST tutorials I’ve ever seen! Explanation, graphics, and progression are exceptionally clear. THANK YOU!
Hi your blog is nice and well explain can you answer on this, I am trying to add an IF & AND formula and showing the value in % since my data is coming from other sheet (Test) for some value instead of % show round numbers it is show value i.e .3333
How to use round function together with this formula?
Here is my formula.
=IF(AND(Test!B5=””),”0%”,(SUMPRODUCT(–(Test!$A$4:$A$153=”Sam”),Test!B$4:B$153))*100&”%”&IF(Test!B152=””,””,” & “&(1+ABS(((RIGHT(Test!B152,LEN(Test!B152)-SEARCH(“-“,Test!B152)))-LEFT(Test!B152,SEARCH(“-“,Test!B152)-1))))&”AL”))
hi i have tried locking the cells that u dont want anyone to edit & unlocked the cells that people are okay to edit, however when i protect the sheet it wont let me hide or unhide columns? how do i lock the formula cells but still hide & unhide all columns please?
Is there a way to keep a workbook protected each time it is opened? Without having to protect it each time before saving and closing it?
thnks, very useful this video…
I like that you quickly go over the concept within excel as well as the steps. “change a lock
This has greatly helped me to know how to keep my hard work safe and complete my project in time.
Very good video, I have been trying to figure this for a good while!
When I use tables and lock one cell, all cells are locked. How do i solve this
Thanks indeed. that was so helpful. Subbed to the YouTube Channel.
Thanks !! Really helpful
BUT WITH THE MERGED HEADER CELLS WE CANT DO LIKE THIS
thank you, very help me
wow, thanks to you. This article is helpful, God bless you.
I am reading this article about really too good information and help us infromation thanks sharing this amazing and lovely blog
Thank you – very helpful, without sounding too technical 🙂
I have seven columns in my excel table with formulas. I would like to have those cells/ columns tamper-proof while only showing the answer to my formula while keeping the ability to sort.
I made sure, only the formula cells are locked. I have the Sort and Use AutoFilter checked on the Protect Sheet, under the Allow all users of this worksheet to: (I also tried checking everything) that did not work. I have tried Allow users to edit ranges. Nothing is allowing me to sort after the Protect Sheet is on and nothing locks my formula cells without the Protect Sheet selected.
Can you help?