How to Lock Cells in Excel
Updated on 09th Dec, 23 9.1K Views

This blog covers locking Excel cells, including individual cells or full worksheets, to restrict editing to maintain integrity. Through clear explanations and step-by-step examples, you’ll understand the concepts and practical applications of cell locking to preserve accuracy across your worksheets.

Table of Contents

Want to learn Excel from the basics to the expert level? Check out the Excel course provided by Intellipaat!

How to Lock All Cells in Excel

Let’s find out how you can lock the entire cell in an Excel sheet.

  • Step 1: Select all the cells in the spreadsheet. You can do this by selecting the entire sheet using the selector triangle to the left of column A and above row 1 or by pressing Ctrl + A on your keyboard. This will select and highlight all the cells on the active sheet.
How to Lock All Cells in Excel
  • Step 2: With all the cells selected, go to the Home tab on the ribbon. In the cell group, click the Format button. This will open up a menu of formatting options.
How to Lock All Cells in Excel
  • Step 3: From the Format menu, click on the Format Cells option, which will open a dialog box as shown below.
How to Lock All Cells in Excel
  • Step 4: Click on the Protection tab in the top right and check the Locked box to lock the cells. A disclaimer will appear that you also need to protect the worksheet for locking to take effect.
How to Lock All Cells in Excel
  • Step 5: With the cell range still selected, go to the Home tab and click Format. In the dialog box, choose Protect Sheet.
How to Lock All Cells in Excel
  • Step 6: In the Protect Sheet dialog box, enter a new password so only those with the password can edit the locked cells. Keep the two boxes checked, as shown in the image, and click OK.
How to Lock All Cells in Excel
  • Step 7: Re-enter the new password when prompted, and click OK again.
How to Lock All Cells in Excel
  • Step 8: The cells are now protected. Attempting to edit the locked cells without the password will display an error, as shown in the image: 
How to Lock All Cells in Excel

Intellipaat provides an Advanced Certification in Microsoft Excel. Enroll now to get a professional certificate!

Get 100% Hike!

Master Most in Demand Skills Now !

Unlock the Locked Cells

Now, let’s find out how to unlock locked cells.

  • Step 1: To unlock previously locked cells, keep the selected cell range. Go to the Home tab and click Format. In the dialog box, choose Unprotect Sheet to unlock the sheet, as shown in the image below:
Unlock the Locked Cells
  • Step 2: It will prompt you to enter the password to unlock the cells.
Unlock the Locked Cells
  • Step 3: Once you have entered the correct password, you can edit the previously locked cells.
Unlock the Locked Cells

Learn how to keep your Excel sheets safe and sound by adding a password with our simple guide! – How to Protect Excel Sheet with Password.

Lock Specific Cells in Excel

If you only need to lock certain cells rather than the entire sheet, follow these steps to lock specific cells:

  • Step 1: To lock specific cells rather than the entire sheet, first select the particular cells you want to lock, as shown in the image below:
Lock Specific Cells in Excel
  • Step 2: Once you have selected the specific cells to lock, right click on those selected cells. From the pop-up menu, choose Format Cells.
Lock Specific Cells in Excel
  • Step 3: Once the Format Cells dialog box opens, go to the Protection tab. Check the box for Locked. This will lock only the cells you have selected rather than the entire sheet.   
Lock Specific Cells in Excel
  • Step 4: As a reminder, locking cells alone has no effect unless you also protect the entire sheet. To do this, go to Home tab > Format > Protect Sheet > Set a Password > Click OK

Protecting the sheet is required to activate cell locking and prevent users from unlocking those cells you have selected to lock.

Check out Intellipaat’s guide on MS Excel Interview Questions to crack interviews for data analysis jobs.

How to Lock Formula Cells

Follow these steps to lock the formulas in an Excel worksheet, which helps protect the integrity of the formulas by preventing unwanted changes.

  • Step 1: Select the cells containing formulas you want to lock. For example, if cells B1:B10 contain the formulas, select that entire range.
How to Lock Formula Cells
  • Step 2: Go to the Home tab at the top of the spreadsheet.
  • Step 3: Click on the Format drop-down menu and select Format Cells from the options. This will open the Format Cells dialog box.
How to Lock Formula Cells
  • Step 4: To protect the formulas in a sheet, navigate to the Format Cells dialog box. Within this box, there is a Protection tab. Check the two boxes in the Protection tab that allow you to lock cells and hide the formulas. 
How to Lock Formula Cells
  • Step 5: Once you check the boxes, click OK to hide them from users.
  • Step 6: However, remember that locking and hiding formulas have no effect unless you also protect the sheet. To do this, go to the Home tab, select Format, click on Protect Sheet, set a password, and then click OK.
How to Lock Formula Cells

Career Transition

Intellipaat Job Guarantee Review | Intellipaat Job Assistance Review | Data Engineer Course
Got Job Promotion After Completing Artificial Intelligence Course - Intellipaat Review | Gaurav
How Can A Non Technical Person Become Data Scientist | Intellipaat Review - Melvin
Artificial Intelligence Course | Career Transition to Machine Learning Engineer - Intellipaat Review
Non Tech to Data Scientist Career Transition | Data Science Course Review - Intellipaat

Benefits of Locking Cells in Excel

Benefits of Locking Cells in Excel

Locking cells in Excel can have numerous benefits, depending on your requirements. Let’s look into some of the advantages of locking cells.

  • Prevent Accidental Changes: Locking cells can prevent unauthorized or accidental changes to important data or formulas. For example, if you lock cells containing formulas, it protects the integrity of the formulas.
  • Multiple User Access: If multiple people access a shared workbook, locking certain cells allows you to restrict edits by certain users. This enables smooth collaboration.
  • Protect Data: Integrity-sensitive data like financial figures or personal data can be locked to prevent tampering or unintentional changes that corrupt the data. Locking preserves data integrity.
  • Focus User Input: By only keeping input cells unlocked, you can guide users to only enter data in desired locations rather than arbitrarily throughout. This facilitates workbook design.     

FAQs

How do you unlock cells in a protected Excel sheet?

To unlock cells in a protected Excel sheet, first unprotect the sheet by going to Review > Unprotect Sheet and entering the password if one was set. Then, select the locked cells you want to unlock, go to Format Cells > Protection, and uncheck the locked checkbox. Don’t forget to re-enable sheet protection after.

Why won’t my locked cells stay locked in Excel?

If you have locked cells that seem to allow editing even when protection is turned on, you likely have not activated sheet protection. After locking cells, go to the Review tab and select Protect Sheet. In the Protect Sheet dialog box, choose what editing you want to permit and enter a password if desired before clicking OK.

What do locking cells in Excel do?

Locking cells in Excel protects them from being edited when protection is turned on for a worksheet. This allows certain cells, like headers or formulas, to stay static while other cells can be edited by anyone accessing the spreadsheet. 

How do you lock a specific cell in a formula?

To lock a single cell referenced in a formula, add a $ sign in front of the column and row references you want to remain constant. For example, to lock cell C2 as an absolute reference, you’d update the formula to reference it as $C$2. The $sign fixes that cell reference. As you copy or drag the formula cell, it will keep referencing exactly cell C2 instead of changing that reference. 

Can locked cells still be formatted in a protected Excel sheet?

Yes, locked cells in a protected Excel sheet can still be formatted if the user has the formatting permissions when sheet protection is enabled. However, locked cells cannot be moved, deleted, or edited unless those permissions were also given before protecting the sheet.

Course Schedule

Name Date Details
Data Scientist Course 02 Mar 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 09 Mar 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 16 Mar 2024(Sat-Sun) Weekend Batch
View Details

Leave a Reply

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

Speak to our course Advisor Now !

Related Articles

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.