How to Lock Cells and Protect Formulas in Excel (Step by Step Guide)

Excel Functions Tutorials

Lock cells in Excel (Table of Contents)

How to Lock Cell and Protect Formulas in Excel?
How to Lock Formulas in Excel?

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 a 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 which prevent other users to do 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.

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.

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).

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

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

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 Locked option.

Then, click OK to lock 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).

You can optionally give a password. Click OK.

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.

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.

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

=SUM(C3:G3) in cell H3.

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

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

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

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

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

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.

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

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 how to 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

Median Prices for Sep

= MEDIAN(D5:D18) for Sep

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

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

This will give the month (Aug here).

Now, you are supposed to protect all the cells containing the formula.

To do this, follow the following steps:

Step 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.

Step 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…

and press Special.

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

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.

Step 3 – Protect the formula cells.

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

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

Things to Remember About Lock Cell in Excel

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

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

Lock Cells in Excel