If you have ever created a spreadsheet with formulas, you know how frustrating it can be when someone accidentally changes or deletes them. Fortunately, Excel has a feature that allows you to lock formulas in cells so that they cannot be modified or overwritten.

We will show you how to lock formulas in Excel using the following steps:
:

Step 1: Select the Cells with Formulas

Select the cells that contain the formulas you want to lock. You can use Ctrl+A to select all cells, or use the mouse to drag and select a range of cells.

Step 2: Format Cells

Right-click on the selected cells and choose Format Cells from the menu. Alternatively, you can press Ctrl+1 to open the Format Cells dialog box.

Step 3: Lock the Cells

In the Format Cells dialog box, go to the Protection tab and check the Locked option. This will mark the selected cells as locked, but they will not be protected until you apply worksheet protection.

Step 4: Protect the Worksheet

To enforce cell locking, you need to protect the worksheet:

1. Go to the Review tab in the Excel ribbon.
2. Click on Protect Sheet.
3. Set a password (optional) and configure other protection settings, such as allowing certain actions or selecting specific cells.
4. Click OK to protect the sheet.

Step 5: Test the Locking

Now, your formulas are locked and cannot be changed or deleted by anyone who does not have the password. You can test this by trying to edit or delete a formula cell. You will see an error message that says the cell is protected and requires a password to modify.

Step 6: Use VBA to Lock Cells (Advanced)

If you want more advanced control or need to automate cell locking, you can use VBA. Here’s a simple VBA code snippet to lock specific cells with formulas:

Sub LockFormulasWithVBA()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A1:A10")
rng.Locked = True
rng.FormulaHidden = True
ws.Protect Password:="YourPassword", UserInterfaceOnly:=True
End Sub

Copy and paste this VBA code into the Visual Basic for Applications (VBA) editor in Excel (Press ALT + F11 to access VBA), and run it.

This code will lock the specified cells containing formulas programmatically.

Related posts:

Adding a Digital Signature to an Excel Document How to insert degree symbol? How to Use SWITCH Function