Lock Cells in Excel

We lock cells in excel to protect them so that there isn’t any unwanted changes in the cell, by default all the cells in excel are locked which can be seen by right click on the cell and then click on format options which will open a wizard box for us, now click on protection tab and we can see the option for locked cells, to protect a cell after locking it we need to put a protection by protect worksheet option.

How to Lock Cells and Protect Formulas in Excel?

Formulas in Excel are easy to create and edit. A single formula in an Excel worksheet can be very important. Though it is easy to edit a formula, any accidental change in the formula can lead to completely different and erroneous results. When the Excel sheets travel from one person to another, the chances that a person hit a deleterious key (such as backspace, delete, alphabet, or number key) on the formula while going through it also increases. Thus, it is important to secure your Excel worksheet from any change. To address such issues, Excel has incorporated Lock and protect functions that prevent other users from doing any further change (deleting or overwriting) in an Excel worksheet.

How to Lock Cells in Excel?

Let us learn how to lock cells in excel 2016 with some examples.

You can download this Lock Cell Formulas Excel Template here – Lock Cell Formulas Excel Template

Lock Cells in Excel – Example #1

Suppose you have a worksheet in which there are three values v1, v2, and v3, and you to calculate the average of the three values.

Lock cells in Excel Formula

Cell E5 contains the formula for calculating the average. Now you want to lock the cell in excel and protect this formula.

Since all the cells in Excel are locked by default, let us first unlock all the cells in excel.

To do this, first, select all the cells using Control + A (or Command + A).

Lock cells in Excel formula (cntrl + A)

Then open the Format Cells dialog box by pressing Control + 1 (or Command + 1).

Lock cells in Excel Formula (cntl + 1)

Under the Protection tab, uncheck the Locked option and click OK.

Lock cells in Excel Formula (uncheck)

All the cells in the excel worksheet will be unlocked.

Now, select the cell containing the formula and press Control + 1 to open the Format Cells dialog box. Under the protection tab, check the Locked option.

Lock cells in Excel formula (check gif)

Then, click OK to lock the cell formula in excel. Now, you need to protect the formula. To protect the cell in excel, go to Review Tab and click on Protect Sheet or Sheet (Highlighted below).

Lock cells in Excel formaula (protect sheet)

You can optionally give a password. Click OK.

Lock cells in Excel formula (password)

Now the formula cell in excel is locked and protected. If you try to change the cell, a window will pop-up, as shown below.

lock formula (password) 1

You will find that only this particular cell is protected. If you try to overwrite any other cell, no message or warning appears. This is because only the locked cells in excel are protected.

A locked cell in excel or formula cannot secure the cell from further overwriting unless it is protected. So, to protect a formula from any change, you need to lock the cell formula in excel and then protect it. It is important to note that by default, all cells in a worksheet are locked. Therefore, if you want to protect only the formula cells, you need to unlock all the other cells and then protect the formula cells.

Lock Cells in Excel – Example #2

Suppose you have the sales data for different products obtained in different zones, as shown below. You are supposed to calculate the total sales of each product separately and identify the product with the highest sales. You are also supposed to lock a cell in excel and also protect the formula for calculating the highest sales; however, the sales data and its sum can be editable.

lock Formulas Example 1

Let us first calculate the total sales for each product. To do this, use the syntax:

=SUM(C3:G3) in cell H3.

lock Formulas Example 1-1

Press ENTER and then drag it to the rest of the cells.

lock Formulas Example 1-2

To identify the product with the highest sales, use the index formula:

=INDEX(B2:B17,MATCH(MAX(H2:H17),H2:H17,0))

lock Formulas Example 1-3

This will give the product with the highest sales in the cell K6.

lock Formulas Example 1-4

Now, to protect this formula cell from any further change, you need to first unlock all the cells in the excel sheet. To do this, press Control + A and then press Control + 1. This will open the Format Cells dialog box.

lock Formulas Example 1-6

Now, uncheck the Locked option under the Protection Tab. Now, select the cell K9 containing the formula and again press Control + 1. Under the Protection tab, check the Locked option and click OK. This will lock the cell formula.

lock Formulas Example 1-5

Now, go to the Review Tab and select the Sheet option to protect the formula and select OK. You can optionally select a password.

lock Formulas Example 1-7

The formula for identifying the product with the highest sales has been locked.

How to Protect Formulas in Excel by Locking Cell?

Let us take an example to see protect formulas in Excel by using Cell Lock.

Suppose you have the oil prices for different cities for August and September. You need to calculate the median oil prices for August and September, and see in which month the oil prices were higher.

To calculate the median prices, use the syntax:

Median Prices for Aug 

= MEDIAN(C5:C18) for Aug

lock Formulas Example 2-1

Median Prices for Sep

= MEDIAN(D5:D18) for Sep

lock Formulas Example 2-2

To identify in which month the prices were higher, use the syntax:

=INDEX(F4:F5,MATCH(MAX(G4:G5),G4:G5,0))

lock Formulas Example 2-3

This will give the month (Aug here).

lock Formulas Example 2-4

Follow the following steps to protect all the cells containing the formula –

  1. Unlock cells in Excel.


    Select all the cells by pressing Control + A. Then press Control + 1 to open the Format Cells dialog box and go to the Protection tab. Then, uncheck the Locked option. This will unlock all the cells.

    lock Formulas Example 1-6

  2. Select and lock the cells containing the formula.


    Click Find & Select in Home and select Go to Special. If you are using Mac, then in the Edit Tab, select Go To…

    lock Formulas Example 2-5

    and press Special.

    lock Formulas Example 2-6

    In the Go To Special dialog box, select Formulas and click OK.

    lock Formulas Example 2-7

    All the cells containing the formulas will be selected. Now, to lock these cells, press Control + 1 and go to Protection Tab in the Format Cells dialog box. Check Locked and click OK.

    lock Formulas Example 2-8

  3. Protect the formula cells.


    To protect the cells with formulas, go to Review Tab, and click on Sheets.

    lock Formulas Example 2-9

    You can optionally provide a password. Let us provide a password “12345” and then click OK. The formulas are now protected.

Things to Remember

  1. Only locking the cells in excels does not provide security to the cells from any further change.
  2. You need to lock a cell in excel and protect the formulas to secure them.
  3. All the cells in a worksheet are locked by default.
  4. Only protecting formula cells without locking also does not secure them.

Recommended Articles

This has been a guide to Lock Cells and Protect Formulas in Excel. Here we discuss how to lock cells in excel and how to protect formulas in Excel along with practical examples and downloadable excel templates.

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *