home / Education/ How to protect a column in excel from editing. Protecting cells from editing in Microsoft Excel

How to protect a column in excel from editing. Protecting cells from editing in Microsoft Excel

Hello my friends.

Don't want anyone to make changes to the spreadsheet you've written? There is a solution - protection of cells from editing in excel. Moreover, the developers offer different degrees of it: from blocking individual cells to the entire book. This article will show you how to use these options.

How do I secure individual cells?

Excel 2007 and later let you set a password for the file so that only you can open it. For example, you want to give other users the freedom to open and view a book. However, you do not want them to make any changes to the totals cells or to a specific column.

In such cases, the steps for excel 2010 and any other will be the same:

Thus, protection against changes is set only for individual cells.

But that is not all.

Next stage

Now we will password-protect the sheet so that only you can make adjustments in the future:

Are the cells to be locked away from each other? Excel 2016 and versions up to and including 2007 allow you to select them automatically, not manually.
To do this, in the "Home" tab in one of the most recent fields called "Editing", click the "Find and Select" button. Select "Select a group of cells ..."

And set the desired settings:

That's all.

Now your documents will be completely safe.

Visit me often to find out even more useful information.

Goodbye friends!

Did you know that the spreadsheet editor Microsoft Excel allows you to put a password for changing both the whole book and a separate sheet? This is very convenient if you need to protect content from users, prevent them from making changes to the table, or even block access to it. This is especially true in some companies, where a single file-trash can is organized on the server, which is available to everyone and where all users dump their documents. By password-protecting the entire XLS or XLSX file from unauthorized access. At the same time, for those who really need access, you can issue a password on an individual basis.

Let's take a closer look at how to password protect data in Excel.

Setting a password on an Excel sheet

At the bottom of the window, find a list of sheets and click on the desired one with the right mouse button to bring up a menu where you need to select the "Protect sheet" item:

A separate window "Protection of the sheet" will appear, in which you must specify the password, as well as use the checkboxes to set the rights that will be available to users:

After you enter the password, another window will appear in which you must enter it again to confirm:

Now, when trying to change the contents of a protected sheet, the user will receive the following message:

And if he tries to remove the protection of the sheet, then Excel will require him to enter the password:

Note: You can also call the "Protect Sheet" window through the main menu in the "Review" section. See the video for more details:

How to put a password on an Excel workbook

Now let's look at how to set up password protection for a workbook in Excel. In the main window of the program and call the menu "File". Such a menu will appear, in which you need to open the "Information" section:

The first item is "Book protection". Click on it and see this menu:

We select the item "Encrypt using a password". Excel will ask you to enter a password:

Enter and click "OK". After that, you will see a message that a password is required to open this book.

Excel file password

This method of data protection implies that you want to set a password for the entire XLS or XLSX file so that an unauthorized person cannot open it at all, and if you try to do this, you will be prompted to enter a security key:

After you press the "OK" button, a password confirmation window will pop up twice - first to open an XLS or XLSX file, and then to change it.
After that, you can be sure that your data is well protected.

You can put a password on the email Excel spreadsheet thus preventing access for other users. This can come in handy at home or at work. But in this way, it is impossible to seriously protect the table. Because Excel uses a very weak form of encryption, it is very easy to access the file. Let's try to figure it out in more detail.

When you need to close access to an Excel file, at home or at work, from other employees, just put a password on it. The password is set quite simply, right in Excel file e. But, as mentioned above, it is very easy to access a protected Excel file. A few minutes is usually enough to crack the Excel file.

However, later versions of Excel have more sophisticated encryption methods. Plus a complex and long password. Together, this will better protect the table. How older version Excel, the easier it is to crack the password. Therefore, you must use a version not lower than Excel 2010, better - 2013 and later.

Also with a password, the more complex it is, the better. ? - in this thread, we discussed in detail what to rely on when creating a complex and secure password. Another topic that can be useful when creating protection is? Rely on the advice given in the articles on the links above and you will really have good password, and at the same time protection.

Put password on Excel file

And so, let's get down to business. You can put a password on the Excel file in the most Microsoft program Excel.

There are two ways to protect a file: the first is to put a password for the entire workbook (the entire Excel file), the second is to protect a specific sheet (tab).

Password protect sheet in Excel

All password to Excel sheet is set, now when you open this Excel worksheet will ask for a password.

Protect entire workbook in Excel with a password

Now let's set a password for the whole Excel file.

The entire Excel workbook is now password protected.

The next time you start a protected Excel file, you will be asked for a password, without entering which, nothing can be edited and even the contents of the table can be seen.

I keep repeating that you need to use strong passwords. Even in this case, if you want to get a protected Excel file, you need not just 12345678, qwerty123, the date of birth or the name of your beloved dog, but something more "hard", such as: f0P! LgkTr & - do not try to decipher, this is just a set of characters: )

And of course, I will repeat once again: it is impossible to reliably protect information in an Excel file. But, at least using a newer Microsoft versions Excel and good, strong password help from accidental hacking, something like this.

In the following articles, we will look at how you can get access to a protected Excel file and you will see for yourself that to save confidential information it is not at all enough to put a password in Microsoft Excel.

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

To prevent others from intentionally or accidentally changing, moving, or deleting data in a worksheet, you can lock cells on the Excel worksheet and then password protect it. Suppose you have a team report sheet and you want team members to be able to add data only to specific cells and not be able to change other items. By protecting a sheet, you can make only certain parts of the sheet available for editing, after which users will not be able to edit data in other areas of the sheet.

Important:

Leaf protection not is a safety feature. It simply disallows changing locked cells on the sheet.

Protecting a sheet is different from protecting a file or Excel workbook with a password. See below for more details.

For information on how to lock a file so that others cannot open it, see Protect an Excel file.

To prevent adding, modifying, moving, copying, or hiding or showing sheets in a workbook, see Protect a workbook.

Selecting sheet items to lock

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

Formulas: If you don't want others to see formulas, you can hide them from cells or the formula bar. For more information, see Show and hide formulas.

Ranges: you can allow users to work in specific ranges on a protected sheet. For more information, see the article Locking and unlocking specific areas of a protected sheet.

Note: ActiveX controls, form controls, shapes, charts, SmartArt graphics, sparklines, slicers, timelines, and a few other controls are blocked as soon as they are added to the table. However, blocking will only work if sheet protection is enabled. For more information on how to enable protection, see the next section.

Enabling sheet protection

Protecting a worksheet is a two-step process: you first need to unlock the cells that users need to modify, and then you need to secure the worksheet with or without a password.

Step 1. Unlock all cells that need to be changed

Step 2. Protecting the sheet

Decide what actions users should take on the sheet (for example, insert and delete columns or rows, change objects, sort, or use AutoFilter). Alternatively, you can also provide a password to lock the sheet. The password will not allow other users to remove the protection from the sheet - it will need to be entered to disable it.

Below are instructions on how to protect a sheet.

In the tab Peer review press the button Protect sheet.

In the list Allow all users of this sheet select the checkboxes for the items that you want users to edit.

Parameter	Possibility
selection of locked cells	Move the pointer to the cells for which the checkbox is checked Protected cell in 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 in the tab Protection in the dialog box Cell format... By default, users are allowed to select unprotected cells as well as navigate between unprotected cells on a protected worksheet using the TAB key.
cell formatting	Changing parameters in dialog boxes Cell format or Conditional formatting... If conditional formatting was applied before the sheet was protected, the formatting will change when you enter a value that satisfies the specified condition.
column formatting	Use any column formatting commands, including changing the width of a column or hiding columns (the tab home, group Cells, button Format).
string formatting	Using any line formatting commands, including changing the line height or hiding lines (tab home, group Cells, button Format).
inserting columns	Inserting columns.
insert lines	Insert lines.
inserting hyperlinks	Insert new hyperlinks (even in unlocked cells).
deleting columns	Removing columns. Note: If the element " delete columns"protected and Inserting columns unprotected, the user can insert columns but cannot delete them.
delete lines	Deleting lines. Note: If string "delete lines"protected and Insert Rows unprotected, the user can insert rows but cannot delete them.
sorting	Using commands to sort data (tab Data, group Sort and filter). Note: Users will not be able to sort ranges that contain locked cells on a protected worksheet, regardless of this setting.
using an autofilter	Use the drop-down arrows to change the filter in ranges if AutoFilters are applied. Note: Users will not be able to apply or remove AutoFilters on a protected sheet regardless of this setting.
using pivot table reports	Formatting, changing the layout, refreshing data, or otherwise modifying PivotTable reports, and generating reports.
changing objects	Do the following: Changes to graphic objects, including maps, inline charts, shapes, text boxes, and controls that were not unlocked prior to setting sheet protection. For example, if a worksheet has a button that runs a macro, you can click it to run the macro, but you cannot delete it. Making any changes (such as formatting) to the inline chart. The chart will continue to update when its original data changes. Adding and editing notes.
changing scripts	View hidden scripts, modify scripts that are not allowed to change, and delete those scripts. Users can change values in editable cells if the cells are not protected and add new scripts.

If you wish, you can enter a password in the field Password to disable sheet protection and press the button OK... In the dialog box Password confirmation re-enter the password and press OK.