How to lock cells in Excel and protect formulas [easy guide]

May 8, 2022

If you are working on a shared workbook, you may want to protect all or some of the cells to prevent unwanted changes from other users. This is also valid if you're going to send a report and you don't want people modifying it or changing the file structure or the formulas. That is why it's important that you learn how to lock cells in Excel.

In this article, I'll show you:

  • How to lock all cells from an Excel worksheet
  • How to lock specific cells only
  • How to lock formula cells
  • How to lock cells based on color
  • How to lock cells using Kutools for Excel (a useful Excel add-in)

How to lock all cells in Excel

By default, all cells in an Excel spreadsheet are locked. However, you can still edit them unless you protect the worksheet with a password. Once you set a password, the locked cells will no longer be editable unless you provide the password.

1. Select all the cells in the active Excel worksheet. You can do this either by using the CTRL + A shortcut or by pressing on the gray arrow from the upper left corner (above row 1 and to the left of column A).

Select all cells in a workbook

2. Right-click anywhere on the sheet, and choose Format Cells (or press CTRL + 1).

Note: You can also navigate to the Home tab and click Format located on the upper right side.

3. On the Protection tab, a check in the Locked check box indicates that all cells are locked. Click OK to confirm that all cells are locked.

How to lock cells in Excel

4. Navigate to the Review tab and click Protect Sheet in the Protect group.

5. Check the list of actions that you want users to be able to perform on your worksheet.

Select the actions you want the users to be able to perform on locked cells

6. Enter a password in the Protect Sheet dialog box. Confirm the password by re-entering it in the next dialog box. If you leave the password field empty, Excel will not set a password for your file, but your worksheet will still be locked.

7. Click OK.

Seeing the Unprotect Sheet option active in the Protect group confirms that all the cells are now protected.

The Unprotect Sheet option is available

How to lock specific cells in Excel 

To lock specific cells in Excel, make sure that all cells are unlocked. To unprotect specific cells and leave the other ones protected, follow the steps below:

1. Select all the Excel cells from the active worksheet.

2. Right-click anywhere on the sheet and select Format Cells (or press CTRL + 1).

Note: You can also go to Home and click Format located on the upper right side.

3. On the Protection tab, uncheck the Locked check box and click OK.

Note: A solid box in the Locked check box means the selected cells contain both unlocked and locked cells.

4. Select the specific cells that you want to protect. For example, select range A1:A4.

5. Right-click on the selected cells and click Format Cells (or press CTRL + 1).

6. On the Protection tab, check the box next to Locked.

7. Click OK. Now, only the cells from the range A1:A4 are locked for editing when the worksheet is protected.

How to lock formula cells

One thing you certainly don't want others to mess up is cells that contain formulas. To prevent cells with formulas from being edited, follow these steps:

1. Select all cells in the active Excel worksheet by pressing CTRL + A.

2. Right-click anywhere on the sheet, and choose Format Cells (or press CTRL + 1).

Note: You can also go to the Home tab and click Format located on the upper right side.

3. On the Protection tab, uncheck the box next to Locked and click OK.

4. Go to the Home tab, and click Find & Select from the Editing group.

The Find & Select menu is located in the Editing tab

5. Click Go To Special from the drop-down list.

6. Select Formulas, and click OK. All the cells with formulas in the worksheet will be selected.

Go To Special menu

7. Press CTRL + 1, and check the Locked checkbox under the Protection tab.

8. Click OK.

9. Add a password to your Excel document by following the steps from the first section of this guide, and you should be good to go. All the cells containing formulas are protected, while the user can change the rest.

Note: A solid box in the Locked checkbox means the selected cells contain both unlocked and locked cells.

How to lock cells in Excel based on color

I often use colors to show the users which are input cells, especially in files designed to work as templates. The nice part about this approach is that you can set up your Excel file to allow input only in colored cells.

Let's look at an example where we want to protect all cells except those colored in light yellow. To do this easily, we want the user to use the tab key to move from one cell to another by locking the rest of the cells and leaving only the yellow cells unprotected.

First, we need to lock all the cells. Check the steps above on how to lock all cells in Excel. After protecting all the cells using the Protect Worksheet feature, follow the instructions below to leave the yellow cells unlocked.

1. Select the range where you want to allow input of data. You can also select the entire sheet if you prefer. In this example, cells C2, C5, and C6 are input cells that I want the user to be able to change. They are used to calculate the mortgage payment using a simple mortgage calculator, but I want the interest rate and the period to be fixed.

How to lock cells in Excel based on color

2. Press CTRL + F and click Options in the Find & Replace dialog box.

Find an Replace window

3. Click Format, and expand the drop-down list. Select Choose Format From Cell option.

4. Select one of the colored cells that you want to unlock. The selected format will be displayed in the Find and Replace window.

How to lock cells in Excel based on color or formatting

5. Click Find All in the Find & Replace dialog box, and press CTRL + A to highlight all the colored cells in the worksheet.

Select all the cells with the custom format that you want to unlock

6. Press CTRL + 1 to go back to the Format Cells option and uncheck the Locked checkbox under the Protection tab.

7. Click OK.

8. Click Protect Sheet from the Review tab, and enter a password.

9. Click OK.

One thing to remember is that this method also considers other formatting options. This means that if you have yellow cells formatted as currency and yellow cells formatted as percentages, they will not all get selected at once using the Find and Replace method. The best approach would be to decide which cells you want to unlock before applying additional formatting like borders, font styles, or number formats.

How to lock cells in Excel using Kutools for Excel

If you use Kutools for Excel, you have additional options available.

Kutools is a downloadable Excel add-in that offers more than 300 powerful advanced features and functions for Excel. It also offers a free 30-day trial of the full version, with no credit card required. Selection Lock/Unlock and Encrypt Cells features can be used in protecting cells in Excel worksheets.

Locking cells using the Selection Lock/Unlock feature

1. Select all cells in the active worksheet.

2. Click Kutools Plus in the menu bar and click Worksheet Design to activate the Design tab.

3. On the Design tab, click Unlock Cells.

4. A dialog box will appear saying that the selected cells have been unlocked. Click OK to close the dialog box.

5. Select the cells or ranges that you want to lock, and click Lock Cells on the Design tab.

6. Click OK to close the Kutools for Excel dialog box.

Locking cells using the Encryption Cells feature

If you use sensitive data in your file (like payroll information), you could use the Encryption Cells feature. To activate it:

1. Select the cells that you want to lock and encrypt.

2. Click Kutools Plus in the menu bar, and then click Encrypt Cells.

3. Select Encrypt Cells from the drop-down list to open the Encrypt Cells dialog box.

4. Enter a password into both the Password box and the Confirm box, and then select one of the mask types.

5. Click OK. The selected cells will be encrypted.

 You can decrypt it by selecting Kutools Plus > Encrypt Cells > Decrypt Cells, and then type your password into the Decrypt Cells dialog box.

What to do next?

After reading this guide, I hope that you have a much better understanding of how to lock cells in Excel and protect your spreadsheets. Using password protection helps you safely share your spreadsheets with other users without worrying about accidental changes.

And remember, it's always wise to make a full backup of your Excel file before adding a password to it since it's very common for people to forget passwords after months of not using them.

In case you've set a password and forgot it, you can read this article on how to remove the password from an Excel file. It helped hundreds of people recover lost passwords and saved them countless hours.

About me
Radu from ExcelExplained

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

Essential Functions

Popular Articles

Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2022 ExcelExplained.com
>