How To Lock and To Not Lock Cells In Excel

How To Lock and To Not Lock Cells In Excel

 How To Lock Cells In Excel 

Do you wanna know how to lock & protect specific cells in Excel? Well, this time CloverLeo will share excel tips and tricks to solve this problem.

The goal is to lock Excel formulas to certain cells or vice versa, you want some Excel cells cannot be edited and some other cell locations can be changed.

In Excel, there are 3 levels of protection. The first level is File Protection, Workbook Protection and the last is Worksheet Protection. And there is one additional level of protection for VBA Macro code access



In some cases, you may want only part of the cell to be locked from changes. And in other cases, you want the opposite, on a particular sheet all cells are protected except for a few cells or ranges.

Before continuing the discussion, try to think about which case better reflects your needs. Do you want to protect some cells or you want to not lock certain cells or ranges?

This is important because your choice will more or less affect the steps you have to take to lock certain cells in Excel.

How to Lock Specific Cells in Excel

For this first option, you want all Excel cells unlocked or protected except for some Excel cells or ranges.

This means that you want the file user to be able to change anything on a sheet except for a few cells that you specify that the contents cannot be edited.

Pay attention to the following image:

How to Lock Specific Cells in Excel

In this example, you want each cell in Range A1: F1 to be locked so that it cannot be changed by the user.

The main goal in this section is to protect the limit of certain cells or ranges so the user cannot change their contents.

The steps to partially protect these cells are as follows:

Deactivate Cell Protection

By default, all cells of a sheet are locked when you enable sheet protection.

For that, the first step you need is to disable these protection settings. The trick is as follows.

  1. Select all cells on the sheet associated with the shortcut Ctrl + A or click the triangle icon in the upper left corner of the sheet.
  2. In one of the selected cells, right-click then select Format Cells menu.
    Deactivate Cell protection
  3. Next will appear Format Cells Dialog. In this dialog box select or activate Tab Protection.
  4. In the Protection tab, uncheck the Locked option.
    Deactivate Cell protection Tutorial
  5. Click OK to close the Format Cells dialog box

You can also access the “Format Cells” dialog box via the Home Tab menu – Group Cells – Format – Format Cells

 

Alternatively, for a shorter and easier way you can disable protection for all cells in the following ways:

  1. Block or select all cells on the sheet associated with the shortcut Ctrl + A or click the triangle icon in the upper left corner of the sheet.
  2. Then deactivate the protection by clicking the Lock Cell menu in the Home Tab – Group Cell – Format.
    Deactivate Cell protection Trick

At this stage, the main goal is to disable lock cell settings for all excel cells on the assembled sheet.



Lock Certain Cells

The next step, after setting all unprotected cells (unlocked cells) is to activate the lock cell settings for certain cells that you need. In this example, the cell or range in question is Range A1: F1.

The method is as follows:

  1. Block/select range A1: F1. Please adjust the range to be locked as needed.
  2. In one of the selected cells, right-click then select Format Cells menu.
  3. After the Format Cells Dialog Box appears, activate the Protection Tab.
  4. On the Protection tab, make sure the Locked option is checked.
    How to Lock Certain Cells in Excel
  5. Click OK to exit the Format Cells Dialog.

For an easier way, you can use the Lock cell menu as before on the Home Tab – Group Cell – Format.

The Lock cell menu on the Home Tab – Group Cell – Format is used to change the locked format settings for a sheet cell. If previously active (checked / protected) then it becomes inactive and vice versa if previously it was inactive / unlocked (unchecked) it will become active.

After the locked cell settings are correct and appropriate, we continue to the next step.



Turn on Sheet Protection

In the previous steps, you have disabled protection for all cells except for certain cells. Next, all you need to do is activate sheet protection.

Here are the steps:

  1. Open the Review Tab – Group Changes then select the Protect Sheet menu.
    Turn on Sheet Protection
  2. Check the Protect worksheet and contents of locked cells option
  3. Determine what options you allow file users to do in the Allow all users of this worksheet to: section. Details about these options will be explained in the next section.
  4. Write down the password that you will use to protect the sheet.
    How To Lock Sheet in Excel
  5. Click OK and you’re done.

It’s finished and you have successfully locked or protected certain cells in Excel which in this case is the range A1: F1.

How to NOT lock Specific Cells in Excel

If in the previous section we locked certain cells, this section will explain the opposite, that is how to not locking or protecting certain cells or ranges on an excel sheet when you activate a password or Sheet protection.

This means that you will exclude some cells or ranges from the protection so that they are not locked and can be changed or filled when sheet protection is active.

For example, suppose you create the following form on an excel sheet. Then you want when sheet protection is activated, every cell in the range D5: D10 isn’t protected so that it can be filled by the user of the excel file.

How to NOT lock Specific Cells in Excel

The steps you need to take are as follows:

Disabling Lock Cell

The first step you will need is to disable the Lock Cells setting on the cell or range you need.

In this example, this is the range D5: D10. The method is almost the same as before, which is:

  1. Block or range selection that will not be locked (Range D5: D10)
  2. Open the Format cells dialog box by clicking the Format Cells menu on the Home Tab – Group Cell – Format or by right-clicking in the selected cell then select the Format Cells menu.
  3. Once the Format Cells Dialog Box appears, click on the Protection tab and make sure the Locked option is unchecked.
    Disabling Lock Cell
  4. Click OK to exit the Format Cells Dialog.

After making sure that the Locked setting on certain cells is not active (unchecked), the next step is to turn on sheet protection.

On the Format cell protection tab there is a Hidden option which you can check if you want Excel to hide excel formulas or fill in a cell when you activate sheet protection.



Turn on Sheet Protection

After making sure the cell or range you want is unlocked, the steps you need to do is activate the worksheet protection as previously explained. That is by accessing the Protect Sheet menu on the Review Tab – Group Changes, then proceed to set some of the parameters you need.

An explanation of the parameters or settings of this Protection Sheet will be explained in the following section.



Excel Sheet Protection Settings

When you activate Sheet Protection, a dialog box like this will appear:

How To Lock Sheet in Excel

What do these parameters mean?

  • Protect worksheet and contents of locked cells: If you check this section then when you click OK you will activate the protection of the corresponding Sheet. so that any cells that are set as locked on the sheet will be locked.
  • Password to unprotect sheet: This section sets the password or password that will be used to unlock protection. The password here is optional. If you want the sheet locked without a password leave this section blank. Choosing this option is intended to prevent accidental changes to the contents of a sheet.
  • Allow all users of this worksheet to: This section sets some parameters about what the user can do when a sheet is locked. You can learn the explanation of each section in the table below.

If the following settings are checked / active while the sheet is protected the user will be allowed to do things as described in the following:

  • Select locked cells
    Select, select, or activate locked cells. By default, you are allowed to select locked cells.
  • Select unlocked cells
    Select, select, or activate unlocked cells. By default, you can select unlocked cells and can use the TAB key to move between unlocked cells on a protected worksheet.
  • Format cells
    Change the Cell Format or Conditional Formatting on locked cells.
  • Format columns
    Use any column formatting commands (column), such as changing column widths or hiding columns.
  • Format rows
    Use any row formatting commands, such as changing row height or hiding rows.
  • Insert columns
    Allows you to insert a column (column)
  • Insert rows
    Allows you to insert a row (row)
  • Insert hyperlinks
    Insert hyperlinks, including in locked cells
  • Delete columns
  • Delete rows
  • Sort
    Use each command to sort data
  • Use AutoFilter
    Using the filter feature on a cell range when AutoFilter is active. Including enabling or disabling Autofilter.
  • Use PivotTable reports
    Format, change the layout, refresh or change a PivotTable report including creating a new PivotTable.
  • Edit objects
    Make changes to graphic objects including maps, charts, shapes, text boxes, and any controls that you did not unlock before you protected the worksheet. Make chart changes, such as formatting. Add or edit comments.
  • Edit scenarios
    View scenarios that you have hidden, make changes to scenarios that you have prevented from changing and deleting these scenarios.

Be the first to comment

Leave a Reply

Your email address will not be published.


*