Useful Tips

How to setup?

Pin
Send
Share
Send
Send


This article will discuss how to protect a cell in Excel from changes. Fortunately, this option is present in this spreadsheet editor. And you can easily save all the data you entered from someone else's interference. Also, protecting cells is a good way to escape from yourself. By protecting cells in which formulas are entered, you will not inadvertently delete them.

How to block a cell in Excel from editing

If you often work with spreadsheets, you may run into the need to block editing cells in Excel. Such an operation is somewhat more complicated than blocking the entire sheet and today I want to tell how it is done. So, in order to block a table cell from editing and changing contents, you need to select it with a simple click of the left mouse button, and then open the menu item Home >> Format. Another menu will appear:

In the Protection section, select the Block Cell item. Then you need to click on the small gray square, which is located in the upper left part of the Excel window, exactly between the beginning of the column numbers and table rows.

As soon as you do this, the sheet will turn gray and a context menu will appear. Again, open the menu section "Format" >> "Protection".

Now, to lock the Excel cell, click on the “Protect Sheet” item. This window will open:

In it, if necessary, to maximize the security of the document, you can additionally set a password that will need to be entered in order to remove the restriction on changing the contents of cells and editing them. Click on the OK button. Cell content is locked!

How to protect all cells in an Excel file

If you want to protect absolutely all cells in your Excel file from editing and changes, do the following:

  • Go to the “ Peer review ”On the toolbar => in the subsection“ Protection "Click on the icon" Protect sheet “:

  • In the pop-up window, make sure that the checkmark is checked next to “ Protect sheet and contents of protected cells “:

  • Enter the password in the “ Password to disable sheet protection “, If you want to remove protection only those users to whom you gave the password could:

  • Select from the list and tick off those actions with sheet cells that will be allowed to all users:

If you have set a password, the system will ask you to re-enter it.

Now, for all users who try to make changes or edit cell values, this message will be displayed:

In order to remove the installed protection, go to the “ Peer review “, And in the“ Protection "Click on the icon" Remove sheet protection “. After that, the system will ask you to enter a password to remove protection.

How to protect individual cells in Excel from changes

More often than not, you may not need to protect the entire sheet as a whole, but only individual cells. As I wrote at the beginning of the article, all cells in Excel are locked by default. In order for the lock to occur, you actually need to configure sheet protection and set a password.

For example, consider a simple table with data on income and expenses. Our task is to protect against cell changes in the range B1: B3 .

In order to block individual cells, we will do the following:

  • Select absolutely all cells on the Excel worksheet (using the keyboard shortcut CTRL + A ):

  • Go to the tab “ home ”On the toolbar => in the“ Alignment ”Click on the icon in the lower right corner:

  • In the pop-up window, go to the tab “ Protection ”And uncheck“ Protected cell “:

Thus, we turned off the Excel setting for cells of the whole sheet, in which all cells are ready for protection and blocking.

  • Now, select the cells that we want to protect from editing (in our case, this is the range of cells B1: B3 ),
  • Again, go to the tab “ home ”On the toolbar and in the subsection“ Alignment ”Click on the icon in the lower right corner, as we did earlier.
  • In the pop-up window, on the “ Protection “Check the box“ Protected cell “:

  • Let's go to the tab “ Peer review "On the toolbar and click on the icon" Protect sheet “:

  • In the pop-up window, make sure that a checkmark is set next to “ Protect sheet and contents of protected cells “:

  • Enter the password in the “ Password to disable sheet protection “To remove protection, only those users to whom we provided a password could:

  • We select from the list and tick off the actions with the cells of the sheet that will be allowed to all users:

After that, the system will ask us to re-enter the password. Now, while trying to edit cells B1: B3 from our example, a message will be displayed:

How to hide a locked cell formula

If the cells that you protected from editing contain formulas, you can also hide them.

To do this, follow these steps:

  • Select the cells you want to protect and hide the formulas,
  • Go to the tab “ home ”On the toolbar and in the subsection“ Alignment ”Click on the icon in the lower right corner, as we did earlier,
  • In the pop-up window, on the “ Protection “Check the boxes in points” Protected cell ”And“ Hide formulas “:

Method 1: enable locking through the File tab

In order to protect a cell or range, you need to perform the actions described below.

  1. Select the entire sheet by clicking on the rectangle located at the intersection of the Excel coordinate panels. Right click. In the context menu that appears, go to "The format of the cells ...".

A window for changing the format of the cells will open. Go to the tab "Protection". Uncheck the option "Protected cell". Click on the button OK.

Highlight the range you want to block. Go to the window again "The format of the cells ...".

But, the fact is that after this the range has not yet become protected. It will become such only when we turn on sheet protection. But at the same time, it will not be possible to change only those cells where we checked the boxes in the corresponding paragraph, and those in which the boxes were unchecked will remain editable. Go to the tab "File".

In section "Intelligence" click on the button “Protect the book”. In the list that appears, select Protect Current Sheet.

The sheet security settings are opened. Be sure to check the box next to the parameter “Protect the sheet and contents of protected cells”. If desired, you can set the blocking of certain actions by changing the settings in the parameters below. But, in most cases, the settings set by default satisfy the needs of users to block ranges. In field "Password to disable sheet protection" you must enter any keyword that will be used to access the editing features. After the settings are completed, click on the button OK.

Now, those ranges that we have previously allocated and set their protection in the formatting settings will be unavailable for editing. In other areas, you can perform any action and save the results.

Method 2: enable blocking through the Review tab

There is another way to block the range from unwanted changes. However, this option differs from the previous method only in that it is executed through another tab.

  1. We remove and check the boxes next to the “Protected cell” parameter in the format window of the corresponding ranges in the same way as we did in the previous method.
  2. Go to the “Review” tab. Click on the “Protect Sheet” button. This button is located in the Modifications toolbox.

  • After that, the exact same sheet protection settings window opens as in the first version. All further steps are completely similar.
  • Range unlock

    When you click on any area of ​​the locked range or when you try to change its contents, a message will appear stating that the cell is protected from changes. If you know the password and knowingly want to edit the data, then in order to unlock it, you will need to do some actions.

      Go to the tab "Review".

    On a ribbon in a tool group "Change" click on the button “Remove protection from sheet”.

  • A window appears in which you must enter the previously set password. After entering, click on the button OK.
  • After these actions, protection from all cells will be removed.

    As you can see, despite the fact that the Excel program does not have an intuitive tool for protecting a specific cell, but not the entire sheet or book, this procedure can be performed by some additional manipulations through changing the formatting.

    Thank the author, share the article on social networks.

    Select the desired cell range.

    Now the first method will be provided, how to protect cells in Excel from changes. Of course, it is not much different from the second one, which will be described later, but it cannot be missed.

    So, in order to protect the table cells from corrections, you need to do the following:

    Select the entire table. The easiest way to do this is by clicking on the special button that is located at the intersection of the vertical (line numbering) and horizontal (column designation). However, you can use the hot keys by pressing CTRL + A.

    Press the right mouse button (RMB).

    In the menu, select "Cell Format".

    In the window that appears, go to the "Protection" tab.

    Uncheck the item "Protected cell".

    So, we just removed the ability to protect all cells in the table. This is necessary in order to designate only a range or a single cell. To do this:

    Select the necessary cells using conventional stretching with the left mouse button pressed (LMB).

    In the menu, again select "Format Cells".

    Go to "Protection".

    Check the box next to "Protected cell".

    We put protection on the selected cells

    We told the program which cells we want to protect from changes. But this is not enough to make them protected. To achieve this goal, you need to enable sheet protection in Excel. For this:

    Click on the "File" tab.

    In the menu, go to the "Details" section.

    Click on the "Protect Book" icon.

    From the menu, select Protect Current Sheet.

    A window will appear in which to configure. Follow the guide:

    In no case do not uncheck the item "Protect sheet and contents of protected cells".

    In the window located a little lower, you can perform more flexible settings, but by default it is set so that no one can modify the parameters.

    Enter the password in the corresponding field. It can be of any length, but remember that the harder and longer it is, the more reliable it is.

    After the manipulations have been done, you will be asked to re-enter the password so that all changes take effect. Now you know the first way how to protect a cell in Excel from changes.

    Second way

    The second way, how to protect a cell in Excel from changes, as mentioned above, is not much different from the first. Here is a detailed instruction.

    As last time, unprotect cells from the entire table and put it in the desired area.

    Go to Review.

    Click on the "Protect Sheet" button, which is located in the "Changes" tool group.

    After that, a familiar window will appear in which you need to set protection parameters. In the same way, enter the password, select the necessary parameters, check the box next to "Protect sheet and contents of protected cells" and click OK.

    Pin
    Send
    Share
    Send
    Send