WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Lock Cells in Excel

Lock Cells in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

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.

lock Formulas Example 1-6

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…

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

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

  • Checkbox in Excel
  • Excel Protect Formula
  • Scroll Lock in Excel
  • Last Day of the Month in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Lock Cell Formulas Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More