How to block some cells in Excel. How to protect a cell from changes in Excel

26.06.2020 Advice

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 (4)

How to allow only selected cells to be changed?

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 of 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 be made only in these cells, and the values of the rest cannot be changed. This is very much in demand in various types of fillable templates, in which only certain cells can be filled, and all others are prohibited from editing. This is quite simple 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 set the protection to the sheet.

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

After that, install the protection on the sheet (as see at the very beginning of the article) and voila! You can change only those cells that have unchecked the "Protected cell" (Locked) checkbox.
In this case, if, when protecting a sheet, you uncheck the box Select locked cells- it will be possible to select only those cells that are allowed for editing. Also, moving through the cells with 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 in which cells the values can be changed and in which not.

Also on the tab Protection there is a point Hide formulas (Hidden)... If it is installed together with the setting of the Protected cell attribute, then after setting the protection in the protected cells it will be impossible to see the formulas - only the results of their calculations. It is useful if you want to leave the opportunity to enter some parameters, and leave the calculations by 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; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (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))

Sometimes you want to allow users to change cells that contain data without allowing them to change formulas. You can lock cells containing formulas without protecting the entire sheet or workbook.

When creating a spreadsheet, most of us use some kind of formulas, Sometimes, however, we do not want other users to spoil, delete or rewrite any formulas present in the spreadsheet.The simplest and most common way to prohibit modification of formulas is to protect the sheet, However, to protect the sheet not only prevents users from changing formulas, it also implies that users cannot enter or change any data. Usually you don't want to go that far. By default, all cells on a worksheet are locked; however, this has no effect until sheet protection is applied. Next, we will talk about a very simple way to protect a sheet, when only cells with formulas are locked and protected.

Select all cells on the sheet by pressing Ctrl / Apple + A or by clicking the sheet selection button (the gray square at the intersection of column A and row 1). Then select Format → Cells → Protection and uncheck the Locked box. Click the OK button.

Now select any cell, choose Edit → Go To (Ctrl + G or F5) and click the Special button. You will see a dialog box for selecting a group of cells (Fig. 1.13).

In the Go To Special dialog box, select the Formulas radio button and, if necessary, select specific types of formulas by checking the appropriate boxes. Click the OK button. Only cells with formulas will be highlighted. Select Format → Cells → Protection and select the Locked check box. Click the OK button. Now select Tools → Protection → Protect Worksheet to protect the sheet and, if necessary, assign a password.

The previous method definitely saves a lot of time and eliminates possible mistakes when looking for formulas for protection. Unfortunately, this does not allow users to take advantage of several tools, such as sorting, formatting, text alignment, and many others that you may not be aware of, even in an unlocked cell. This problem can be solved in two ways.

The first approach does not use sheet protection at all, but uses data validation.

Data validation is far from ideal when it comes to preventing unverified data from being put into cells. Users can easily paste any copied data into a verified cell and, in doing so, remove the verification mark from that cell. If the copied cell contains a check mark, then the new check mark will overlap the original one.

To see what we're talking about, select any cell, choose Edit → Go To (Ctrl + G or F5) and click the Special button. In the Go To Special dialog box, select the Formulas radio button and, if necessary, select only specific types of formulas. Click the OK button.

Now that only cells with formulas are selected, go to the Data → Validation → Settings tab, in the Allow field, select Custom, and in the Formula field, enter = "" ( fig. 1.14). Click the OK button.

This method prevents accidental writing of data to any formula cells, although, as highlighted in the previous warning, it is not completely safe and can only be used against accidental data entry, etc. However, the big advantage of this method is that all the features of Excel can be applied to the worksheet.

The latter method will allow you to use all the features of Excel, but only when you are in an unlocked cell. First, make sure that only the cells that you want to protect are locked, and all others are not locked. Right-click the sheet tab, select View Code from the context menu, and enter the code from Listing 1.17.

1 2 3 4 5 6 7 8

// Listing 1.17 Private Sub Worksheet_SelectionChange (ByVal Target As Range) If Target. Locked = True Then Me. Protect Password: = "Secret" Else Me. Unprotect Password: = "Secret" End If End Sub

// Listing 1.17 Private Sub Worksheet_SelectionChange (ByVal Target As Range) If Target.Locked = True Then Me.Protect Password: = "Secret" Else Me.Unprotect Password: = "Secret" End If End Sub

If you do not want to use a password, remove the Password: = "Secret" line. If you entered a password, change the word Secret to your password. Press Alt / Apple + Q or click the cross in the upper right corner of the window to return to Excel and save the workbook. Now, every time you select a locked cell, your sheet will automatically protect itself. As soon as you select an unlocked cell, the sheet will automatically unprotect itself.

This trick does not work perfectly, although it is usually quite passable. The keyword used in the code, Target, only refers to the cell that is active at the time of the selection. Therefore, it is important to remember that if the user selects a range of cells (where the active cells will not be locked), he will be able to delete the entire selected range, since the target cell is not locked and therefore the sheet is unprotected.

There are various ways to protect information in an Excel workbook. Set a password for the entire book, then it will be requested every time you open it. Put the password on separate sheets, then other users will not be able to enter and edit data on the protected sheets.

But what if you want other people to be able to work normally with the Excel workbook and all the pages that are in it, but at the same time you need to limit or even prohibit editing the data in individual cells. This is what this article will be about.

Protection of the selected range from changes

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

Cell protection can be done only if you enable protection for the entire sheet as a whole. By default, Excel, when you turn on sheet protection, automatically protects all cells that are located on it. 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, you need to click on the triangle in the upper left corner. Then click on any of them with the right mouse button 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 prohibit editing blocks that are in the range B2: D7. Select the specified range, right-click on it and select "Format Cells" from the menu. Then go to the "Protection" tab and put a tick in the "Protected ..." field. Click OK.

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

After that, any user will be able to work with the information on the page. The example introduces fives in E4. But when you try to change text or numbers in the range B2: D7, a message appears that the cells are protected.

We put a password

Now suppose that you yourself often work with this sheet in Excel and periodically you 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 put a password for individual cells in Excel. In this case, you can edit them by simply entering the requested password.

Let's make it so that other users can edit everything on the sheet, except for the range B2: D7. And you, knowing the password, could edit the 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 again go to "Format cells" and put a tick in the "Protected ..." field.

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

We return to the previous window. Click "Apply" and "OK" in it. Thus, it is possible to create several ranges protected by different passwords.

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

Checking out how cell protection works. Introduce sixes in E5. If you try to remove a value from D5, a window asking for a password will appear. By entering the password, you can change the value in the cell.

Thus, knowing the password, you can change the values in the protected cell of the Excel worksheet.

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 some kind of questionnaire or form.

For example, the table has a "Class" column. There cannot be a number greater than 11 and less than 1, meaning school classes. Let's make the program generate an error if the user enters a number in this column that is not from 1 to 11.

Select the required range of table cells - C3: C7, go to the "Data" tab and click on the button "Data validation".

In the next dialog box, on the "Options" tab, in the "Type ..." field, select "Integer" from the list. In the “Minimum” field, enter “1”, in the “Maximum” field - “11”.

In the same window on the tab "Message to enter" we will enter a message that will be displayed when selecting any cell from this range.

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

Rate article:

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

Webmaster. Higher education in the specialty "Information Security" .. Author of most articles and lessons of computer literacy

How to protect all cells in an Excel file

If you want to protect absolutely all cells in your Excel file from editing and changes, do the following:

Go to the “ Peer review"On the toolbar => in the subsection" Protection"Click on the icon" Protect sheet “:

In the pop-up window, make sure to check the box next to ““:

Enter the password in the ““ field if you want only those users to whom you have provided the password to remove protection:

Select from the list and check the box the actions with the cells of the sheet that will be allowed to all users:

Click “ OK “

If you have set a password, the system will ask you to re-enter it.

Now, all users who try to make changes or edit cell values will receive a message like this:

In order to remove the installed protection, go to the “ Peer review “, And in the section“ Protection"Click on the icon" Remove sheet protection “. After that, the system will ask you to enter a password to remove protection.

How to protect individual cells in Excel from changes

Most often, you may not need to protect the entire sheet as a whole, but only individual cells. As I wrote at the beginning of this article, all cells in Excel are locked by default. In order for the blocking to occur, you actually need to configure sheet protection and set a password.

As an example, consider a simple spreadsheet with data on income and expenses. Our task is to protect against cell changes in the range B1: B3 .

In order to block individual cells, we will do the following:

Select absolutely all cells on the Excel sheet (using the keyboard shortcut CTRL + A ):

Let's go to the “ home"On the toolbar => in the section" Alignment ”Click on the icon in the lower right corner:

In the pop-up window, go to the “ Protection"And uncheck the box" Protected cell “:

Let's click “ OK “

Thus, we turned off the Excel setting for the cells of the entire sheet, in which all cells are ready to be protected and locked.

Now, select the cells that we want to protect from editing (in our case, this is a range of cells B1: B3 );
Let's go to the “ home"On the toolbar and in the subsection" Alignment ”Click on the icon in the lower right corner, as we did earlier.
In the pop-up window, on the “ Protection"Check the box" Protected cell “:

Let's go to the tab “ Peer review "On the toolbar and click on the icon" Protect sheet “:

In the pop-up window, make sure to check the box next to “ Protect sheet and contents of protected cells “:

Let's enter the password in the “ Password to disable sheet protection “So that only those users to whom we have given the password could remove protection:

Sometimes there are times when we are working with important documents and do not want anyone to see them. Or, at the very least, we prohibit editing the content without our knowledge. But, as practice shows, if such files are not opened for a long time, the owner himself can forget the access code. In this article, we will look at how to remove protection from a sheet in Excel editor without knowing the password.

This is not about hacking other people's documents. We will work with what is on our computer.

When you open a protected document, you will see the following.

The toolbar will be inactive. File modification and almost all functions are not available. Try clicking somewhere and typing something on the keyboard.

The selected cell will be inactive and editing will be impossible. In this case, the following error will appear.

In order to unlock a document in Excel, you need to follow these steps.

Click on the "File" menu item.

In the window that appears, click on the "Remove protection" link.

Immediately after that, you will be asked to enter a password. If you do not know him, then you will not be able to remove the protection.

A similar window can be called through another menu. To do this, you need to follow a few simple steps.

Go to the "Review" tab and click on the "Unprotect sheet" button.
The result will be exactly the same.

In this case, the book is available for reading, but you cannot change anything. It is worth noting that sometimes, if the maximum file protection is not set, Excel program allows you to copy the contents of the cells.

Try to select something and press the keyboard shortcut Ctrl + C. The active range is immediately highlighted with a dotted frame. This means that copying is not disabled.

Open a new document and press Ctrl + V. The data will transfer just fine and you don't need to hack anything.

How to remove protection in Excel 2003

The steps described above are suitable for newer versions of Excel. In the old, the principle of operation is slightly different. When opening the file, many functions will also be unavailable.

In order to remove the lock, you need to do the following.

Click on the "Service" menu item.
Then select "Protection".
After that - "Remove sheet protection".

Immediately after that, you will be asked to enter a security key.

How to remove a password

It doesn't matter what you are trying to do - hack someone else's document or open your old file - in any case, you will have to do the same thing. There is no difference for the program and it does not understand who the owner is. Ultimately, you are trying to bypass the protection.

In fact, everything is very simple. To do this, follow these steps.

Most likely, your operating system does not display file extensions. To enable them, follow a few simple steps:
1. Open File Explorer.
2. Click on the "View" tab.
3. Check the box next to the corresponding item.

Then change the extension of your file from XLSX to ZIP.

To do this, click on the file and press the F2 key. After you change the end of the document and press the Enter button, you will see the following window. Click on the "Yes" button.

Your file now has a new icon because Windows thinks it is an archive.

Open this file (but don't unpack it!). Go to the "xl" folder.

Then in "worksheets".

A list of files for each sheet will appear in this folder. Click on it with the mouse and drag it into the explorer.

Then right click on this file. In the menu that appears, select the "Change" item.

As a result, a window like this will appear.

Press the keyboard shortcut Ctrl + F. Enter the word "sheetProtection" there. Then click on the "Find Next" button.

If the file is saved in Excel 2013 or 2016 editor, you will see the following.

In the 2007 program, this tag is much shorter, and the type of encryption is simpler.

We remove everything contained in the sheetProtection tag. In the case of an example, you need to delete this piece.

Then we save the document by pressing the hot keys Ctrl + S. We transfer this file back (from the explorer to the archiver).