How to prevent changing a value in an excel cell. Two ways to protect cells in Excel from changes

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 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 a file or workbook Excel password. additional information see below.

Selecting Sheet Elements 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 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, 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.

Cancel protection Excel sheet

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.

Data in Excel can be protected from outside interference. This is important because sometimes you spend a lot of time and effort creating a pivot table or volumetric array, and another person accidentally or intentionally changes or completely deletes all your work.

Let's consider methods of protection Excel document and its individual elements.

Protecting an Excel cell from modification

How to protect a cell in Excel? By default, all cells in Excel are protected. This is easy to check: click on any cell right click, select CELL FORMAT - PROTECTION. We see that the PROTECTED CELL checkbox is checked. But this does not mean that they are already protected from changes.

Why do we need this information? The fact is that Excel does not have a function that allows you to protect a single cell. You can choose to protect a sheet, and then all cells on it will be protected from editing and other interference. On the one hand, this is convenient, but what if we need to protect not all cells, but only some?

Let's look at an example. We have a simple table with data. We need to send this table to the branches so that the stores fill out the QUANTITY SOLD column and send it back. To avoid making any changes to other cells, we will protect them.

First, let's release from protection those cells where branch employees will make changes. Select D4:D11, right-click to open the menu, select CELL FORMAT and uncheck the PROTECTED CELL item.

Now select the REVIEW tab – PROTECT SHEET. A window appears with 2 checkboxes. We remove the first of them in order to exclude any intervention by branch employees other than filling out the QUANTITY SOLD column. Create a password and click OK.

Attention! Don't forget your password!

Now to the range D4:D11 strangers They can only enter some value. Because We have limited all other actions, no one can even change the background color. All formatting tools on top panel instruments are not active. Those. they do not work.

Protecting an Excel workbook from editing

If several people work on one computer, then it is advisable to protect your documents from editing by third parties. You can put protection not only on separate sheets, but also for the entire book.

When the book is protected, outsiders will be able to open the document, see the written data, but rename the sheets, insert a new one, change their location, etc. Let's try.

We keep the previous formatting. Those. We still only allow changes to be made to the QUANTITY SOLD column. To protect a book completely, on the REVIEW tab, select PROTECT BOOK. Leave the checkbox next to the STRUCTURE item and come up with a password.

Now, if we try to rename the sheet, it won't work. All teams gray: they do not work.

The protection is removed from the sheet and book using the same buttons. When removed, the system will require the same password.

Graphs and Charts (5)
Working with VB project (12)
Conditional Formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

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 mouse button - 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))

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 against making adjustments more specific cells, 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.