Prevent editing cells in Excel. Protecting cells in Excel from changing, editing and entering erroneous data

Microsoft program Office Excel 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 apply.
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.

Limit the entire work area:

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.

This article will discuss how to protect a cell in Excel from changes. Fortunately, this option is present in this spreadsheet editor. 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 perform more flexible setup, 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 your password in the same way, select required parameters, check the box next to "Protect the sheet and contents of protected cells" and click OK.

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 only be able to add data to specific cells and could not 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 a file or workbook Excel password. additional information see below.

Selecting Sheet Items to Lock

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

Note: ActiveX controls, form controls, shapes, charts, graphic elements SmartArt, Sparklines, Slicers, Timelines, and some other elements are locked as soon as they are added to the 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 modified

Step 2. Protect the sheet

Decide what actions you want users to perform on the worksheet (for example, inserting or deleting columns or rows, editing 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 satisfies 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 Removing Columns protected and Inserting Columns is not protected, the user can insert columns, but they cannot be deleted.
deleting rows	Removing rows. Note: If Removing rows protected and Inserting Rows is not protected, the user can insert rows, but they cannot be deleted.
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 reports pivot table	Formatting, changing the layout, updating data, or otherwise modifying PivotTable reports, and creating reports.
changing objects	Fulfill the following actions: Making changes to graphic objects, including maps, embedded charts, shapes, text boxes, and controls that were not unlocked before you set the sheet protection. 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. Add or edit notes.
changing scenarios	View hidden scripts, change scripts from established ban to change and delete these scripts. Users can change the values in changeable cells, if 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 special characters. Not all of these characters are used in weak passwords. The password must be at least 8 characters long, but it is better to use passphrase at least 14 characters long.

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 Group Changes.

If a sheet is not protected, the "Protect Sheet" button appears on the ribbon.

Unprotect an Excel worksheet

To remove sheet protection, do the following:

Select the sheet you want to unprotect.

Select File > Intelligence > Protection > Remove sheet protection or ReviewChanges > Remove sheet protection.

If the sheet password protected, enter it in the dialog box Sheet protection and press the button OK.

Filling Excel tables- This is enough routine process, which can lead to errors. Often, when entering data into a table, users accidentally change other cells and have to spend even more time correcting these errors.

In this article we will talk about how to protect Excel cells from editing. The article will be useful for current versions Excel, such as Excel 2007, 2010, 2013 and 2016.

The problem is that Excel does not have the ability to protect individual cells from editing. There are no functions to directly implement this idea. But there is one trick that allows you to get almost the same result. You can protect an entire Excel sheet from changes, but still be able to edit individual cells.

For implementation this method you need to select those cells in which you need to leave the ability to edit data, click on them right click mouse and select “Format Cells” in the menu that opens.

As a result, the “Format Cells” window will open in front of you. Here you need to go to the “Protection” tab, uncheck the “Protected cell” function and save the settings by clicking on the “OK” button.

After this, you need to right-click on the sheet name at the bottom of the page and select “Protect Sheet” from the menu that opens.

Then enter the password again to confirm and click on the “OK” button again. Important point, it’s better not to forget the password, otherwise you won’t be able to remove the protection from the sheet later.

That's all, you have protected all cells of this sheet Excel, of course, except for those for which you have disabled the “Protected Cell” function. Now when working with this sheet Excel user will not be able to change any unnecessary data since only individual cells will be available for editing.

If in the future you come to remove protection from this sheet, then this is done in a similar way. Right-click on the sheet name, select “Unprotect” and enter the password. Without a password, you will not be able to remove protection from the sheet.

It should be noted that even if the sheet is protected, the user still has the ability to change the name and delete this sheet. In order to avoid similar problems you need to protect the entire Excel workbook. To do this, go to the “Review” tab, click on the “Protect Book” button and enter the password twice, as was shown above.

By adding protection to the entire workbook, you will prohibit the user from any operations with the sheets and document structure, which will significantly increase work security.

You can protect information in an Excel workbook 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 Excel workbook and all the pages that are in it, but at the same time 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 right-click on any of them 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 must enable protection for 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