When working with Excel spreadsheets, sometimes you need to prevent a cell from being edited. This is especially true for ranges that contain formulas or are referenced by other cells. After all, incorrect changes made to them can destroy the entire structure of calculations. It is simply necessary to protect data in especially valuable tables on a computer that other people besides you have access to. The rash actions of an unauthorized user can ruin all the fruits of your work if some data is not well protected. Let's take a look at exactly how this can be done.

There is no special tool in Excel designed to lock individual cells, but this procedure can be done by protecting the entire sheet.

Method 1: Enable Locking via the File Tab

In order to protect a cell or range, you need to perform the actions described below.

Now those ranges that we previously selected and set their protection in the formatting settings will be unavailable for editing. In other areas, you can perform any actions and save the results.

Method 2: Enable blocking through the Review tab

There is another way to block a range from unwanted changes. However, this option differs from the previous method only in that it is performed through a different tab.

Unlock range

When you click on any area of ​​the locked range or try to change its contents, a message will appear indicating that the cell is protected from changes. If you know the password and consciously want to edit the data, then you will need to do some steps to unlock it.

This article will discuss how to protect a cell in Excel from changes. Fortunately, this option is available in this spreadsheet editor present. And you can easily protect all the data you enter from someone else’s interference. Cell protection is also good way save yourself from yourself. By protecting the cells in which formulas are entered, you will not accidentally delete them.

Select the required range of cells

Now the first method will be provided on how to protect cells in Excel from changes. It is, of course, not much different from the second one, which will be described later, but it cannot be missed.

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 special button, which 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).

Select "Format Cells" from the menu.

In the window that appears, go to the "Protection" tab.

Uncheck the "Protected cell" checkbox.

Click OK.

So, we've just removed the ability to protect all cells in a table. This is necessary in order to designate only a range or one cell. To do this you need:

Select the required cells using normal stretching while holding down the left mouse button (LMB).

Press RMB.

Select "Format Cells" from the menu again.

Go to "Protection".

Check the box next to "Protected cell".

Click OK.

We put protection on selected cells

We indicated to the program which cells we want to protect from changes. But this is not enough for them to become protected. To achieve 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.

From the menu, select Protect Current Sheet.

A window will appear in which you need to make settings. Follow the guide:

Never uncheck the “Protect sheet and contents of protected cells” checkbox.

In the window located just below, you can make more flexible settings, but by default it is set so that no one can change the parameters.

Enter your password in the appropriate field. It can be of any length, but remember that the more complex and longer it is, the more reliable it is.

Click OK.

After the manipulations have been completed, you will be asked to re-enter your password 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 are detailed instructions.

Just like last time, remove cell protection from the entire table and place it in the desired area.

Go to "Review".

Click on the "Protect Sheet" button, which is located in the "Changes" tool group.

After this, a familiar window will appear in which you need to set protection parameters. Enter the password in the same way, select the necessary options, check the box next to “Protect the sheet and contents of protected cells” and click OK.

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 wants to install protection simply 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, I was targeted Excel versions 2007 and 2010, because the principle of setting a password 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:

1. 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.
2. In the opened context menu Click "Save As".
3. 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”.
4. 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 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 Excel document.

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

Video to help

Program Microsoft Office Excel is designed to work with information in tables. It is presented in the form of a grid, which is formed by columns and rows. In some cases, the author of a “book” - an Excel document - needs to protect data from changes. You can protect an arbitrary number of cells from entering incorrect data or editing. This is necessary in order to:

• limit the rights of users or groups by setting a password for a specific list (or the entire book);
• protect the elements from changing the cell on their own and losing information.

The article discusses the options available in Excel.

This is how you can prevent interference with Excel workbook from accidental or intentional intervention of one or another user. This will avoid data loss when saving and closing a document, as well as attempts to restore values, which takes time and is not always possible.

Unfortunately, Excel does not have a button that will instantly restrict access to a particular area. Nevertheless, everything can be done using the program’s capabilities without resorting to others. In order to install such protection against entering incorrect data or changing it, use one of the following methods:

1. restriction on entering incorrect information;
2. protecting a selected quantity or a specific group of values ​​from editing;
3. setting different rights for different users or groups;
4. restricting rights to one or more pages of an Excel workbook.

You can also password-protect security features, making changes unavailable to people who don't know the access code. For example, this allows you to split a book into areas with one person working in each area. Combining various functions, you can achieve the desired type of editing rights restriction. The change restriction can also be canceled (if a password has been set, you will need to enter it).

The setting can be made for various types protected data.

This method is the easiest to use. With it, you can control what you (or another user) puts into the cell. You can filter out data that does not meet certain criteria, which can also be specified. This way, you can prevent entering a negative price, a fractional number of people, or the wrong date for a certain event. In order to install protection, you need:

1. Select the elements to which the function will be applied.
2. Go to the “Data” tab in Excel, then in the “Working with Data” area, click “Data Validation”.
3. A window will open - in “Parameters”, specify the type of data you want to protect cells from entering.
4. You can also set up notifications that Excel will display before editing and if you enter incorrect data. This is done in the “Input Message” and “Error Message” tabs, respectively.
5. Then you need to save the settings by clicking “OK” and they will take effect.

Adding automatic notifications for working with cells.

Restriction from editing

Using this function, you can clearly indicate specific cells or groups that need to be protected from modification. This can be done for:

• All elements work area- press Ctrl + “A”;
• Specific - select them manually, using Ctrl to add a cell to the list and Shift to include several horizontally, vertically or crosswise;
• Certain groups of elements, for example, only cells with formulas, notes or any others.

Limit the entire work area:

1. Select all elements - press Ctrl + “A” or click on the area where the numbering of rows and columns on the table frame intersects (an empty cell without a number or letter).
2. Select “Format Cells”, in the window go to “Protection” and activate the items “Protected cell” (set by default, optionally or instead), “Hide formulas”.
3. After this, you need to activate the saved page function.

By selecting the entire table or part of it, we restrict access.

Restriction of rights to manually selected cells:

1. Select elements that do not need to be protected by clicking on them, holding Shift (to include in the group list) and Ctrl (to include a specific, including non-adjacent cell).
2. Right-click on the highlighted area to open the context menu.
3. Select “Format Cells”, in the window go to “Protection” and uncheck “Protected cell” - all other selected elements will be limited in editing when sheet protection is activated.
4. Click "OK" to apply the settings.
5. After this, you need to activate the option.

Restricting access to certain groups of cells is necessary if you need to restrict editing of one or more groups with specific content properties:

1. Specify items belonging to one or more groups that need to be protected. To do this, follow the path “Home” - “Editing” - “Find and Select” - “Selecting a group of cells”.
2. Specify the required options and click OK for the changes to take effect.
3. After this, you need to activate the protection function.

An example of selecting content with specific properties.

How to set worksheet protection in Excel

It is not enough to simply uncheck or check the boxes in the paragraphs on restricting the rights to change data. The cells will become protected (or vice versa, if you unchecked them) after the function is activated for the entire page. Excel Settings By default, the “Protected Cell” option is activated and “Hide Formulas” is turned off, but you can check this or make adjustments if necessary.

1. Go to the “Review” tab, select “Protect sheet” - this way you will limit the rights to all cells that are checked (the rest can still be changed).
2. You can also deposit additional settings before applying the option to specify what exactly will be done with the marked elements.
3. Confirm activation of access restriction.

If you want to leave the option to format all elements of an Excel page, check only the first three items.

Excel allows you to add a password when protecting a worksheet.

Limiting different ranges for different users

If several people are working on a document, and each of them should have access to only a certain area, it is necessary:

1. Go to the “Review” tab, click “Allow changing ranges”.
2. In a new window, create an area - enter a list of values ​​and a password to access them.
3. Make the required quantity arbitrary areas and allow access to any number of users.
4. Install the function for the changes to take effect.

When using such a system of functions, each user will be forced to enter a password before editing a certain section of the table. If he doesn't know secret code, you won't be able to make changes.

We restrict or provide access to cell ranges.

Restrict one or more sheets from changing and formatting

In most cases, it is not enough to limit the rights to cells and their groups by functionality. It is advisable to prohibit editing an entire Excel page or several. To do this you need:

1. Go to “Review”, select “Protect Sheet” in the “Changes” area.
2. Enter a password if required.
3. Save changes.

Using this function, you can avoid not only changing certain values, but also editing the title, “header” (fixed rows and columns), formatting and resizing in the Excel window.

How to set a password to protect against editing

Restrictions set in an Excel document can be overridden by any user with rights (by default). In order to prevent other users from disabling the function of protecting against editing or entering incorrect data, you can set a password. For this:

1. Specify the cells or sheets to which you want to restrict access.
2. Go to Review and click Allow Ranges to Change.
3. In a new window, create a range or specify an existing one.
4. Enter a password and save the settings.

After activating the function, the document cannot be changed by a person who does not know the file access code. Be careful, as it is impossible to recover your Excel password - it will be lost along with all your data.

How to remove protection

This algorithm is suitable for returning rights to the entire sheet, and to some of its elements, if they are the only ones blocked. For this:

1. Specify the list of cells to which you want to restore access (press Ctrl + “A” to select all).
2. Open the “Format Cells” window by right-clicking on the elements area.
3. Go to “Protection” and configure the “Hide formulas” and “Protected cell” items.
4. Save your changes.

After using these functions, the Excel document will again become available to all users. In order to cancel protection for only part of the book, first do this for the entire book, and then select the elements whose rights should be restricted again.

Bottom line

Knowing what storage capabilities Excel offers, you can fine-tune access restrictions. This way you can protect yourself from accidentally entering unwanted data, as well as editing the book by a third-party user. You can password-protect cells, as well as remove protection from unwanted access or amendments in whole or in part. These are the simplest ways to restrict rights that can be configured in Excel itself.

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 a command such as . You can find it:

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

But when this command is executed, ALL cells in 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 cannot be changed. This is in great demand in various types fillable templates in which only certain cells can be filled in and all others cannot be edited. This is quite easy to do. Select the cells that need to be allowed to change (A1, C2 and D3); then Ctrl+1(or right button mice- Format cells)-tab Protection. Uncheck the box Protected cell (Locked). Now we install protection on the sheet.

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

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

Also on the tab Protection there is a point 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 formulas - only the results of their calculations. Useful if you want to leave the ability to enter some parameters, and leave calculations using 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; text-align:left;","textbgcss":"display:absolute; left:0px; ; background-color:#333333; opacity:0.6; filter:alpha(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))