How To Lock a Formula in Excel

Matthew June 19, 2021

Excel is a spreadsheet application that has loads of functions that you can add to sheets. Excel 2016 also has enhanced collaboration options for sharing sheets with other recipients. Those who often share their spreadsheets might sometimes need to lock formula (or function) cells. Locking the function cells will ensure that other spreadsheet users can not delete or edit the formulas.

The full versions of Excel include lock and protect options for spreadsheets. When you select to lock specified function cells, nobody, including yourself, can edit them. You can unlock the cells for editing by entering your sheet password. So if you do not need anybody to edit a shared spreadsheet’s functions, it is usually better to lock the cells beforehand.

Unlock All the Cells in the Spreadsheet

The first thing to note is that Excel’s Locked option is selected by default. However, this has absolutely no effect until you select to protect the spreadsheet. The Locked setting is selected for all cells, so protecting the spreadsheet will lock all the cells in it whether they include functions or not. Those who only need to lock functions should first unlock the spreadsheet and then select just the formula cells.

To unlock a spreadsheet:

Select all its cells by pressing the Ctrl + A hotkey. Alternatively, you can click the Select All button at the top left of the spreadsheet next to the A1 cell. That will select all the cells in the sheet as in the snapshot below.
Next, press the Ctrl + 1 hotkey to open the Format Cells window.
Now, click on the Protection tab, deselect the Locked checkbox and press the OK button to exit the window.

Lock the Spreadsheet’s Formulas

Now you have unlocked the spreadsheet, you can select to lock only the function cells in it.

Select all the functions and formula cells in the sheet by pressing the Find & Select button on the Home tab.
Now, select Go To Special… from the dropdown menu, as shown below.
Click the Formulas radio button to select all the formula type options, and press the OK button. Alternatively, you can manually select a function cell with the cursor. To select multiple cells, press and hold the Ctrl key. Or hold the left mouse button and drag the cursor over multiple cells.
Now, press the Ctrl + 1 keyboard shortcut to open the Format Cells window again, select the Locked option on the Protection tab and then click OK to close the Format Cells window.

Nothing locks until you have applied for spreadsheet protection. To protect the sheet:

Click the Review tab.
Next, press the Protect Sheet button on that tab to open a password window.
Enter a password for the sheet in the Protect Sheet window’s text box. The Select locked cells and Select unlocked cells options are selected by default so that Excel users can only select, but not edit, the function cells. You can select further options there so that spreadsheet users can still apply formatting changes or add hyperlinks to the formula cells.
When you press the OK button on the Protect Sheet window, a Confirm Password dialog box opens. Re-enter exactly the same password in that window’s text box, and press the OK button. If the second password does not match, your original one might have included a typo. Also check that you have not pressed the Caps Lock key, which will capitalize all the text.

Now you have locked the formula cells, you will need to unlock them to edit the functions. You can unlock cells by selecting the Review tab, which includes an Unprotect Sheet option. Press the Unprotect Sheet button to open a password text box. Enter the unlock password in the text box.

Lock Spreadsheet Cells with Kutools for Excel

If you still need more lock options, check out Kutools for Excel. Kutools is an add-on for Excel that adds more than 200 extra options to the application. You can also lock cells with Kutools for Excel’s Worksheet Design utility. The Kutools add-on is available at $49, and you can try out a full trial version for a couple of months.

With Kutools installed, you can open a new Enterprise tab within Excel. Press the Worksheet Design button on the Enterprise tab to open the add-on’s locking options. Then you can select a Highlight Formulas option to highlight cells that include functions. Select the highlighted cells on the spreadsheet, and press the Selection Lock button to lock the formulas. Press the Protect Sheet button on the Design tab to enter a password.

That is how you can lock formula cells in Excel spreadsheets with the application’s built-in options and the Kutools add-on. Locking cells will ensure their functions remain intact when sharing spreadsheets. Check out this YouTube page to play an Excel screencast that provides further details for locking spreadsheet cells.