You can put a password on an Excel spreadsheet, thereby preventing access for other users. This can come in handy at home or at work. But in this way, it is impossible to seriously protect the table. Because Excel uses a very weak form of encryption, it is very easy to access the file. Let's try to figure it out in more detail.
When you need to close access to an Excel file, at home or at work, from other employees, just put a password on it. The password is set quite simply, right in the Excel file. But, as mentioned above, it is very easy to access a protected Excel file. A few minutes is usually enough to crack the Excel file.
However, later versions of Excel have more sophisticated encryption methods. Plus a complex and long password. Together, this will better protect the table. How older version Excel, the easier it is to crack the password. Therefore, you must use a version not lower than Excel 2010, better - 2013 and later.
Also with a password, the more complex it is, the better. ? - in this topic, we discussed in detail what to rely on when creating a complex and secure password... Another topic that can be useful when creating protection is? Rely on the tips given in the articles on the links above and you will have a really good password, and in the same place, protection.
Put password on Excel file
And so, let's get down to business. Put password on Excel file you can in the program itself Microsoft Excel.
There are two ways to protect a file: the first is to put a password for the entire workbook (the entire Excel file), the second is to protect a specific sheet (tab).
Password protect sheet in Excel
All password to Excel sheet is set, now when you open this Excel worksheet will ask for a password.
Protect entire workbook in Excel with a password
Now let's set a password for the whole Excel file.
The entire Excel workbook is now password protected.
Next time you start protected Excel file, you will be prompted for a password, without which you cannot edit anything and even see the contents of the table.
I keep repeating that you need to use strong passwords. Even in this case, if you want to get a protected Excel file, you need not just 12345678, qwerty123, the date of birth or the name of your beloved dog, but something more "hard", such as: f0P! LgkTr & - do not try to decipher, this is just a set of characters: )
And of course, I will repeat once again: it is impossible to reliably protect information in an Excel file. But, at least using more new version Microsoft Excel and a good, strong password will help against accidental hacking, like this.
In the following articles, we will look at how you can get access to a protected Excel file and you will see for yourself that to save confidential information it is not at all enough to put a password in Microsoft Excel.
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, but someone needs to protect cells from others, for example, not very experienced users who can replace the formula with an intermediate calculation in a 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-adjacent 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.
Sheet protection in various Excel versions devoted to separate publication, here it should be recalled that you can set the protection of a sheet in Microsoft Excel 2003 in the menu Service / 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 clear the "Protected cell" and "Hide formulas" property 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 protection of cells
macro (add-on) for custom setting and unsetting the "protected cell" property
Did you know that the editor spreadsheets Does Microsoft Excel allow you to set a password for changing both a whole workbook and a separate sheet? This is very convenient if you need to protect content from users, prevent them from making changes to the table, or even block access to it. This is especially true in some companies, where a single file-trash can is organized on the server, which is available to everyone and where all users dump their documents. By password-protecting the entire XLS or XLSX file from unauthorized access. At the same time, for those who really need access, you can issue a password on an individual basis.
Let's take a closer look at how to password protect data in Excel.
Setting a password on an Excel sheet
At the bottom of the window, find a list of sheets and click on the desired one with the right mouse button to bring up a menu where you need to select the "Protect sheet" item:
A separate window "Protection of the sheet" will appear, in which you must specify a password, as well as use the checkboxes to set the rights that will be available to users:
After you enter the password, another window will appear in which you must enter it again to confirm:
Now, when trying to change the contents of a protected sheet, the user will receive the following message:
And if he tries to remove the protection of the sheet, then Excel will require him to enter the password:
Note: You can also call the "Protect Sheet" window through the main menu in the "Review" section. See the video for more details:
How to put a password on an Excel workbook
Now let's look at how to set up password protection for a workbook in Excel. In the main window of the program and call the menu "File". Such a menu will appear, in which you need to open the "Information" section:
The first item is "Book protection". Click on it and see this menu:
We select the item "Encrypt using a password". Excel will ask you to enter a password:
Enter and click "OK". After that, you will see a message that a password is required to open this book.
Excel file password
This method of data protection implies that you want to set a password for the entire XLS or XLSX file so that an unauthorized person cannot open it at all, and if you try to do this, you will be prompted to enter a security key:
After you click the "OK" button, a password confirmation window will pop up twice - first to open XLS or XLSX file, and then - to change.
After that, you can be sure that your data is well protected.
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.
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 Excel workbook and all the pages that are in it, but at the same time it is necessary 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 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 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 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 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
Similar posts
Discussion: 13 comments
To answer
