Security and data protection are one of the main directions of development of modern information technologies... The urgency of this problem is not decreasing, but only growing. Data protection is especially important for table files, which often store important information commercial nature. Let's find out how to protect Excel files with a password.
The developers of the program perfectly understood the importance of the ability to set a password specifically for Excel files, so they implemented several options for performing this procedure at once. At the same time, it is possible to set the key, both for opening the book, and for changing it.
Method 1: set a password when saving a file
One of the ways is to set a password directly when saving Excel workbooks.
Thus, 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: Setting a password and removing it in the "Review" tab
The ability to set a password also exists in the tab "Review".
As you can see, the program 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.
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, 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, you need to click on the triangle in the left upper 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 the 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 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 checking".
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 for input" 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
Similar posts
Discussion: 13 comments
To answer
From time to time during work, it becomes necessary to set or remove a password for the Excel file. In this article, we will look at how this can be done in various Excel versions.
If you need to set a password in Excel, then you can do this in a special dialog box. The location of this window in different versions Excel is different.
The password must be set after all changes in 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 the fileExcel or password to change it in the appropriate 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 in order for you to be sure that you entered the password correctly the first time and remember it. Changes will take place after saving the file.
You can set or remove a password in Excel using the method described below.
Set or remove password in Excel 2007 and 2010
Setting a password in Excel 2010 is a little different from 2003, and now we will 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
This article will talk about 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, the protection of cells is good way save yourself from yourself. By protecting the cells in which the formulas are entered, you inadvertently will not delete them.
Selecting the required range of cells
Now the first method will be provided on how to protect cells in Excel from changes. He, of course, is not much different from the second, which will be described later, but you cannot skip it.
So, in order to protect table cells from corrections, you need to do the following:
Select the entire table. The easiest way to do this is by clicking on a special button that is located at the intersection of the vertical (row numbering) and horizontal (column designation). However, you can also use hotkeys by pressing CTRL + A.
Press the right mouse button (RMB).
In the menu, select the item "Format cells".
In the window that appears, go to the "Protection" tab.
Uncheck the box "Protected cell".
Click OK.
So, we have just removed the ability to protect all cells in the table. This is necessary in order to designate only a range or one cell. For this you need:
Select the required cells using the usual stretching with the left mouse button pressed (LMB).
Press RMB.
In the menu again select "Format cells".
Go to "Protection".
Check the box next to "Protected cell".
Click on OK.
We put protection on the selected cells
We told the program exactly which cells we want to protect from changes. But this is not enough for them to be protected. To accomplish this goal, you need to enable sheet protection in Excel. For this:
Click on the "File" tab.
In the menu, go to the "Information" section.
Click on the "Protect Book" icon.
Select "Protect Current Sheet" from the menu.
A window will appear in which you need to configure. Follow the guide:
Do not under any circumstances uncheck the "Protect sheet and contents of protected cells" item.
In the window located just below, you can make more flexible settings, but by default it is set so that no one can modify the parameters.
Enter your password in the appropriate field. It can be of any length, but remember that the harder and longer it is, the more reliable it is.
Click OK.
After the performed manipulations, you will be asked to enter the password again for all changes to take effect. Now you know the first way to protect a cell in Excel from changes.
Second way
The second way to protect a cell in Excel from changes, as mentioned above, is not much different from the first. Here is a detailed instruction.
Just like last time, remove the protection of cells from the entire table and put it in the desired area.
Go to "Review".
Click on the "Protect Sheet" button in the "Changes" toolbar.
After that, the already familiar window will appear in which you need to set the protection parameters. Enter the password in the same way, select the required options, check the box next to "Protect sheet and contents of protected cells" and click OK.
For various reasons, there are situations where Excel users want to protect cells in their Excel spreadsheets. Someone wants to protect all cells without exception, while someone needs to set selective protection exclusively on cells with filling or with formulas.
Someone wants to protect data from himself, so as not to accidentally delete something important, and someone needs to protect cells from others, for example, not very experienced users who can replace the formula with an intermediate calculation in the cell with their value and put thus questioning the final result. At the same time, finding such an error will be quite problematic.
Installing and removing protection of cells using standard Excel tools
Cell protection is performed in two stages, in order to protect cells from changes, it is necessary:
Stage one - 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 item "Format cells ..." from the context menu, go to the "Protection" tab and check the boxes in the "Protected cell" and / or "Hide Formulas".
The presence of checked boxes does not protect the cells, but only says 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 non-contiguous cells. By default, in every new workbook and every new worksheet, all cells are securable and all cells do not have a checkbox in the Hide Formulas box.
Stage two - to establish the protection of the sheet.
Worksheet protection in different versions of Excel is dedicated to separate publication, here it should be recalled that you can set the protection of a sheet in Microsoft Excel 2003 in the menu Tools / Protection / Protect a sheet, and in Excel 2007/2010/2013 the sheet is protected on the "Review" tab, in the group of "Changes" buttons by clicking the "Protect sheet". 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, open the macro dialog box directly from the Excel toolbar;
2. choose at your discretion one of the four data processing modes;
3. at the user's choice, set or uncheck the "Protected cell" and "Hide formulas" property according to seven conditions (for empty cells, for cells with numerical and text values, with formulas, with notes, with fills and frames).
video on installing / removing protection of cells
macro (add-on) for custom setting and unsetting the "protected cell" property
