How to Lock Cells for Editing and Protect Formulas

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

Video Tutorial

Watch on YouTube & Subscribe to our Channel

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:

  1. Locking/unlocking the cells.
  2. 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.

Format Cells Right Click Menu

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.

Format Cells Window to Lock Cells

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.

Uncheck the Locked Box to Unlock Cells or Range

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.

Protect Sheet Right Click 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.

Protect Sheet Window

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.

Error message for Protected Sheet

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.

Protect Sheet Button on Review Tab

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.

Uncheck box in 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.

Lock Button on QAT to See Change Locked Property for Cell 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.

Lock Cells Ribbon Menu Option

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.

Add to 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.

Add Lock Cell Button to Quick Access Toolbar

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.

Formatting locked cells

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! πŸ™‚


Your email address will not be published. Required fields are marked *

  • 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.

    • Hi Wanda,
      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 Sort
      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?



  • 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.
    Many thanks.

  • 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.

  • Dear sir,

    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

  • 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.

  • 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?

  • I am reading this article about really too good information and help us infromation thanks sharing this amazing and lovely blog

  • 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?

  • Jon, Thank you for your articles as always, very helpful.

    A question, Is it possible to lock only the cells with formulas without selecting the individual cells?

  • Thank you for doing this, but I’m wondering how to lock down columns without locking down the entire sheet? For example:
    Lock Down “Revenues from 2020-2022” in Columns A – C
    But allow a user to edit “Revenues from 2023) in Column D.

  • Thank you for doing this, but I’m wondering how to lock down columns without locking down the entire sheet? For example:
    Lock Down “2020-2022 Revenues” in Columns A – C
    But allow a user to edit “Revenues from 2023” in Column D.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter