When you create an Excel spreadsheet, sometimes you need to use a calculated formula, and you don’t want others to change, edit, or delete the formulas. The simplest and most common way of preventing others from changing your formulas is to lock and protect cells that contain formulas in your spreadsheet.
The following article will guide you to lock and protect the formulas as follows:
Lock and protect Excel formulas with Format Cells
By default, all cells in Excel
are locked, so you have to unlock all cells.
1. Select the entire workbook with Ctrl + A
, right-click, and choose Format Cells
from the context menu.
2. And a Format Cells dialog box will pop out. Click protect, and uncheck the option lock. Click OK. The whole workbook has been unlocked.
3. Then click Home> Find & Select> Go To Special, and the Special dialog box will appear. Check the formula and Choose an option, then click OK. See screenshot:
4. All cells containing formulas will be selected.
5. Then leave the checkboxes locked. Right-click the selected cell and choose Format Cells from the context menu, and a Format Cells dialog box will display. Click Protect, check the locked check box.
6. And then click Review > Protect Sheet, and check the box Protect worksheet and contents of locked cells check box, you can enter the password in the Password to Unprotect sheet box.
7. Then click, OK. And a Confirm Password dialog box will appear. Re-enter your password. And click, OK.
And then, all cells containing formulas are locked and protected. In this case, you cannot modify the formulas, but you can edit other cells.