5 Ways to Lock and Unlock Formulas in Microsoft Excel

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.

  1. Press the Special button.
  1. Select the Formulas option.
  2. 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.
  1. 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.
  2. 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.
  1. Go to the Info tab.
  2. Click on the Protect Workbook command.
  3. 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!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Related Posts

Comments

8 Comments

  1. Nikita

    Hi

    Need your help!
    I have two columns –
    A. B
    US. 200
    UK. 300
    GER. 60
    US. 86
    IND. 0
    CH. 50
    CH. 20

    Now I want this result –
    C. D
    US. 286
    UK. 300
    GER. 60
    CH. 70

    So basically adding the values against same name (I have used Index Match and it works) but if amount is zero, I dont want that name in final list.

    Thank you.

    • John MacDougall

      Use a pivot table to summarize, then filter out zero values.

  2. Thomas Rogers

    Hi John. Just a quick note to lt you know how I go about preventing people from messing up my formulas.
    1. Select the cells I want to give them access to.
    2. Go to the Format cells dialog box and select the Protection Tab
    3. Unlock the cells by removing the tick mark next to Protect Cells, then clicking on OK.
    4. Go to the review tab and protect the sheet.
    Users can now click on any cell in the sheet but can only change the cells I gave them access to.
    Thanks for ALL your tips, tricks, and other helpful information. Whenever I learn something new, I share it with my students. I will surely make use of your methods to help them even more.

    • John MacDougall

      Thanks for the tips!

  3. Wayne Tuffery

    John.
    This is my first attempt at the Script language. I have used your Lock all cells with Formulas but I get an error. Line 12: Cannot read properties of undefined (reading ‘getFormat’) if any of the sheets don’t have a formula in them. Is there a way of stepping over this error, say testing if formulaCells is empty.

    Works well if all sheets have formulas so thank you for the script.

    • John MacDougall

      Ah, I didn’t try this scenario.

      You can see if a try catch statement will work for this.

  4. Wayne Tuffery

    John.
    I have spotted another issue where I get an error Line 11: FormatProtection setLocked: You cannot perform the requested operation. if a sheet is already locked. I added a test to see if a sheet is already locked before changing the sheet cells and step over the sheet if already loacked.
    Senario. Opened the sheet to modify one or more sheets. Unlock the required sheets and make the changes. I now want to ensure they are all locked using your script before saving.
    Inserted at line 7: if (sheet.getProtection().getProtected() == false) {
    Inserted at line 31: };
    This allows it to ignore sheets that are already locked.
    Thanks again

    • John MacDougall

      Thanks

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃