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

As someone who loves data, Excel is my happy place. I've spent countless hours creating spreadsheets that track everything from my personal finances to work projects. I'm always looking for ways to make my workflow more efficient, and one technique that I've found incredibly useful is locking formulas in Excel.

Now, if you're not familiar with Excel formulas, here's a quick introduction. Formulas are equations that perform calculations on the data in your spreadsheet. They're what make Excel such a powerful tool for data analysis. By locking a formula, you ensure that the formula always stays the same, no matter what data you input or how you manipulate the spreadsheet. This is a game-changer if you're working with complex calculations or sharing your spreadsheet with others.

If you're ready to take your Excel skills to the next level, here's how to lock formulas in Excel.

Step 1: Select the Cells With Formulas

The first step is to select the cells with the formulas that you want to lock. You can do this by clicking and dragging over the cells, or by clicking on the first cell and then holding down the Shift key while clicking on the last cell in the range. You'll see that the cells are now highlighted.

Step 2: Open the Format Cells Window

Next, you'll need to open the Format Cells window. You can do this by right-clicking on the selected cells and selecting "Format Cells" from the drop-down menu. Alternatively, you can go to the Home tab on the ribbon and click on the "Format" button. Then, select "Format Cells" from the drop-down menu.

Step 3: Navigate to the Protection Tab

In the Format Cells window, you'll see several tabs at the top. Click on the "Protection" tab to access the options for locking cells.

Step 4: Check the "Locked" Box

In the Protection tab, you'll see two checkboxes: "Locked" and "Hidden." Check the "Locked" box, which will prevent anyone from editing the formulas in the selected cells.

Step 5: Protect the Worksheet

Now that you've locked the formulas in the selected cells, you'll want to protect the worksheet to ensure that no one can edit the cells by mistake. To do this, go to the Review tab on the ribbon and click on the "Protect Sheet" button. You'll see a dialog box with several options for protecting the sheet.

You can choose to allow certain users to edit specific parts of the sheet, or you can require a password to make any changes. You can also restrict certain actions, such as sorting or deleting cells. Take a few moments to explore the options and choose the settings that make the most sense for your needs.

Step 6: Save Your Changes

Finally, save your changes! You'll want to make sure that you save the file in a format that supports locking cells, such as .xlsx. You can also add a password to the file to ensure that only authorized users can open it.


Locking formulas in Excel is a powerful technique that can save you time and ensure the accuracy of your data. By following these simple steps, you can protect the formulas in your spreadsheet and create a secure environment for your data analysis.

Now, go forth and Excel!

By clicking “Accept”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.