Setting a password for files in Microsoft Excel. Protecting cells in Excel from changing, editing and entering erroneous data Protect cells with a warning

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

How to protect cells in Excel from being changed?

A file created in Microsoft Excel whose main component is a worksheet is called a workbook. Thus, all Excel workbooks consist of worksheets. A book cannot contain less than one sheet. Worksheets, in turn, are made up of cells organized into vertical columns and horizontal rows. Worksheet cells contain various types of information about number formats, alignment, display and direction of text, font name, style, size and color, line type and border color, background color and finally security. All this data can be seen if you select “Format Cells” in the context menu, which is called up with the right mouse button. 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 selected checkbox in the "Protected cell" field does not mean that the cell is already protected from changes , this only means that the cell will become protected after the sheet protection is set.

You can protect not all cells, but only part of them, while leaving the ability for other users to enter and change data in unprotected cells. In order to make some cells unprotected, you need 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 titled “Lists” or “Cell Format” (depending on the version of Excel), go to the “Protection” tab and uncheck the “Protected cell” box.

In the same window, you can hide formulas and show 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 removing 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 for selecting a group of cells that is located on the "Home" tab, in the "Editing" button group, in the "Find and Select" button menu, the "Select group of cells" item. There are also additional convenient tools for installing and removing cell protection .

How to protect a sheet (sheet elements) in Excel?

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

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

All security levels discussed are complementary, but are not intended to protect confidential data.

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

Password protect the entire Excel workbook file from viewing and making changes

This method of protecting data in Excel provides optimal security by limiting access to the file and eliminating the possibility of unauthorized opening of the file. The file is protected with a password, the length of which should not exceed 255 characters. Any symbols, 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 for opening (viewing) a file and for making changes to the file. Password protection for opening and viewing a file uses encryption. The password for making changes to the file is not encrypted.

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

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

A password for opening and changing a file can also be set when saving the file. To do this, in the “Saving Document” window, you need to click the “Tools” button and select “General parameters...”

After this, a window called “General Settings” 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 for opening a file in Excel 2010 on the “File” tab in the “Information” group in the “Protect Workbook” button menu by selecting “Encrypt with password”

The window for entering a password looks absolutely similar to the same window 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; 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 such 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 loss of passwords, it is recommended that you either write them in a way that you can remember or keep written down passwords in a safe place.

For various reasons, situations arise when Excel users want to protect cells in their Excel spreadsheets. Some people want to protect all cells without exception, while others need to install selective protection exclusively on cells with fill or formulas.

Someone wants to protect data from themselves, 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 a formula with an intermediate calculation in a cell with their own value and put thereby calling into question the final result. However, finding such an error will be quite problematic.

Installing and removing cell protection using standard Excel tools

Cell protection is carried out in two stages; in order to protect cells from changes, it is necessary:

Stage one is to select cells and make them protectable;

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

The presence of selected checkboxes does not protect the cells, but only indicates that the cells will become protected after setting the 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 protected and all cells do not have the Hide Formulas checkbox selected.

Stage two is to install sheet protection.

Protecting a sheet in different versions of Excel is devoted to separate publication, here it should be recalled that you can install worksheet protection in Microsoft Excel 2003 in the Tools/Protection/Protect Sheet menu, and in Excel 2007/2010/2013 the worksheet is protected on the “Review” tab, in the “Changes” button group by clicking the “Protect” button 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, call the macro dialog box directly from the Excel toolbar;

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

3. at the user's discretion, 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 comments, with fills and frames).

video on installing/removing cell protection

macro (add-in) for selectively setting and removing the “protected cell” property

The issue of data protection is particularly relevant today. The network is full of attackers who can easily take over unprotected information. In addition, there are often cases when the user simply wants to install protection from prying eyes, for example, if several people use the same computer. In general, now we will not find out the motive for such a desire, but rather talk about how to password protect an excel file. In this article, Excel versions 2007 and 2010 were targeted, because the principle of setting a password for them will be identical. So, for everyone who wants to install protection on an Excel document, I suggest using one of two methods. Which ones? Find out further!

Method one

In fact, both methods are extremely simple and equally functional, so it doesn’t matter which one you choose. It's also simple. So, let's start with the algorithm for performing the first method:

Let's imagine that the required document is ready, all that remains is to password protect it. To do this, click the large “Office” button in the upper left corner of the program.
In the context menu that opens, click “Save As”.
A new window will open, your task is to pay attention to the small “Service” button located next to “Save” and “Cancel”. Click on it and a small menu will appear, select the line “General settings”.
Next, you can set passwords for two cases: for opening a document and changing it. Enter the desired code and click “OK”. From now on, every time a document is opened, each user will be prompted to enter the set password. It’s better to write down your passwords somewhere, otherwise, if you forget, you’ll have to rack your brains.

Method two

In addition to the above, I can offer one more option for setting a password on an Excel document.

By following these simple rules, you can install protection on any Excel file in no time. I note that this feature is based on 128-bit AES encryption, which is considered the standard method of file protection.

Video to help

Hello my friends.

You don't want anyone to make changes to the table you wrote? There is a solution - protecting cells from editing in Excel. Moreover, the developers offer different degrees of it: from blocking individual cells to the entire book. This article will show you how to use these options.

How to secure individual cells?

Excel 2007 and later versions allow you to set a password on a file so that only you can open it. For example, you want to allow other users to freely open and view a book. But you don't want them to make any changes to the total cells or to a specific column.

In such cases, the actions for excel 2010 and any other will be the same:

Thus, protection against changes is installed only for individual cells.

But that's not all.

Next stage

Now we will password-protect the sheet so that only you can make adjustments in the future:

Are the cells that need to be blocked located at a distance from each other? Excel 2016 and versions up to 2007 inclusive allow you to select them not manually, but automatically.
To do this, in the “Home” tab, in one of the very last fields called “Editing”, click the “Find and Select” button. Select "Select a group of cells..."

And set the necessary settings:

That's all.

Now your documents will be completely safe.

Visit me often to find out even more useful information.

Goodbye friends!

To prevent others from intentionally or accidentally changing, moving, or deleting data in a worksheet, you can lock cells in an Excel worksheet and then protect it with a password. Let's say you have a team report sheet and you want team members to be able to add data only to certain cells and not to change other elements. By protecting a worksheet, you can make only certain parts of the worksheet editable, preventing users from changing data in other areas of the worksheet.

Important:

Sheet protection Not is a safety feature. It simply prevents changing locked cells on the worksheet.

Protecting a worksheet is different from protecting an Excel file or workbook with a password. See below for more information.

For information about how to lock a file to prevent others from opening it, see Protect an Excel file.

To prevent sheets from being added, edited, moved, copied, or hidden or shown in a workbook, see Protect a workbook.

Selecting Sheet Elements to Lock

Here are the elements you can lock on an unprotected sheet:

Formulas: If you don't want other users to see your formulas, you can hide them from cells or the formula bar. For more information, see Showing and hiding formulas.

Ranges: You can allow users to work within specific ranges on a protected worksheet. For more information, see Lock or unlock specific areas of a protected worksheet.

Note: ActiveX controls, form controls, shapes, charts, SmartArt graphics, sparklines, slicers, timelines, and some other elements are locked as soon as they are added to a table. However, the lock will only work if sheet protection is enabled. For more information about how to enable protection, see the next section.

Enabling sheet protection

Protecting a worksheet is a two-step process: first you need to unlock the cells that users need to change, and then you need to protect the worksheet with or without a password.

Step 1: Unlock all cells that need to be changed

Step 2. Protect the sheet

Decide what actions you want users to perform on the worksheet (for example, inserting or deleting columns or rows, changing objects, sorting, or using an AutoFilter). Additionally, you can also provide a password to lock the sheet. The password will not allow other users to remove the protection from the sheet - you will need to enter it to disable it.

Below are instructions for protecting the sheet.

On the tab Review click the button Protect sheet.

On the list Allow all users of this sheet Select the check boxes for items that you want users to be able to change.

Parameter	Opportunity
highlighting locked cells	Move the pointer to cells that have a checkbox selected Protected cell on the tab Protection in the dialog box Cell Format. By default, users are allowed to highlight protected cells.
highlighting unlocked cells	Move the pointer to unchecked cells Protected cell on the tab Protection in the dialog box Cell Format. By default, users are allowed to highlight unprotected cells and also move between unprotected cells in a protected worksheet by using the TAB key.
cell formatting	Changing settings in dialog boxes Cell Format or Conditional Formatting. If conditional formatting was applied before you set the worksheet to be protected, the formatting will change when you enter a value that meets the specific condition.
column formatting	Use any column formatting commands, including changing column width or hiding columns (the Home, group Cells, button Format).
string formatting	Use any line formatting commands, including changing line height or hiding lines (the Home, group Cells, button Format).
inserting columns	Inserting columns.
inserting rows	Inserting rows.
inserting hyperlinks	Insert new hyperlinks (even in unlocked cells).
deleting columns	Removing columns. Note: If the element " remove columns"protected and Inserting Columns is not protected, the user can insert columns but cannot delete them.
deleting rows	Removing rows. Note: If line "delete lines"protected and Inserting Rows is not protected, the user can insert rows, but cannot delete them.
sorting	Using commands to sort data (tab Data, group Sorting and Filter). Note: Users will not be able to sort ranges that contain locked cells on a protected worksheet, regardless of how this setting is configured.
using autofilter	Use drop-down arrows to change the filter on ranges when using AutoFilters. Note: Users will not be able to apply or remove AutoFilters on a protected worksheet, regardless of how this setting is configured.
using PivotTable reports	Formatting, changing the layout, updating data, or otherwise modifying PivotTable reports, and creating reports.
changing objects	Do the following: Make changes to graphics, including maps, embedded charts, shapes, text boxes, and controls that were not unlocked before you secured the worksheet. For example, if there is a button on a worksheet that runs a macro, it can be clicked to run the macro, but cannot be removed. Make any changes (such as formatting) to the embedded chart. The chart will still update as its source data changes. Adding and editing notes.
changing scenarios	View hidden scripts, modify locked scripts, and delete these scripts. Users can change values in editable cells if the cells are not protected and add new scripts.

If desired, you can enter a password in the field Password to disable sheet protection and press the button OK. In the dialog box Password confirmation Enter your password again and press OK.

Important:

Use strong passwords that contain uppercase and lowercase letters, numbers, and symbols. Weak passwords do not use all of these elements, or they are not mixed. An example of a weak password: House27. A passphrase that uses 14 or more characters is better.

It is very important to remember your password. If you forget your password, Microsoft cannot reset it.

How to determine if a sheet is protected?

If the sheet is protected, the command Protect sheet on the tape changes to Remove sheet protection. Team Remove sheet protection can be found on the tab Review in the group Changes.

Unprotect an Excel worksheet

To remove sheet protection, do the following:

Select the sheet you want to unprotect.