How to Lock Formulas in Excel: Step-by-Step Guide

Updated 6 November 2026 · Standard formula, shown below

This guide shows you how to lock formulas in Excel so they can’t be accidentally changed or deleted. You’ll learn how to protect the cells containing formulas while leaving other cells editable, which is essential for sharing spreadsheets or preventing errors. By the end, you’ll be able to lock specific formulas, unlock input cells, and password-protect your sheet.

What You’ll Need

A computer with Microsoft Excel installed (2010 or later, including Microsoft 365).
An Excel workbook with formulas you want to lock.
Basic familiarity with selecting cells and using the ribbon.

Step-by-Step

Open your workbook and go to the worksheet that contains the formulas you want to lock.
Select all cells by clicking the triangle at the intersection of the row numbers and column letters (top-left corner) or press Ctrl+A.
Open the Format Cells dialog: Right-click any selected cell and choose Format Cells, or press Ctrl+1.
Go to the Protection tab and uncheck the Locked checkbox. Click OK. This unlocks all cells by default, because Excel locks every cell by default but the protection is not active until you protect the sheet.
Select only the cells that contain formulas. You can do this manually, or use the Go To Special feature: press F5 (or Ctrl+G), click Special, choose Formulas, and click OK. This selects all formula cells.
Reopen Format Cells (right-click > Format Cells or Ctrl+1) and go to the Protection tab again.
Check the Locked checkbox and click OK. Now only the formula cells are marked as locked.
Protect the worksheet: Go to the Review tab on the ribbon and click Protect Sheet.
Set a password (optional but recommended) in the dialog box. You can also choose what users can do; for locking formulas, you typically want to leave the first two checkboxes (Select locked cells and Select unlocked cells) checked so users can still click around, but uncheck any actions you don’t want them to perform.
Click OK and re-enter the password if prompted. Your formulas are now locked.

To test, try clicking on a formula cell. You should see that it is not editable. Non-formula cells remain editable.

Common Mistakes

Forgetting to unlock all cells first: If you skip step 3-4, all cells remain locked, and after protecting the sheet, users can’t edit anything.
Protecting the sheet but not locking the formula cells: If you don’t check the Locked checkbox on formula cells, they remain editable even after protection.
Using a password and then forgetting it: Excel does not offer a password recovery for sheet protection. Write it down or store it securely.
Protecting the workbook instead of the sheet: The Protect Workbook button locks the structure (adding, deleting, renaming sheets), not the cell contents. You need to use Protect Sheet.
Locking the entire sheet accidentally: If you protect the sheet without unlocking any cells, everything becomes read-only. That might be what you want, but not if you only wanted to lock formulas.

Tips

Use the Go To Special trick: It’s the fastest way to select all formula cells, especially in large sheets.
Leave the “Select locked cells� checkbox enabled: This lets users click on formula cells to see the formula in the formula bar (unless you also hide formulas). If you want to hide the formula itself, check the Hidden checkbox in Format Cells > Protection, but that’s a separate feature.
Combine with data validation: For input cells, you can add data validation to restrict what users type, but that’s optional.
Unprotect easily: To edit later, go to Review > Unprotect Sheet and enter the password if set.
Use cell styles: You can create a custom cell style that marks input cells (e.g., yellow background) and another for formula cells (e.g., grey), making it visually clear.

Troubleshooting

Problem: I followed the steps, but I can still edit the formula cells.
Cause: The sheet might not be protected, or you didn’t re-lock the formula cells. Go to Review and check if Unprotect Sheet is visible. If it is, the sheet is not protected. Also, verify that the formula cells have the Locked checkbox checked (after unprotecting, select a formula cell, open Format Cells, Protection tab).

Problem: I can’t select any cells after protecting the sheet.
Cause: In the Protect Sheet dialog, you unchecked “Select locked cells� and “Select unlocked cells�. Re-protect with those options checked, or unprotect and redo.

Problem: The password I entered doesn’t work.
Cause: Passwords are case-sensitive. Check Caps Lock and try again. If you truly forgot it, there is no built-in recovery. You can try to copy the data to a new sheet, but the protection remains.

Problem: Formulas are locked, but I want users to be able to sort or filter.
Solution: In the Protect Sheet dialog, check the “Sort� and “Use AutoFilter� options before protecting. Note that sorting may rearrange formulas, so be cautious.

Problem: I need to lock formulas in multiple sheets.
Solution: Repeat the process for each sheet. There is no one-click to protect all sheets at once, but you can group sheets (hold Ctrl and click sheet tabs) before protecting, but that can be tricky. Better to do it individually.

Frequently Asked Questions

Can I lock formulas without protecting the whole sheet?

No. Excel’s protection is at the sheet level. You must protect the sheet to enforce the locked status of cells. However, you can unlock all other cells so users can edit them.

Does locking formulas prevent users from seeing them?

No, it only prevents editing. To hide the formula from the formula bar, you also need to check the Hidden checkbox in Format Cells > Protection, and then protect the sheet.

Can I lock formulas in Excel Online or Google Sheets?

Excel Online supports the same protection features, but the steps may vary slightly. In Google Sheets, you use “Protect sheets and ranges� from the Data menu, which is different.

What happens if I copy a locked cell to another sheet?

The copied cell will have its formatting and protection settings copied, but the protection only applies when the destination sheet is protected. If the destination sheet is unprotected, the copied formula is editable.

Is there a way to lock formulas without a password?

Yes, you can protect the sheet without entering a password. Anyone can unprotect it, but it prevents accidental edits. For stronger protection, use a password.

Can I lock only certain formulas, not all?

Yes. Instead of selecting all formula cells, manually select only the cells you want to lock (hold Ctrl and click each cell), then follow the same steps. Unlock all cells first, then lock only the selected ones.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.