Protect excel from editing. How to set or remove edit protection for cells, sheets and workbooks in Excel

Protect information in Excel workbook Can different ways. Set a password for the entire book, then it will be requested every time you open it. 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 an 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 exactly what this article will discuss.

Protecting the allocated range from modification

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

Cell protection can only be done if you enable protection for the entire sheet. By default, in Excel, when you enable sheet protection, all cells 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 left top corner. Then click on any of them right click mouse 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 disable editing blocks that are in the range B2:D7. Select the specified range, right-click on it and select “Format Cells” from the menu. Next, go to the “Protection” tab and check the “Protected...” box. Click OK.

On next step you need to enable protection for of this sheet. Go to the tab "Review" and click the "Protect Sheet" button. Enter the password and check the boxes for what users can do with it. Click OK and confirm your password.

After this, 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 text or numbers in the range B2:D7, a message appears that the cells are protected.

Set a password

Now let’s assume that you yourself often work with this sheet in Excel and periodically 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 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 the range B2:D7. And you, knowing the password, could edit 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 go to “Cell Format” again and check the “Protectable...” box.

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

We return to the previous window. Click “Apply” and “OK” in it. This way, you can create multiple ranges protected with different passwords.

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

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

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

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 a questionnaire or form.

For example, a 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 this column the number is not from 1 to 11.

Select the desired 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 we enter “1”, in the “Maximum” field – “11”.

In the same window on the tab "Message to be entered" Let's enter a message that will be displayed when any cell from this range is selected.

On the tab "Error message" Let's 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 we 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. In addition, you can set a password, knowing which certain users will still be able to change data in protected blocks.

Rate this article:

(1 ratings, average: 5,00 out of 5)

Webmaster. Higher education with a degree in Information Security. Author of most articles and computer literacy lessons

Discussion: 13 comments

Answer

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 in an Excel workbook from accidental or intentional interference by 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:

restriction on entering incorrect information;
protecting a selected quantity or a specific group of values from editing;
setting different rights for different users or groups;
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 wrong date a certain event. In order to install protection, you need:

Select the elements to which the function will be applied.
Go to the “Data” tab in Excel, then in the “Working with Data” area, click “Data Validation”.
A window will open - in “Parameters”, specify the type of data you want to protect cells from entering.
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.
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.

Limiting the entire workspace:

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).
Select “Format Cells”, in the window go to “Protection” and activate the items “Protected cell” (set by default, optionally or instead), “Hide formulas”.
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:

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).
On the highlighted area, click right key mouse to open the context menu.
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.
Click "OK" to apply the settings.
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:

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”.
Specify the required options and click OK for the changes to take effect.
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.

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).
You can also deposit additional settings before applying the option to specify what exactly will be done with the marked elements.
Confirm activation of access restriction.

If you want to leave the option to format all elements Excel pages, check only the first three points.

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 only have access to specific area, necessary:

Go to the “Review” tab, click “Allow changing ranges”.
In a new window, create an area - enter a list of values and a password to access them.
Create the required number of custom areas and allow access to any number of users.
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:

Go to “Review”, select “Protect Sheet” in the “Changes” area.
Enter a password if required.
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:

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

After activating the function, the document cannot be changed by a person, not those who know the code access to the file. 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:

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

After use specified functions Excel document will again be 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 the cells, as well as remove the protection against unwanted access or amendments in whole or in part. These are the simplest ways to restrict rights that can be configured in Excel itself.

How to protect cells in Excel from editing, step-by-step instruction and ways to protect data in table cells.

Do you want to know how to consistently earn money online from 500 rubles a day?
Download my free book
=>>

From time to time, when working with tables in Excel, you need to install protection against intentional or accidental editing. This especially applies to ranges containing formulas for performing any calculations.

After all, if another user makes some changes to the work you have done previously, the consequences can be very unpleasant.

How to protect cells in Excel from editing

In Excel you can install it like full protection pages from data correction, or partial. Therefore, the choice remains solely with the user who decides to protect the data.

Protecting specific cells

So, if you need to protect a specific cell from making adjustments, then do the following:

The principle of operation is similar when using the “Review” tab.

But in this case, first select “Protect Sheet”, and then check “Highlight locked cells”.

Thus, if someone wants to make changes in a certain area, then without knowing the password you previously set, nothing will happen.

How to remove protection

To unlock access and make it possible to make adjustments, also through “Review” or the “Cells” section in the main menu, go to “Sheet Protection” and select “Unprotect Sheet”. Next, enter the password specified when blocking.

It is best to write down the password and keep it in a safe place. If you lose it, you cannot recover your password. When entering your password, it is important to enter uppercase and lowercase letters correctly. Since if you wrote, for example, the name of your dog with capital letter, and then entered the password with a small one, then you will be told that the password is incorrect.

Protecting the page

In a situation where you need to protect the entire table from unexpected changes, you need to:

Go to “Review” and in the “Changes” subsection click on “Protect Sheet”.
Next, uncheck the first line and leave it next to the “Select unlocked cells” entry.
Then, in the “Password for...” field, enter the password and repeat it.
Click on “OK”.

Universal method of protection

If you need to protect specific area in the table, and all the data on the page as a whole, then move the mouse to the lower left corner, where the sheets are located.

By the way, the name of the completed sheet may not be the standard “Sheet 1”, but something else. It's not very important.

So let's get started:

As a result, when you try to make any changes, you will be prompted for a password. When you need to open access, to be able to make adjustments, then also move the mouse over the lower part program window From the left side.

Then, right-click on it. Next, select “Unprotect…”, or use “Review” for these purposes.

Summary

As you can see, when the question arises about how to protect cells in Excel from editing, there are several options for solving it. It all depends on whether you need to protect all the data on one sheet or only certain areas.

The other day a question came from one of the users: How to protect specific cells from changes?

Can in the usual way, it is possible using VBA (but I am not a supporter of the VBA solution, since this is not allowed and will work everywhere).

First, make the cells defensible;

In the format of the cells that you want to protect, you must select the checkbox in the “Protected cell” field. To do this, just right-click, select the “Format Cells...” context menu item and go to the “Protection” tab.

The mere presence of this flag does not protect the cell in any way; it only indicates that the cell will become protected after the sheet protection is set. In every new Excel workbook, all cells are protected by default. It is worth noting here that you can change the format of cells at once in the entire selected range, as well as in a group of non-adjacent cells.

Secondly, install sheet protection.

To set worksheet protection in Excel 2007/2010, you must go to the “Review” tab, in the “Changes” group and click the “Protect Sheet” button. In Excel 2003, sheet protection is set through the menu item Tools/Protection/Protect Sheet.

There is a way quick installation protection for all sheets at once. Only after these two steps have been completed will the cell/cells protection take effect.

By protecting cells, a table can be clearly demarcated into areas that can be changed and those that cannot be changed. As you have already seen, this is quite simple to do. The only inconvenience arises when you need to selectively protect cells, for example, install protection only on cells with formulas, but this inconvenience can also be solved. To do this, just go to the “Home” tab and in the “Editing” group open the menu of the “Find and Select” button, then select “Select a group of cells...” and select required parameter (read more about selecting a group of cells). After all the necessary cells have been selected, all that remains is to make the cells protected, and then set the sheet protection. Let me remind you that to select all the cells of the sheet, you can use the hotkey combination Ctrl1+A or click on the so-called zero cell (in the screenshot the zero cell is highlighted with a red frame).

That's all.

There is another option to solve the same issue:

Select all cells, then in the menu Format select team Cells(CTRL+1) In the standard dialog box that appears, select the bookmark Protection and uncheck the box opposite Protected cell. Now select the cells that are intended to be blocked (you can select non-adjacent cells and ranges using CTRL key) and in the menu Format again select the Cells command, and then check the box next to Protected cell. Now, in the Tools menu, select Protection and the Protect Sheet command. In the Allow all users of this sheet list: be sure to uncheck the box next to Highlight locked cells and check the box next to Highlight unlocked cells, enter required password and click Ok.

Like 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 there is a set various 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 enable worksheet protection in Excel, all cells will be protected. If you select a cell or several and enter the cell format, you can go to the tab "Protection" see the check mark "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 unchecked. And the cells we previously edited can be changed. At the same time, when you turn on the protection, you will be able to make additional settings and choose what exactly cannot be done with the cells.

Now let's look at the possibility of password protecting 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, protecting cells from editing in Excel still does not work. For everything to work, you need to enable sheet protection, as discussed above. After this, all cells will be protected from changes, but for the cells we have selected, separate password, by entering which you can make the necessary changes without unlocking the sheet.

Knowing the capabilities of protecting cells in Excel, you can now combine various options. For example, by selecting all the cells of a sheet and going to the cell format, you can, on the protection tab, cancel the protection of these cells, then select separate group cells and return protection to them. On at this stage When you enable sheet protection, only the cells we have marked will be locked. And if you first enter a separate password for the selected cells through the menu for allowing changes in ranges, then after turning on the sheet protection, you can enter any data into all cells, and changes can be made to locked cells only when you enter a password. Actually, this is the only way to protect individual Excel cells from changes using standard means.