• Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
Home » Office » How to Lock Cells In Excel to Protect Them

How to Lock Cells In Excel to Protect Them

By: Waseem Patwegar

Sometimes, you may want to Lock all or specific cells in Excel, in order to protect the content in those cells from being modified or erased. You will find below the steps to Lock Cells in Excel.

Lock Cells In Excel

Lock Cells in Excel

The simplest way to Lock Cells in Excel is to protect the entire worksheet and this will prevent others from deleting or editing the content in any of its Cells.

However, locking all Cells in Excel by protecting the entire worksheet prevents others from working on it.

Hence, this method is not suitable if you are sharing the worksheet with others or you want others to be able to work on the worksheet.

Luckily, it is also possible to Lock Specific Cells in Excel and this will prevent others from making changes only in those selected Cells.

1. Lock All Cells in Excel Worksheet

As mentioned above, you can Lock all cells in Excel by protecting the Worksheet. By default, all the Cells in a Protected worksheet are locked.

1. Click on the Review tab and then click on Protect Sheet option in the “Changes” Group.

Protect Sheet in Excel

2. In the Protect Sheet box that appears, select Protect worksheet and contents of locked cells option > Enter a Password (optional) > select actions that you want to allow (Select Locked Cells, Select Unlocked Cells, etc) and click on OK.

Protect Sheet Dialogue Box in Excel

3. If you had typed a Password, you will be prompted to reconfirm the password.

Once the worksheet is protected, all the Cells in the worksheet will be locked and Excel will display “The Cell or Chart you’re trying to open is on a protected sheet” message, whenever anyone tries to modify the worksheet.

Protected Worksheet Message in Excel

In case you want to edit the Cells, you will have to first Unprotect the worksheet by clicking on Review tab > Unprotect Sheet.

Unprotect Worksheet in Excel

Note: You will be required to enter a Password, in case the worksheet that you are trying to unlock was protected using a Password.

2. Lock Specific Cells in Excel

Follow the steps below to Lock only certain selected or specific cells in Excel worksheet.

1. Select the entire sheet and click on the dialog box launcher icon located in the “Alignment” Group of the Home Tab.

Launch Dialog Box in Excel

2. In Format Cells dialog box, click on the Protection tab and uncheck the Locked box.

Unlock Cells in Excel

3. Click on OK to save the changes.

4. Now, select only the Specific Cells that you want to Lock and click on the dialog box launcher icon located in Alignment group of the Home Tab.

Select Cells to Lock in Excel

In this case, we have selected Cells D2 to D5 to prevent others from making changes to Rates in the worksheet.

5. In the Format Cells Dialog box, click on the Protection tab and select the Locked option.

Lock Cells in Excel

6. Next, click on the Review tab and click on Protect Sheet option in “Changes” group.

Protect Sheet in Excel

7. On Protect Sheet screen, select Protect worksheet and contents of locked cells option > select the actions that you want to permit on this worksheet (Select Cells, Insert rows, Format Cells, etc) and click on OK to save the settings.

Protect Sheet Dialogue Box in Excel

After this only the selected Cells will become locked in the worksheet and all other cells will be unlocked.

Also, users will be able to work on the worksheet and perform all permitted actions that you had selected in Protect Sheet window.

3. Hide Formula in Locked Cells

Even if a Cell is locked in Excel, the formula in the Locked cell becomes visible when anyone clicks on the Locked Cell.

Follow the steps below to Lock Cells and also hide the Formula in them.

1. Select the Cells that you want to Lock and Hide the Formula and click on the dialog box launcher icon located in Alignment section of the Home Tab.

Select Cells to Lock in Excel

2. In Format Cells window, check both Locked and Hidden options.

Lock and Hide Formula in Excel

3. Click on OK to save this setting.

This will Lock the selected cells and also hide the Formula, when someone selects or clicks on the Locked Cells.

Protect Sheet Option Grayed Out in Excel

If you find that the Protect Sheet option is grayed out, it likely that you are dealing with a shared worksheet. In order to fix this issue, you will have to disable sharing on the workbook.

1. Click on the Review tab and click on Share Workbook option in the “Changes” group.

Share Workbook Option in Excel

2. On Share Workbook window, uncheck Use the old Shared workbooks feature… or Allow changes by more than one user… option and click on OK.

Share Worksheet Window in Excel

The Protect Sheet option in Excel can also be grayed out due to multiple worksheets being selected.

To fix this, right-click on any worksheet tab and click on Ungroup Sheets option in the menu that appears.

Ungroup Worksheets in Excel

Hope this has provided you with a good understanding of how to Lock Cells in Excel and protect your work.

Related
  • How to Hide Cells, Rows and Columns In Excel
  • How to Merge and Unmerge Cells in Excel

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar



Gadget Guide

  • Should You Buy MacBook or Windows Laptop
  • Chromebook vs Windows Laptop
  • 12 Reasons to Switch to Chromebook
  • Kindle E-Reader Comparison
  • Best Asus Laptops Under 500




About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved