Do you want to lock the formulas in your Excel sheet to prevent other users from changing them?

Locking formulas in Microsoft Excel is an important step to ensure accuracy. Protecting a spreadsheet from modifications can help ensure the integrity of the calculations are preserved.

This can help prevent costly mistakes when it comes to important projects or reports.

This post is going to show you all the ways you can protect the formulas in your sheets.

Lock Formulas from the Review Tab

The most common method for locking formulas will be with the commands found in the Review tab of the ribbon.

This tab contains a Protect section with various options for locking and protecting parts of your workbook.

The trick most people don’t realize is that by default all cells in the sheet will be locked when you use the Protect Sheet command in the Review tab.

But if you only want certain cells locked such as those with formulas, then you will need to do these steps.

1. Unlock all the cells in the sheet.
2. Select only the cells with formulas you want to lock.
3. Lock the selected cells.
4. Use the Protect Sheet command to protect the locked cells.

These steps will allow your users to view and edit all the cells except those with formulas.

Unlock All the Cells in the Sheet

When you create a new sheet the default is that all cells are locked when you apply any protection options to the sheet.

If you only want formulas locked, then you need to update this setting.

Unfortunately, this setting is hidden in the Format Cells menu which is not a very obvious place.

Here are the steps to unlock the cells in a sheet.

1. Press Ctrl + A or click on the button at the intersection of the rows and columns headings to select the entire sheet.

1. Press Ctrl + 1 or right-click on the sheet and choose the Format Cells option to open the Format Cell menu.
2. Go to the Protection tab in the Format Cells menu.
3. Deselect the Locked option. This will set all the cells to be unlocked and won’t inherit any sheet protection options applied.
4. Press the OK button.

This cell contains a formula and is not locked to protect it from being changed inadvertently.

Microsoft Excel cell warning

You should now see the above warning in each cell with a formula.

Select Only Cells with Formulas

Now that every cell is unlocked, you can select the cells with formulas that you want to lock.

You can manually select these if there aren’t many, or if you only want to lock certain formulas. But the easiest way will be to automatically select all the cells with formulas using the Go To command.

Here’s how to select all the cells with formulas.

1. Go to the Home tab.
2. Select the Find & Select command in the Editing section.
3. Select the Go To option from the menu.

Alternatively, you can use the Ctrl + G shortcut to open the Go To menu.

💡 Tip: Alternatively, you can use the Ctrl + G shortcut to open the Go To menu.

4. Press the Special button.

5. Select the Formulas option.
6. Press the OK button.

The advantage of using the ribbon commands is you can go directly to the Go To Special menu and skip the Go To menu.

All the cells with formulas should now be selected.

Lock Selected Cells with Formulas

With all the formula cells to be locked selected, you can now lock them from the Format Cells menu.

1. Press Ctrl + 1 or right-click on the sheet and choose the Format Cells option.
2. Go to the Protection tab.
3. Check the Locked option. This will only apply the setting to the selected cells.

Protect the Locked Cells with Formulas

Your formula cells are now locked and you can apply the sheet protection. This is the essential step to protect your formulas as applying the Locked setting has no effect until you protect the sheet.

Here’s how to protect the sheet.

1. Go to the Review tab.
2. Click on the Protect Sheet command in the Protect section. This will open the Protect Sheet menu.

3. Add a Password if you don’t want users to be able to unlock the formulas. This will add an extra layer of security to your locked formulas as only those with the password will be able to unlock the formulas. Leave this blank if you don’t mind who is able to remove the protection.
4. Press the OK button.

The default permissions of allowing users to Select locked cells and Select unlocked cells will be sufficient to block your formulas from being edited.

But you might want to allow extra permissions such as the ability to Format cells since these types of edits won’t affect the formulas.

The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.

Microsoft Excel warning

After you press the OK button, your formulas are protected! If you try to edit or delete them, you will get the above warning and your change won’t be applied.

Lock Formulas from the File Tab

The Protect Sheet command can also be accessed from the File tab.

The steps beforehand will be the exact same. You first need to unlock all cells, then select the cells to lock, then lock those selected cells, then protect the sheet.

This is just another location to access the protect sheet command.

Here’s how to protect the sheet from the File tab.

1. Select the sheet which you want to protect.
2. Go to the File tab. This will open up the Excel backstage menu.

3. Go to the Info tab.
4. Click on the Protect Workbook command.
5. Select the Protect Current Sheet option in the menu.

This will open the Protect Sheet menu for the sheet.

This is a slightly more inconvenient method as there are a few more clicks involved over using the Review tab commands.

Lock Formulas from the Right Click Menu

Another way to access the Protect Sheet menu is from the right-click menu on a sheet tab.

This is the easiest way to protect the sheet since it’s always available at the bottom of your workbook.

Again the previous steps to unlock, select, and lock cells with formulas will need to be performed beforehand.

Here’s how to protect the cells with the right-click menu.

1. Right-click on the sheet tab you want to protect.
2. Select the Protect Sheet option from the menu.

That’s it! This opens the Protect Sheet menu in the least amount of clicks.

Lock Formulas in All Sheets with VBA

One drawback to locking formulas is that you must do the above steps for each sheet in the workbook.

This can be a daunting task if you have many sheets in which you want to lock formulas.

This is where a VBA solution can help speed up the job.

You can create a VBA script that will loop through each sheet and perform the task for you automatically.

Go to the Developer tab and click on the Visual Basic command or press the Alt + F11 shortcut to open the visual basic code editor.

Sub LockFormulas()

Dim ws As Worksheet
Dim varPassword As String
varPassword = "password"

For Each ws In ThisWorkbook.Worksheets

    ws.Activate
    Cells.Select
    Selection.Locked = False

    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Selection.Locked = True
    
    ActiveSheet.Protect varPassword, _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        AllowFormattingCells:=False, _
        AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, _
        AllowInsertingColumns:=False, _
        AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, _
        AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, _
        AllowSorting:=False, _
        AllowFiltering:=False, _
        AllowUsingPivotTables:=False
    
Next ws
    
End Sub

Go to the Insert menu in the visual basic editor and select the Module option then paste the above code into the new module.

This code will loop through each sheet in the workbook and unlock all the cells in the sheet. It then selects only the cells with formulas and sets those as locked.

The sheet is then protected with a password varPassword and the basic permissions to select cells.

📝 Note: If you don’t want to protect the sheets with a password, then you can replace this line ActiveSheet.Protect varPassword, _ with this line ActiveSheet.Protect _ in the code.

This code will make quick work of locking all your formulas on all the sheets!

Lock Formulas in All Sheets with Office Scripts

You can also automate the process of locking your formulas on all sheets with Office Scripts.

Go to the Automate tab and click on the New Scripts command to open the Office Scripts code editor.

function main(workbook: ExcelScript.Workbook) {

	let sheets = workbook.getWorksheets();
	let varPassword = "password"

	for (let sheet of sheets) {
		let fullRange = sheet.getRange();
		let usedRange = sheet.getUsedRange();
		let formulaCells = usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas);

		fullRange.getFormat().getProtection().setLocked(false);
		formulaCells.getFormat().getProtection().setLocked(true);

		sheet.getProtection().protect({
			allowAutoFilter: false,
			allowDeleteRows: false,
			allowDeleteColumns: false,
			allowEditObjects: false,
			allowEditScenarios: false,
			allowFormatCells: false,
			allowFormatColumns: false,
			allowFormatRows: false,
			allowInsertColumns: false,
			allowInsertHyperlinks: false,
			allowInsertRows: false,
			allowPivotTables: false,
			allowSort: false,
			selectionMode: ExcelScript.ProtectionSelectionMode.normal
		}, varPassword);
	};
}

Paste the above code in the editor and press the Save script button.

When you run the code, it will loop through each sheet in the workbook and apply the locked setting to all the cells with formulas and then protect the sheet with a password set using the varPassword variable.

📝 Note: You can replace this line }, varPassword); with this line }); in the code if you don’t want to add a password to your sheet protection.

All the formulas in all the sheets will get locked!

Unlock Formulas in a Sheet

If you have locked formulas in protected sheets, you might want to occasionally edit them.

For this, you will need to unlock these formulas by unprotecting the sheet.

This can be done with various techniques shown below

But if the sheets are protected with a password, you will need to know it in order to unlock them.

⚠️ Warning: If the sheet is protected with a password, you will be prompted to input the password when unprotecting the sheet.

Unlock Formulas from the Review Tab

Here’s how to unlock formulas from the Review tab.

1. Select the sheet with your formulas to unlock.
2. Go to the Review tab.
3. Click on the Unprotect Sheet command in the Protect section. This command only appears if the sheet is protected.

Unlock Formulas from the File Tab

Here’s how to unlock formulas from the File tab.

1. Go to the File tab.
2. Go to the Info tab. You will see all the sheets listed in the Protect Workbook section that are protected.
3. Click on the Unprotect link next to the sheet you want to unlock formulas in.

This is a great option to quickly remove the formula lock in multiple sheets.

Unlock Formulas from the Right Click Menu

Here’s how to unlock formulas from the right-click menu.

1. Right-click on the sheet tab with the formulas you want to unlock.
2. Select the Unprotect Sheet option from the menu.

Unlock Formulas in All Sheets with VBA

Sub UnlockFormulas()

Dim ws As Worksheet
Dim varPassword As String
varPassword = "password"

For Each ws In ThisWorkbook.Worksheets
    
    ws.Activate
    ActiveSheet.Unprotect varPassword
    
Next ws
    
End Sub

The above VBA code will loop through all the sheets in your workbook and unprotect the sheet to unlock the formulas.

The code will input the password from the varPassword variable if your sheets are protected with a password.

If your sheets are not password protected, you can change this line ActiveSheet.Unprotect varPassword to this ActiveSheet.Unprotect.

Unlock Formulas in All Sheets with Office Scripts

function main(workbook: ExcelScript.Workbook) {

  let sheets = workbook.getWorksheets();
  let varPassword = "password"

  for (let sheet of sheets) {
    sheet.getProtection().unprotect(varPassword);
  };
}

The above Office Script code will loop through all the sheets and unprotect the sheet to unlock the formulas.

The code will input a password from the varPassword variable if your sheets are protected with a password.

If your sheets are not password protected, you can change this line sheet.getProtection().unprotect(varPassword); to this sheet.getProtection().unprotect();.

Conclusions

Locking your formulas can be a great way to ensure users don’t accidentally change or remove important calculations in the workbook.

Unfortunately, the process for locking formulas is not simple and requires using the Format Cells menu to change the Locked settings before protecting a sheet.

The process also becomes tedious when you need to lock formulas in all sheets. This is when a VBA or Office Scripts solution will help you out.

Did you know you could lock formulas from being edited? What will you use this for? Let me know in the comments!