How to Protect and Lock Formulas in Microsoft Excel

By default, when you protect a worksheet, Excel locks all cells. However, to ensure that only formula-containing cells remain locked while other cells remain editable, you must first unlock all cells and then specifically lock only the formula cells.

Steps to Lock Formula Cells in Excel

Step 1: Unlock All Cells in the Worksheet

1. Open your spreadsheet in Microsoft Excel.
2. Press Ctrl + A (Windows) or Command + A (Mac) to select all cells.
3. Right-click any selected cell and choose Format Cells.
4. In the Format Cells window, go to the Protection tab.
5. Uncheck Locked, then click OK.
6. Now, all cells in your worksheet are unlocked.

Step 2: Select and Lock Only Formula Cells

1. Click the Home tab in the Excel ribbon.
2. In the Editing section, select Find & Select > Go To Special.
3. In the Go To Special window, choose Formulas and click OK.
4. Excel will now highlight all cells containing formulas.
5. Right-click any highlighted cell and choose Format Cells.
6. In the Format Cells window, navigate to the Protection tab.
7. Check Locked, then click OK.

Step 3: Protect the Worksheet

1. Click the Review tab in the Excel ribbon.
2. Select Protect Sheet.
3. In the Protect Sheet window, optionally enter a password to prevent unauthorized changes.
4. Click OK.
5. If you set a password, re-enter it in the Confirm Password window and click OK again.

Your formula cells are now locked. If anyone attempts to modify them, Excel will display an error message.

How to Unlock Formula Cells Later

If you need to allow modifications to formula cells in the future:

1. Click the Review tab.
2. Select Unprotect Sheet.
3. If a password was set, enter it and click OK.

By following these steps, you can ensure that your formulas remain intact while allowing users to edit other data in your worksheet.

Looking to lock chart positions in Excel? Check out our guide on securing chart placements in your spreadsheet!

Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!