how to lock formulas in excel


Locking formulas in Excel is essential when you want to preserve the integrity of your spreadsheet and prevent accidental changes. By locking specific cells or ranges containing formulas, you ensure that others can only modify the designated input fields while keeping the formulas intact. This article will guide you through the process of locking formulas in Excel, providing step-by-step instructions and valuable tips.

Understanding Formula Locking

Before we delve into the details, let’s clarify what formula locking entails in Excel. When you enter a formula in a cell, Excel automatically adjusts the references based on the relative position of the cells. However, this automatic adjustment can sometimes cause unintended consequences. By locking specific cells or ranges, you can prevent Excel from modifying the references when new data is added or cells are copied.

Step-by-Step Guide to Locking Formulas in Excel

how to lock formulas in excel

Follow these steps to lock formulas in Excel:

Step 1: Open your Excel spreadsheet

Launch Microsoft Excel and open the desired workbook containing the formulas you want to lock.

Step 2: Select the cells or ranges with formulas

Identify the cells or ranges that contain the formulas you want to lock. Click and drag your mouse cursor to select them.

Step 3: Right-click and choose “Format Cells”

Right-click on the selected cells and choose “Format Cells” from the context menu. A dialog box will appear.

Step 4: Go to the “Protection” tab

In the “Format Cells” dialog box, navigate to the “Protection” tab.

Step 5: Check the “Locked” box

Within the “Protection” tab, you will find the option to check the “Locked” box. Ensure this box is checked, indicating that the cells or ranges should be locked.

See also  how to clean womb

Step 6: Apply the changes

Click on the “OK” button to apply the changes and close the “Format Cells” dialog box.

Step 7: Protect the worksheet

To enforce the locked formula protection, you need to protect the entire worksheet.

Step 8: Navigate to the “Review” tab

Select the “Review” tab located at the top of the Excel window.

Step 9: Click on “Protect Sheet”

Within the “Review” tab, click on the “Protect Sheet” option. A dialog box will appear.

Step 10: Set the sheet protection options

In the “Protect Sheet” dialog box, you can specify which actions users are allowed to perform on the protected sheet. Ensure the “Protect worksheet and contents of locked cells” option is checked.

Step 11: Choose a password (optional)

If you want to restrict access further, you can provide a password to prevent unauthorized users from unprotecting the sheet without the password. However, this step is optional.

Step 12: Click on “OK” to protect the worksheet

Click on the “OK” button to protect the worksheet and lock the formulas. You may be prompted to re-enter the password if you opted for one.

Step 13: Save your workbook

Remember to save your workbook to preserve the locked formulas. From now on, these formulas will remain protected, preventing accidental modifications.

Tips for Effective Formula Locking

Here are some additional tips to ensure effective formula locking in Excel:

1. Use separate input areas

To make it clear which cells users should modify, create a separate input area and apply formula locking on the rest of the worksheet.

See also  how to get results via sms

2. Educate and communicate

When sharing your locked formula Excel file, inform users about which cells or ranges they have permission to modify. This helps avoid confusion and accidental changes to protected formulas.

3. Test your protections

Before sharing your protected Excel workbook, test the locked formulas to ensure they still produce the desired results. Verify that the locked cells cannot be modified while the input cells remain editable.


Locking formulas in Excel is a simple yet powerful way to maintain the integrity of your spreadsheets. By following the step-by-step guide provided in this article, you can easily lock your formulas and prevent accidental modifications. Remember to save the workbook after protecting the sheet to preserve the locked formulas. By implementing effective formula locking practices, you can ensure the accuracy and reliability of your Excel files. Happy formula locking!

Similar Posts