You can protect information in an Excel workbook in a variety of ways. Set a password for the entire book, then it will be requested every time it is opened. 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 the Excel workbook and all the pages that are in it, but you need to limit or even prohibit editing data in individual cells. This is what will be discussed in this article.

Protection of the selected range from changes

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

You can only protect cells if you turn on protection for the entire sheet. By default, in Excel, when you turn on sheet protection, all cells that are 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 upper left 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 put restrictions on the changes. For example, let's disable editing blocks that are in the range B2:D7 . Select the specified range, right-click on it and select from the menu "Format Cells". Then go to the "Protection" tab and check the box "Protected ...". Click OK.

The next step is to enable protection for this sheet. Go to the tab "Review" and click the Protect Sheet button . Enter a 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. In the example, fives are entered in E4. But when you try to change the 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 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 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 from the menu "Format Cells". Further on the "Protection" tab, uncheck the box "Protected ...".

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 check the box "Protected ...".

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

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

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

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

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

Protecting blocks from invalid 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, the table has a column "Class". There cannot be a number greater than 11 and less than 1, meaning school classes. Let's make the program throw an error if the user enters a number not between 1 and 11 in this column.

Select the desired 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 from the list "Integer". In the field "Minimum" enter "1", in the field "Maximum" - "11".

In the same window on the tab "Input Message" enter a message that will be displayed when any cell from the given range is selected.

On 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 C3:C7 range, a tooltip will be displayed next to it. In the example, when trying to write in C6 "15", an error message appeared, with the text that we entered.

Now you know how to protect cells in Excel from being changed and edited 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 ratings, average: 5,00 out of 5)

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

Similar posts

Discussion: 13 comments

Reply

For various reasons, situations arise when Excel users want to protect cells in their Excel spreadsheets. Someone wants to protect all cells without exception, while someone needs to install selective protection exclusively on cells with a fill or formulas.

Someone wants to protect data from himself so as not to accidentally delete something important, but someone needs to protect cells from others, for example, not very experienced users who can replace the formula in the cell with an intermediate calculation for their value and put thereby calling into question the final result. At the same time, finding such an error will be quite problematic.

Setting and removing cell protection using standard Excel tools

Cell protection is performed in two stages, in order to protect cells from changes, you must:

The first step is to select cells and make them protected;

To do this, after selecting the cells, press the key combination Ctrl + 1 or right-click in the area of ​​​​selected cells, select the "Format Cells ..." item from the context menu, go to the "Protection" tab and check the boxes "Protected cell" and / or "Hide formulas".

The presence of checked boxes does not protect the cells, but only indicates that the cells will become protected after setting sheet protection. You can change the format of cells both in the entire selected range and in a group of nonadjacent cells. By default, in every new workbook and every new worksheet, all cells are protected, and all cells do not have the "Hide formulas" check box checked.

Stage two - set the sheet protection.

Sheet protection in various versions Excel dedicated separate publication, here it should be recalled that to set sheet protection in Microsoft Excel 2003, you can use the Tools / Protect / Protect Sheet menu, and in Excel 2007/2010/2013, the sheet is protected on the Review tab, in the Changes button group by clicking the Protect Sheet button. If necessary, you can check the permissions for users and enter a password.

Using the add-on allows you to:

1. With one click of the mouse, call the macro dialog box directly from the Excel toolbar;

2. choose one of the four data processing modes at your discretion;

3. At the user's choice, set or remove the "Protected cell" and "Hide formulas" properties according to seven conditions (for empty cells, for cells with numeric and text values, with formulas, with notes, with fills and frames).

video on installing / removing cell protection

macro (add-in) for selective setting and removal of the "protected cell" property

Microsoft Excel has several levels of protection and security that allow you to control access to MS Excel data: cell protection, sheet protection, workbook protection (structure and window protection), and protection of the entire file from viewing and modification. Let's take a closer look at these protections.

How to protect cells in Excel from being changed?

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

You can protect not all cells, but only a part of them, while leaving the possibility for other users to enter and change data in unprotected cells. In order to make a part of the cells not protected, it is necessary to select the desired range of sheet cells and right-click in the area of ​​the selected cells, then select the “Format Cells…” context menu item. In the window that appears with the name "Lists" or "Cell Format" (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 you selectively set or remove 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 located on the "Home" tab, in the "Editing" button group, in the "Find and select" button menu, "Select a group of cells" item. There are also additional handy tools for installing and removing cell protection .

How to set sheet (sheet elements) protection in Excel?

In order to restore full access to the elements, you must unprotect the sheet. Before displaying hidden rows and columns, you must also first remove sheet protection, after which the hidden columns and rows are displayed in the normal order.

Protecting Individual Excel Workbook Elements (Outlines and Windows)

All of the levels of protection discussed are complementary, but are not intended to protect sensitive data.

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

Password protection of the entire Excel workbook file from viewing and modification

This method of data protection in Excel provides optimal security by restricting access to the file and preventing unauthorized opening of the file. The file is protected with a password, the length of which should not exceed 255 characters. Any characters, spaces, numbers and letters, both Russian and English, can be used, but passwords with Russian letters are not correctly recognized when using Excel on Macintosh computers. Password-protected books can only be accessed by users who know the password. You can set two separate passwords to open (view) the file and to make changes to the file. Password protection to open and view 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 "Document Encryption" 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 "Save Document" window, click the "Tools" button and select the "General Options ..." item.

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

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

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

When entering a password, it is not displayed on the screen, dots are displayed instead of password symbols. It is recommended to use complex passwords that 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 errors when entering a password, a password confirmation window appears. Not in Excel standard means recovery forgotten passwords. To prevent losing passwords, it is recommended that you either create them in such a way that you can remember them, or keep written passwords in a safe place.

Periodically during operation, it becomes necessary to set or remove a password on excel file. In this article, we will look at how this can be done in different versions of Excel.

If you need to set a password in Excel, then you can do it in a special dialog box. The location of this window varies in different versions of Excel.

The password must be set after all changes to the file before saving it. To do this, go to the section Service→ Options

This will open the Excel workbook options dialog box, you need to select the tab Security where you can enter either password to open fileexcel, or password to change it in the corresponding field

After entering the password, you must click OK, and a window will open in which you need to confirm the password you entered. This is necessary so that you can be sure that you entered the password correctly the first time and remember it. Changes will take effect after saving the file.

You can also set or remove a password in Excel using the method discussed below.

Set or remove password in Excel 2007 and 2010

Setting a password in Excel 2010 is slightly different from 2003, and now we'll look at how you can do it. You can set a password just before saving the file. To do this, select the dialog box for saving the file: File→ Save as

After that, a window will open where you need to select the item Service→ Common parameters

Graphs and charts (5)
Working with VB project (12)
Conditional formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (64)
Miscellaneous (41)
Excel bugs and glitches (4)

How to allow only selected cells to be modified?

For data on a sheet from changes in Excel, there is such a command as . You can find it:

• in Excel 2003 - Service-Protection-Protect Sheet
• in Excel 2007-2013- tab Review-Protect sheet

But when this command is executed, ALL cells of the sheet are protected. But there are situations when it is necessary to protect all cells except A1, C2 and D3, so that changes can only be made in these cells, and the values ​​​​of the rest could not be changed. This is very much in demand in various types of fillable templates, in which you can only fill in certain cells, and prohibit all others from editing. To do this is quite simple. Select the cells that need to be allowed to change (A1, C2 and D3); then ctrl+1(or right mouse button- Format cells)-tab Protection. Uncheck the box Protected cell (Locked). Now we set the protection on the sheet.

If you need to do the opposite - protect only a few cells, and leave the rest to be able to change them, then the sequence will be slightly different:

After that, install protection on the sheet (as see at the very beginning of the article) and voila! You can only change those cells for which the checkbox is unchecked "Protected cell" (Locked).
At the same time, if, when protecting the sheet, uncheck the item selection of locked cells (Select locked cells)- it will be possible to select only those cells that are allowed for editing. Also, moving through cells with arrows, TAB, and after pressing Enter will occur exclusively in unprotected cells. This can be useful so that the user does not have to guess which cells can and cannot be changed.

Also on the tab. Protection there is an item Hide formulas (Hidden). If you set it together with setting the Protected cell attribute, then after setting protection in protected cells it will not be possible to see the formulas - only the results of their calculations. It is useful if you want to leave the ability to enter some parameters, and leave calculations with formulas "behind the scenes".

Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))