Two ways how to protect cells in Excel from changes. How to protect a cell from changes in Excel How to hide a column in excel and password protect

In this article, I will explain 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 the cell values, as well as see the 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 conditions for restricting access 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 “ Peer review"On the toolbar => in the subsection" Protection"Click on the icon" Protect sheet “:

In the pop-up window, make sure to check the box next to ““:

Enter the password in the ““ field if you want only those users to whom you have provided the password to remove protection:

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

Click “ OK “

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

Now, all users who try to make changes or edit cell values will receive a message like this:

In order to remove the installed protection, go to the “ Peer review “, And in the section“ 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

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

As an example, consider a simple spreadsheet 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 sheet (using the keyboard shortcut CTRL + A ):

Let's go to the “ 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 “ Protection"And uncheck the box" Protected cell “:

Let's click “ OK “

Thus, we turned off the Excel setting for the cells of the entire sheet, in which all cells are ready to be protected and locked.

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 “ 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 to check the box next to “ Protect sheet and contents of protected cells “:

Let's enter the password in the “ Password to disable sheet protection “So that only those users to whom we have given the password could remove protection:

As with 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 then 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 turn on sheet protection in Excel, all cells will be protected. If you select a cell or several, and enter the format of cells, then you can on the tab "Protection" see the checked box "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 cleared. And the previously edited cells can be changed. At the same time, at the moment the protection is turned on, it will be possible to make additional settings and choose what exactly cannot be done with the cells.

Now let's look at the ability to password protect 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, the protection of cells in Excel from editing does not work yet. For everything to work, you need to enable sheet protection, as discussed above. After that, all cells will be protected from changes, but for the cells we have selected, a separate password will be in effect, by entering which without unlocking the sheet it will be possible to make the necessary changes.

Knowing the possibilities of protecting cells in Excel, you can now combine various options. For example, by selecting all the cells of the sheet and going into the cell format, you can unprotect these cells on the protection tab, then select a separate group of cells and restore protection to them. At this stage, when you turn on sheet protection, only the cells we have marked will be blocked. And if you first enter a separate password for the selected cells through the menu for allowing changing ranges, then after turning on the sheet protection, it will be possible to enter any data in all cells, and it will be possible to make changes to locked cells only when you enter the password. Actually, this is the only way to protect individual Excel cells from changes by standard means.

Microsoft Excel implements several levels of protection and security that allow you to control access to MS Excel data: cell protection, sheet protection, workbook protection (structure and windows protection) and protection of the entire file from viewing and modification. Let's consider these protection methods in more detail.

How to protect cells in Excel from being changed?

A file created in Microsoft Excel with a worksheet as its main constituent is called a workbook. Thus, all Excel workbooks are composed of worksheets. A book cannot contain less than one sheet. Worksheets, in turn, are composed of cells organized in vertical columns and horizontal rows. Worksheet cells contain various kinds of information about number formats, alignment, display and direction of text, title, style, font size and color, line type and border color, background color and finally about protection. All this data can be seen if in the context menu, which is invoked by the right mouse button, select the item "Format cells". In the dialog box that appears, on the "Protection" tab, there are two options: "Protected cell" and "Hide formulas". By default, all cells are checked in the Protected box and the Hide Formulas check box is unchecked. A selected checkbox in the "Protected cell" field does not mean that the cell is already protected from changes , it only means that the cell will become protected after the sheet is protected.

You can protect not all cells, but only a part of them, while leaving the opportunity for other users to enter and change data in unprotected cells. In order to make some of the cells unprotected, select the required range of sheet cells and right-click in the area of selected cells, then select the "Format cells ..." context menu item. In the window that appears with the name "Lists" or "Format cells" (depending on the version of Excel) go to the "Protection" tab, and uncheck the "Protected cell" field.

In the same window, you can hide formulas and display them back for viewing. Hiding formulas means not only hiding the formulas themselves, but also displaying any cell contents in the formula bar.

When selectively setting or clearing the "Protected cell" and / or "Hide formulas" properties, when, for example, you need to remove protection from one group or range of cells and leave it for another group or range, it is convenient to use the standard Excel tool to select a group of cells, which is on the Home tab, in the Editing group of buttons, in the Find and Select button menu, the Select Cell Group item. There are also additional convenient tools for installing and removing the protection of cells .

How to set protection of sheet (sheet items) in Excel?

In order to restore full access to the elements, it is necessary to remove the sheet protection. Before displaying hidden rows and columns, you must also first remove sheet protection, then the hidden columns and rows are displayed as usual.

Protecting individual elements of an Excel workbook (structure and windows)

All of the security layers discussed are complementary, but not intended to protect sensitive data.

Attention! If the Protect Sheet and Protect Book buttons are grayed out, meanson the Edit tab, the Allow multiple users to edit the file at the same time check box is selected. In order to uncheck the box, you need to go to the menu item Service / Access to the book ... (if the work is carried out in Excel 2003) or on the "Review" tab, in the group of "Changes" buttons, click the "Access to the book" button (if work is being done in Excel 2007/2010/2013).

Password protect the entire Excel workbook file from being viewed or modified

This method of protecting data in Excel provides optimal security by limiting access to the file and preventing unauthorized opening of the file. The file is protected with a password, the length of which must not exceed 255 characters. Any characters, spaces, numbers and letters can be used, both Russian and English, but passwords with Russian letters are not recognized correctly when using Excel on Macintosh computers. Only users who know the password have access to password-protected books. You can set two separate passwords for opening (viewing) the file and for making changes to the file. Password protection for opening and viewing the file uses encryption. The password for making changes to the file is not encrypted.

There are two ways to set a password to open a file in Excel 2007. In the Office menu / Prepare / Encrypt Document

after clicking the "Encrypt document" button, the "Encrypt document" window appears, in which the password is entered

You can also set a password to open and modify a file when you save the file. To do this, in the "Saving document" window, press the "Service" button and select the "General parameters ..."

after that, a window with the name "General parameters" will appear, in which you can separately enter the password to open the file and the password to save the changes made in it.

You can set a password to open a file in Excel 2010 on the "File" tab in the "Info" group on the "Protect Workbook" button menu by selecting "Encrypt with a password"

The password window looks exactly the same as the one in Excel 2007. Just like in Excel 2007, you can enter passwords for opening a file and for making changes to them with separate passwords when saving the file.

When entering a password, it is not displayed on the screen, instead of password characters, dots are displayed. It is recommended to use complex passwords, which consist of a combination of both lowercase and uppercase letters, numbers, and various symbols. Passwords that do not contain a combination of these elements are weak. To avoid accidental mistakes when entering a password, a password confirmation window appears. Excel does not have standard tools for recovering forgotten passwords. To prevent the loss of passwords, it is recommended that you either design them in such a way that you can remember them, or keep the recorded passwords in a safe place.

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

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

Protection of the selected range from changes

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

Cell protection can be done only if you enable protection for the entire sheet as a whole. By default, Excel, when you turn on sheet protection, automatically protects all cells that are located on it. 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, you need to click on the triangle in the upper left corner. Then click on any of them with the right mouse button 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 prohibit editing blocks that are in the range B2: D7. Select the specified range, right-click on it and select "Format Cells" from the menu. Then go to the "Protection" tab and put a tick in the "Protected ..." field. Click OK.

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

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

We put a password

Now suppose that you yourself often work with this sheet in Excel and periodically you 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 put a password for individual cells in Excel. In this case, you can edit them by simply entering the requested password.

Let's make it so that other users can edit everything on the sheet, except for the range B2: D7. And you, knowing the password, could edit the 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 again go to "Format cells" and put a tick in the "Protected ..." field.

If you do not need 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 that it contains are already specified, so just enter the "Password", confirm it and click "OK".

We return to the previous window. Click "Apply" and "OK" in it. Thus, it is possible to create several ranges protected by different passwords.

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

Checking out how cell protection works. Introduce sixes in E5. If you try to remove a value from D5, a window asking for a password will appear. By entering the password, you can change the value in the cell.

Thus, knowing the password, you can change the values in the protected cell of the Excel worksheet.

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 some kind of questionnaire or form.

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

Select the required range of table cells - C3: C7, go to the "Data" tab and click on the button "Data validation".

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

In the same window on the tab "Message to enter" we will enter a message that will be displayed when selecting any cell from this range.

In the tab "Error message" we will 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 I 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 entry. In addition, you can set a password, knowing which certain users will still be able to change data in protected blocks.

Rate article:

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

Webmaster. Higher education in the specialty "Information Security" .. Author of most articles and lessons of computer literacy

Method 1: set a password when saving a file

One way is to set a password directly when you save the Excel workbook.

This way we have protected the Excel file. Now, to open and edit it, you will need to enter the appropriate passwords.

Method 2: set a password in the "Details" section

The second method involves setting a password in the Excel section "Intelligence".

Method 3: Set a password and remove it in the "Review" tab

The ability to set a password also exists in the tab "Review".

As you can see, Microsoft Excel offers several ways to protect a file with a password, both from deliberate hacking and from unintentional actions. You can use a password to open a book or edit or change its individual structural elements. At the same time, the author himself can determine from which changes he wants to protect the document.