Blue Flower

Lock Cells in Excel

In as much as more people are using one and the same workbook and enter the data into a specific cell is desirable to prevent the possible change of important formulas or accidental deletion of data in the worksheet. For this purpose we use the lock cells in the Worksheet.

selecting all cells on excel worksheet

All Cells on a Excel Worksheet by default option are Locked, you can check if you have selected all the cells (or single cell) and right-click mouse the selected cells and then click on the Format Cells to open the Protection tab.

locked cells default option in excel

  • All cells in Excel are locked by default (selected options Locked) but the option is not active until you activate the worksheet protection.
  • To prohibit a possible change of certain cells need to lock them. On the sheet is necessary to unlock the cells you want to be able to change, and then activate the Protect Sheet.
  • Protect Sheet forbids that in cells that are locked can enter data or delete or modify the property and the like.
  • During lock Excel 2013 worksheet can set a password, but we do not have to if we do not want.
  • When we perform Protect Sheet in the choice we have options that can enable or disable the option Select Unlocked Cells, we are sure turn on.
  • Here in the picture above you can see another option hidden. This means that the cell to which we apply this option in the formula bar (formula field) will be prominently displayed formula or text when selected by the respective cell. It is eg used to hide the formulas that you write, and only you know to create the formula.

So Locked option is important if we set the protection on the Excel Worksheet. This option applies only to a specific cell, so that you make sure you make the Worksheet protection that would not have happened if some cells can not be changed or even select them.

Lock or Unlock the Cells in a Excel Worksheet

For this tutorial I have prepared an example in the figure below. Unlocking a cell range or data in Excel is performed in two main steps. When I write unlock a range of cells that automatically applies to the locking of the remaining unselected cell. So procedures locking/unlocking a certain range of cells is as follows:

The first step is to unlock the data range or the specific cell

  1. It is necessary to select a specific or particular range of cells in the worksheet that want to unlock
  2. Right-click mouse on the selected data range
  3. From the shortcut menu, choose Format Cells command to open a dialog window
  4. In the Format Cells dialog box to Protection tab, you need to Off Locked option

unlocked range of cells in excel

How to Lock or Protect a Excel Worksheet

The second step is to protect the worksheet (lock Excel worksheet). This action is done only to protect the active worksheet but not the protection of the workbook is opened or the other worksheets.

After Unlock/Lock range of data or specific cell approach worksheet protection (protect the Excel Worksheet) and also lock specific cells.

  • On the ribbon, click on the Review toolbar
  • In the group commands Changes click Protect Sheet command to open a dialog window
  • In the Protect Sheet dialog box, turn on the option that you want to allow the user when working on a Worksheet.

Be sure to include the Select unlocked cells because otherwise you will not be able to work because Excel will not allow the selection of cells. Other options include optional and explore them more personally what that option means (though this is a basic tutorial for beginners).

If you want to protect the worksheet so that no one can change your settings in the Password to unprotect sheet set your secret password so that only you can make changes to the worksheet. In as much as you protect the worksheet is not too important then you can leave the Password blank and Lock the Worksheet.

protect worksheet and lock cells in excel

At the end of our range/table looks like as shown below and users can add, edit data only in cells that are not locked. You notice that I am protected or locked cell written in red fonts. So the user can modify the data only in the range of cells B2:E10.

protected worksheet and cells in excel 2013