How to prevent cell editing in Excel. Data protection in Excel, data protection levels

In this article I will tell you how to protect cells in Excel from changes and editing. Cell protection may mean that users who open your file will not be able to edit cell values ​​or see formulas.

Before we figure out how to set up protection, it is important to understand how cell protection works in Excel. By default, all cells in Excel are already locked, but in fact, access to them will be limited after you enter a password and access restriction conditions in the sheet protection settings.

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 “ Review” on the toolbar => in the subsection “ Protection” click on the icon “ Protect sheet “:

• In the pop-up window, make sure the checkbox next to ““ is checked:

• Enter the password in the ““ field if you want only those users to whom you have shared the password to be able to remove the protection:

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

• Click “ OK “

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

Now, all users who try to make changes or edit cell values ​​will see the following message:

In order to remove established protection, go to the “ tab Review “, and in the section “ Protection” click on the icon “ Remove protection from sheet “. After this, the system will ask you to enter a password to remove the protection.

How to protect individual cells in Excel from changes

Most often, you may not need to protect the entire sheet, 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 blocking to occur, you actually need to set up sheet protection and set a password.

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

In order to block individual cells, do the following:

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

• Let's go to the tab “ Home” on the toolbar => in the “ section Alignment ” click on the icon in the lower right corner:

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

• Click “ OK “

So we turned off Excel setup for cells of the entire 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 a range of cells B1:B3 );
• Let's go to the tab again 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 “ tab Protection“check the box “ Protected cell “:

• Let's go to the tab “ Review ” on the toolbar and click on the “ icon Protect sheet “:

• In the pop-up window, make sure that the checkbox next to “ Protect the worksheet and the contents of protected cells “:

• Enter the password in the “ Password to disable sheet protection “, so that only those users to whom we have given the password can remove the protection:

Like any information, data in Excel sometimes has to be protected from editing or accidental deletion. Unfortunately, there is no such button in the menu, by clicking on which you can protect an individual cell from editing in one action. But there is a set of different protections, combining which you can get the desired result.

You can protect a cell from changes in Excel only by protecting the entire sheet. By default, when you enable worksheet protection in Excel, all cells will be protected. If you select a cell or several and enter the cell format, you can go to the tab "Protection" see the check mark "Protected cell". By unchecking this box, we remove protection from these cells, and they will always be available for editing.

Now, when you enable sheet protection in Excel, on the tab "Review" menu item "Protect Sheet", we will protect from changes all sheet cells for which the checkbox has not been unchecked. And the cells we previously edited can be changed. In this case, at the moment the protection is turned on, it will be possible to do additional settings and choose what exactly cannot be done with the cells.

Now let's look at the possibility of password protecting individual cells in Excel. It will be necessary to select the cells that we want to protect with a separate password in Excel, and on the tab "Review" select menu item "Allow changing ranges". In the window that appears, click "Create", if desired, enter the name of the range, check or enter a new range of cells, and specify the password.

But after all these settings, protecting cells from editing in Excel still does not work. For everything to work, you need to enable sheet protection, as discussed above. After this, all cells will be protected from changes, but for the cells we have selected there will be a separate password, by entering which you can make the necessary changes without unlocking the sheet.

Knowing the capabilities of protecting cells in Excel, you can now combine different options. For example, by selecting all the cells of a sheet and going to the cell format, you can use the protection tab to cancel the protection of these cells, then select a separate group of cells and return protection to them. On at this stage When you enable sheet protection, only the cells we have marked will be locked. And if you first enter a separate password for the selected cells through the menu for allowing changes in ranges, then after turning on the sheet protection, you can enter any data into all cells, and changes can be made to locked cells only when you enter a password. Actually, this is the only way to protect individual Excel cells from changes using standard means.

So, first of all, how to distinguish a protected cell from an unprotected one? Select the cell/cells, right-click on one of them and select “Format Cells...” from the context menu.

Step 2

Go to the “Protection” tab. This is the rightmost tab in the cell formatting window.
Look at the two checkmarks that are there.
If the top (“Protected cell”) is checked, it means that editing these cells will not be available if you enable sheet protection. If you remove it, it can be edited even in protection mode.
If the bottom one (“Hide formulas”) is checked, when protecting the sheet, you will not be able to see the cell formula in the formula bar.
Click “OK” to close this window.
By default, all cells have the top one marked

Step 3

Select “Tools → Protection → Protect sheet...” from the menu at the top.

Step 4

You will see a window in which you can configure exactly how it will be sheet protected. You can enter a password in the text field that will need to be entered to remove the protection.
Now about the main checkboxes in the “Allow all sheet users” box. “Selecting blocked cells” – is it possible to select cells that have the “Protected cell” checkbox (see step 2), “Selecting unblocked cells” – that do not have the checkbox. “Formatting cells/columns/rows” – is it possible to format the format of cells (color, font, borders, ...), columns (width), rows (height). “Inserting columns/rows/hyperlinks” – whether you can add columns, rows to the page, insert hyperlinks, “Deleting columns/rows” – whether you can delete columns and rows. Click “OK” to protect the sheet.

Step 5

Now you will not be able to change the contents of any of the cells that have the “Protected cell” checkbox checked. If you try, you will see a window saying that you cannot do this.

Step 6

To remove sheet protection, select “Tools → Protection → Unprotect sheet...” from the menu. If you specified a password when protecting the sheet, Excel will ask you for the password.

Step 7

Now to practice. First, decide which cells should be protected and which should not. There are two examples in the figure - cells that should be blocked are marked in dark.
In the left one it’s easier to first block all the cells, then unlock the rectangular field.
In the right one it’s easier to first unlock everything, then block the necessary cells.

Step 8

Select all cells by clicking on the rectangle at the intersection of the column and cell markings. Then go to the already familiar “Format Cells” window (right-click and select this item in the context menu).

The other day, one of the users received a question: How to protect certain cells from changes?

Can in the usual way, it is possible using VBA (but I am not a supporter of the VBA solution, since this is not allowed and will work everywhere).

First, make the cells defensible;

In the format of the cells that you want to protect, you must select the checkbox in the “Protected cell” field. To do this, just right-click and select context menu“Format Cells...” and go to the “Protection” tab.

The mere presence of this flag does not protect the cell in any way; it only indicates that the cell will become protected after the sheet protection is set. In every new Excel workbook, all cells are protected by default. It is worth noting here that you can change the format of cells at once in the entire selected range, as well as in a group of non-adjacent cells.

Secondly, install sheet protection.

To set worksheet protection in Excel 2007/2010, you must go to the “Review” tab, in the “Changes” group and click the “Protect Sheet” button. In Excel 2003, sheet protection is set through the menu item Tools/Protection/Protect Sheet.

There is a way quick installation protection for all sheets at once. Only after these two steps have been completed will the cell/cells protection take effect.

By protecting cells, a table can be clearly demarcated into areas that can be changed and those that cannot be changed. As you have already seen, this is quite simple to do. The only inconvenience arises when you need to selectively protect cells, for example, install protection only on cells with formulas, but this inconvenience can also be solved. To do this, just go to the “Home” tab and in the “Editing” group open the menu of the “Find and Select” button, then select “Select a group of cells...” and select the required option ( read more about selecting a group of cells). After all the necessary cells have been selected, all that remains is to make the cells protected, and then set the sheet protection. Let me remind you that to select all the cells of the sheet, you can use the hotkey combination Ctrl1+A or click on the so-called zero cell (in the screenshot the zero cell is highlighted with a red frame).

That's all.

There is another option to solve the same issue:

Select all cells, then in the menu Format select team Cells(CTRL+1) In the standard dialog box that appears, select the bookmark Protection and uncheck the box opposite Protected cell. Now select the cells that are intended to be blocked (you can select non-adjacent cells and ranges using CTRL key) and in the menu Format again select the Cells command, and then check the box next to Protected cell. Now, in the Tools menu, select Protection and the Protect Sheet command. In the Allow all users of this sheet list: be sure to uncheck the box next to Highlight locked cells and check the box next to Highlight unlocked cells, enter required password and click Ok.

You can protect information in an Excel workbook in various ways. Set a password for the entire book, then it will be requested every time you open it. Put a password on separate sheets, then other users will not be able to enter and edit data on protected sheets.

But what if you want other people to be able to work normally with Excel workbook and all the pages that are in it, but at the same time you need to limit or even prohibit editing data in individual cells. This is exactly what this article will discuss.

Protecting the selected range from modification

First, let's figure out how to protect the selected range from changes.

Cell protection can only be done if you enable protection for the entire sheet. By default, in Excel, when you enable sheet protection, all cells located on it are automatically protected. Our task is to indicate not everything, but the range that is needed at the moment.

If you need another user to be able to edit the entire page, except for individual blocks, select all of them on the sheet. To do this, click on the triangle in the left top corner. Then right-click on any of them and select Format Cells from the menu.

In the next dialog box, go to the “Protection” tab and uncheck the box "Protected cell". Click OK.

Now, even if we protect this sheet, the ability to enter and change any information in blocks will remain.

After that, we will set restrictions for changes. For example, let's disable editing of blocks that are in the range B2:D7. Select the specified range, right-click on it and select “Format Cells” from the menu. Next, go to the “Protection” tab and check the “Protected...” box. Click OK.

The next step is to enable protection for this sheet. Go to the tab "Review" and click the "Protect Sheet" button. Enter the password and check the boxes for what users can do with it. Click OK and confirm your password.

After this, any user will be able to work with the information on the page. In the example, fives are entered in E4. But when you try to change text or numbers in the range B2:D7, a message appears that the cells are protected.

Set a password

Now let’s assume that you yourself often work with this sheet in Excel and periodically need to change the data in protected blocks. To do this, you will have to constantly remove the protection from the page, and then put it back. Agree that this is not very convenient.

Therefore, let's look at the option of how you can set a password for individual cells in Excel. In this case, you will be able to edit them by simply entering the requested password.

Let's make it so that other users can edit everything on the sheet except the range B2:D7. And you, knowing the password, could edit blocks in B2:D7.

So, select the entire sheet, right-click on any of the blocks and select “Format Cells” from the menu. Next, on the “Protection” tab, uncheck the “Protected...” field.

Now you need to select the range for which the password will be set, in the example it is B2:D7. Then go to “Cell Format” again and check the “Protectable...” box.

If there is no need for other users to edit the data in the cells on this sheet, then skip this step.

Then go to the tab "Review" and press the button "Allow changing ranges". The corresponding dialog box will open. Click the “Create” button in it.

The name of the range and the cells it contains are already specified, so simply enter Password, confirm it, and click OK.

We return to the previous window. Click “Apply” and “OK” in it. This way, you can create multiple ranges protected with different passwords.

Now you need to set a password for the sheet. On the tab "Review" Click the “Protect Sheet” button. Enter your password and check the boxes for what users can do. Click OK and confirm your password.

Let's check how cell protection works. In E5 we introduce sixes. If you try to remove a value from D5, a window will appear asking for a password. By entering the password, you can change the value in the cell.

Thus, knowing the password, you can change the values ​​in protected cells of the Excel sheet.

Protecting blocks from incorrect data

You can also protect a cell in Excel from incorrect data entry. This will come in handy when you need to fill out a questionnaire or form.

For example, a table has a column "Class". There cannot be a number greater than 11 or less than 1, meaning school classes. Let's make the program throw an error if the user enters a number other than 1 to 11 in this column.

Select the desired range of table cells – C3:C7, go to the “Data” tab and click on the button "Data check".

In the next dialog box, on the “Options” tab, in the “Type…” field, select “Integer” from the list. In the “Minimum” field we enter “1”, in the “Maximum” field – “11”.

In the same window on the tab "Message to be entered" Let's enter a message that will be displayed when any cell from this range is selected.

On the tab "Error message" Let's enter a message that will appear if the user tries to enter incorrect information. Click OK.

Now if you select something from the range C3:C7, a hint will be displayed next to it. In the example, when we tried to write “15” in C6, an error message appeared with the text that we entered.

Now you know how to protect cells in Excel from changes and editing by other users, and how to protect cells from incorrect data. In addition, you can set a password, knowing which certain users will still be able to change data in protected blocks.

(1 ratings, average: 5,00 out of 5)

Webmaster. Higher education with a degree in Information Security. Author of most articles and computer literacy lessons

Related Posts

Discussion: 13 comments

Answer