How to Lock Formula in MS Excel

In this article we show you how to Lock Formula in MS Excel. Excel is a spreadsheet program that lets you add a lot of different functions to sheets. Excel 2016 also has better ways to share sheets with other people and work together on them. People who share their spreadsheets often might need to lock formula cells from being changed.

By locking the function cells, other people won’t be able to delete or change the formulas. Spreadsheets can be locked and kept safe in the full versions of Excel. If you choose to lock certain function cells, no one, including you, will be able to change them. By entering your sheet password, you can make changes to the cells.

So, if you don’t want anyone to change the functions of a shared spreadsheet, it is usually best to lock the cells ahead of time. You can also go to the official MS Excel support site to know more information.

ways to Lock Formula in MS Excel

Select All the Cells and Unlock it

Even though it might be hard to understand, please keep reading. As we’ve already said, only cells that are locked and safe can really be limited. If all the cells are locked and the whole worksheet is protected, the user won’t be able to change anything.

But we only want to lock the cells that have formulas in them. To do this, we must first unlock all of the cells and then only select and lock the cells that contain formulas.

• Select all the cells in the worksheet (use the keyboard shortcut Control + A).
• Use the keyboard shortcut Control + 1 (hold the Control key and then press 1). This will open the format cells dialog box.
• In the format cells dialog box, select the Protection tab.
• Uncheck the ‘Locked’ option.
• Click ok.

Select All the Cells that Have Formulas

Now that all of the cells are unlocked, we need to make sure that the ones with formulas are locked. To do this, we must first choose all of the cells that have formulas in them. Here’s how to pick out all the cells with formulas:

• Select all the cells in the worksheet (use Control + A).
• Go to Home and within the Editing group, click on Find & Select.
• From the drop-down, select Go to Special.
• In the Go To Special dialog box, select Formulas.
• Click OK.

Lock the Cells with Formulas

After choosing the cells with formulas, we need to go back and lock them (enable the lock property that we disabled in step 1). Once we do this, protecting the worksheet would only lock the cells with formulas. The other cells would still be open. Here’s how to use formulas to lock cells:

• With the cells with formulas selected, press Control + 1 (hold the Control key and then press 1).
• In the format cells dialog box, select the Protection tab.
• Check the ‘Locked’ option.
• Click ok.

Protect the Worksheet

Now that the “Locked” property is turned on for cells with formulas (but not for other cells), protecting the whole worksheet would only restrict access to the cells with formulas. Here’s how to keep the worksheet safe:

• Go to the Review tab.
• Click on Protect Sheet.
• In the Protect Sheet dialog box, make sure the option ‘Protect worksheet and contents of the locked cells’ is checked.
• Specify the password.
• Click OK.